/**
* function to get a SelectBox field value
* @param field_name : the SelectBox field_name
* @return String : value of the SelectBox field
*/
function getSFValues(ArtifactField $af)
{
if (!$af->isStandardField() && !$af->isUsername()) {
$sql = sprintf('SELECT artifact_id as id,afvl.value as val
FROM artifact_field_value afv
INNER JOIN artifact_field af
USING (field_id)
INNER JOIN artifact_field_value_list afvl
USING (group_artifact_id,field_id)
WHERE af.group_artifact_id = %d
AND af.field_name = "%s"
AND afv.valueInt = afvl.value_id
AND afv.artifact_id IN (' . implode(',', $this->artifacts) . ')', db_ei($this->chart->getGraphicReport()->getAtid()), db_es($af->field_name));
} else {
if ($af->isStandardField() && !$af->isUsername()) {
$sql = sprintf('SELECT artifact_id as id,afvl.value as val
FROM artifact a
INNER JOIN artifact_field_value_list afvl
USING (group_artifact_id)
INNER JOIN artifact_field af
USING (group_artifact_id,field_id)
WHERE a.group_artifact_id = %d
AND af.field_name = "%s"
AND a.%s = afvl.value_id
AND a.artifact_id IN (' . implode(',', $this->artifacts) . ')', db_ei($this->chart->getGraphicReport()->getAtid()), db_es($af->field_name), db_es($af->field_name));
//echo $sql;
} else {
if (!$af->isStandardField() && $af->isUsername()) {
$sql = sprintf('SELECT artifact_id as id,u.realName as val
FROM artifact_field_value afv
INNER JOIN artifact_field af USING (field_id)
INNER JOIN user u
WHERE af.group_artifact_id = %d
AND af.field_name = "%s"
AND afv.valueInt = u.user_id
AND afv.artifact_id IN (' . implode(',', $this->artifacts) . ')', db_ei($this->chart->getGraphicReport()->getAtid()), db_es($af->field_name));
} else {
$sql = sprintf('SELECT a.artifact_id as id,u.realName as val
FROM artifact a,user u
WHERE a.group_artifact_id = %d
AND a.%s = u.user_id
AND a.artifact_id IN (' . implode(',', $this->artifacts) . ')', db_ei($this->chart->getGraphicReport()->getAtid()), db_es($af->field_name));
}
}
}
return db_query($sql);
}
/**
* function to generate data array based on base_field (and eventually group_field if group_field != null)
* @return array : data array
*/
function generateData()
{
require_once 'common/tracker/ArtifactField.class.php';
$af_x = new ArtifactField();
$af_x->fetchData($this->atid, $this->field_X);
// is_null($this->field_Y))
// $af_x->isStandardField())
// $af_x->isUsername()
$result['field1'] = array();
$result['field2'] = array();
$result['c'] = array();
if (!is_null($this->field_Y)) {
$af_y = new ArtifactField();
$af_y->fetchData($this->atid, $this->field_Y);
}
if ($af_x->isUsed() && (!isset($af_y) || $af_y->isUsed())) {
$select = "SELECT STRAIGHT_JOIN ";
$from = "FROM ";
$where = "WHERE ";
$group_by = "GROUP BY ";
$order_by = "ORDER BY ";
// We always join on artifact: it helps to restrict the data set and
// with the current table schema it helps joins and mysql optimiser to
// find the right indexes without FORCE INDEX statement. This was crucial
// on type 3 query because mysql was not able to use index on
// (artifact_id, field_id, value_int) so perfs was horrible.
$from .= "artifact a ";
$where .= "a.artifact_id IN (" . implode($this->artifacts, ',') . ") ";
if ($af_x->isStandardField() && !$af_x->isUsername()) {
//echo "1";
$field = "afvl.value";
$select .= "afvl.value AS field1 ";
$from .= "INNER JOIN artifact_field_value_list afvl";
$from .= " ON (afvl.group_artifact_id = a.group_artifact_id AND afvl.field_id=" . db_ei($af_x->getId()) . " AND afvl.value_id = a." . db_es($this->field_X) . ") ";
} else {
if ($af_x->isStandardField() && $af_x->isUsername()) {
//echo "2";
$field = "u.user_id";
$select .= "u.realName AS field1, u.user_id AS id1 ";
$from .= "INNER JOIN user u";
$from .= " ON (u.user_id=a." . db_es($this->field_X) . ") ";
} else {
if (!$af_x->isStandardField() && !$af_x->isUsername()) {
//echo "3";
$field = "afvl.value_id";
$select .= "afvl.value AS field1 ";
$from .= "INNER JOIN artifact_field_value afv";
$from .= " ON (afv.artifact_id = a.artifact_id AND afv.field_id = " . db_ei($af_x->getId()) . ") ";
$from .= "INNER JOIN artifact_field_value_list afvl";
$from .= " ON (afvl.group_artifact_id = a.group_artifact_id AND afvl.field_id = afv.field_id AND afvl.value_id = afv.valueInt) ";
} else {
//if (!$af_x->isStandardField() && ($af_x->isUsername()))
//echo "4";
$field = "u.user_id";
$select .= "u.realName AS field1, u.user_id AS id1 ";
$from .= "INNER JOIN artifact_field_value afv";
$from .= " ON (afv.artifact_id = a.artifact_id AND afv.field_id=" . db_ei($af_x->getId()) . ") ";
$from .= "INNER JOIN user u";
$from .= " ON (u.user_id=afv.valueInt) ";
}
}
}
$group_by .= $field . " ";
$order_by .= $field . " ASC";
// now if the second field exist
if (!is_null($this->field_Y)) {
$af_y = new ArtifactField();
$af_y->fetchData($this->atid, $this->field_Y);
if ($af_y->isStandardField() && !$af_y->isUsername()) {
//echo " : 1<br>";
$field = "afvl1.value_id";
$select .= ",afvl1.value AS field2 ";
$from .= "INNER JOIN artifact_field_value_list afvl1";
$from .= " ON (afvl1.group_artifact_id = a.group_artifact_id AND afvl1.field_id = " . db_ei($af_y->getId()) . " AND afvl1.value_id = a." . db_es($af_y->getName()) . ") ";
} else {
if ($af_y->isStandardField() && $af_y->isUsername()) {
//echo " : 2<br>";
$field = "u1.user_id";
$select .= ",u1.realName AS field2, u1.user_id AS id2 ";
$from .= "INNER JOIN user u1";
$from .= " ON (u1.user_id=a." . db_es($this->field_Y) . ") ";
} else {
if (!$af_y->isStandardField() && !$af_y->isUsername()) {
//echo " : 3<br>";
$field = "afvl1.value_id";
$select .= ",afvl1.value AS field2 ";
$from .= "INNER JOIN artifact_field_value afv1";
$from .= " ON (afv1.artifact_id = a.artifact_id AND afv1.field_id = " . db_ei($af_y->getId()) . ") ";
$from .= "INNER JOIN artifact_field_value_list afvl1";
$from .= " ON (afvl1.group_artifact_id = a.group_artifact_id AND afvl1.field_id = afv1.field_id AND afvl1.value_id = afv1.valueInt) ";
} else {
//if (!$af_y->isStandardField() && ($af_y->isUsername()))
//echo " : 4<br>";
$field = "u1.user_id";
$select .= ",u1.realName AS field2, u1.user_id AS id2 ";
$from .= "INNER JOIN artifact_field_value afv1";
$from .= " ON (afv1.artifact_id = a.artifact_id AND afv1.field_id = " . db_ei($af_y->getId()) . ") ";
$from .= "INNER JOIN user u1";
$from .= " ON (u1.user_id=afv1.valueInt)";
}
}
}
$group_by .= "," . $field . " ";
$order_by .= "," . $field . " ASC";
}
$select .= ",COUNT(0) AS c ";
//artifact permissions
$sql_group_id = "SELECT group_id FROM artifact_group_list WHERE group_artifact_id=" . db_ei($this->atid);
$result_group_id = db_query($sql_group_id);
if (db_numrows($result_group_id) > 0) {
$row = db_fetch_array($result_group_id);
$group_id = $row['group_id'];
}
$user = UserManager::instance()->getCurrentUser();
$ugroups = $user->getUgroups($group_id, array('artifact_type' => $this->atid));
$from .= " LEFT JOIN permissions\n ON (permissions.object_id = CONVERT(a.artifact_id USING utf8)\n AND \n permissions.permission_type = 'TRACKER_ARTIFACT_ACCESS') ";
$where .= " AND (a.use_artifact_permissions = 0\n OR\n permissions.ugroup_id IN (" . implode(',', $ugroups) . ")\n ) ";
$sql = "{$select} {$from} {$where} {$group_by} {$order_by}";
//echo "$sql<br>\n";
$res = db_query($sql);
for ($i = 0; $i < db_numrows($res); $i++) {
$r[$i] = db_fetch_array($res);
$result['field1'][$i] = $r[$i]['field1'];
if ($af_x->isUsername() && $r[$i]['id1'] == 100) {
$result['field1'][$i] = $GLOBALS['Language']->getText('global', 'none');
}
if (!is_null($this->field_Y)) {
$result['field2'][$i] = $r[$i]['field2'];
if ($af_y->isUsername() && $r[$i]['id2'] == 100) {
$result['field2'][$i] = $GLOBALS['Language']->getText('global', 'none');
}
}
$result['c'][$i] = $r[$i]['c'];
}
}
for ($i = 0; $i < count($result['field1']); $i++) {
$x = array_search($result['field1'][$i], $this->x_values);
if ($x === false) {
$this->x_values[count($this->x_values)] = $result['field1'][$i];
}
}
if (!is_null($this->field_Y)) {
for ($i = 0; $i < count($result['field2']); $i++) {
$y = array_search($result['field2'][$i], $this->y_values);
if ($y === false) {
$this->y_values[count($this->y_values)] = $result['field2'][$i];
}
}
}
// data initialisation
for ($i = 0; $i < count($this->x_values); $i++) {
if (!is_null($this->field_Y)) {
for ($j = 0; $j < count($this->y_values); $j++) {
$this->data[$i][$j] = 0;
}
} else {
$this->data[$i] = 0;
}
}
for ($i = 0; $i < count($result['c']); $i++) {
$x = array_search($result['field1'][$i], $this->x_values);
if (!is_null($this->field_Y)) {
$y = array_search($result['field2'][$i], $this->y_values);
if ($x !== false && $y !== false) {
$this->data[$x][$y] = $result['c'][$i];
}
} else {
if ($x !== false) {
$this->data[$x] = $result['c'][$i];
}
}
}
}
