data.php

Go to the documentation of this file.

<?php
# This file is part of the Savane project
# <http://gna.org/projects/savane/>
#
# $Id: data.php 5387 2006-02-15 15:21:25Z yeupou $
#
#  Copyright 1999-2000 (c) The SourceForge Crew
#  Copyright 2001-2002 (c) Laurent Julliard, CodeX Team, Xerox
#
#  Copyright 2003-2005 (c) Mathieu Roy <yeupou--gnu.org>
#                          Yves Perrin <yves.perrin--cern.ch>
#
#
# The Savane project is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# The Savane project is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with the Savane project; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA


/*

Simple way of wrapping our SQL so it can be
        shared among the XML outputs and the PHP web front-end

        Also abstracts controls to update data

*/
function trackers_data_get_all_fields ($group_id=false,$reload=false)
{

  /*
           Get all the possible bug fields for this project both used and unused. If
           used then show the project specific information about field usage
           otherwise show the default usage parameter
           Make sure array element are sorted by ascending place
  */

  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME, $AT_START;

  # Do nothing if already set and reload not forced
  if (isset($BF_USAGE_BY_ID) && !$reload)
    {
      return;
    }

  # Clean up the array
  $BF_USAGE_BY_ID=array();
  $BF_USAGE_BY_NAME=array();

  # First get the all the defaults.
  $sql='SELECT '.ARTIFACT.'_field.bug_field_id, field_name, display_type, '.
     'display_size,label, description,scope,required,empty_ok,keep_history,special, custom, '.
     'group_id, use_it,show_on_add,show_on_add_members, place, custom_label,'.
     'custom_description,custom_display_size,custom_empty_ok,custom_keep_history '.
     'FROM '.ARTIFACT.'_field, '.ARTIFACT.'_field_usage '.
     'WHERE group_id=100  '.
     'AND  '.ARTIFACT.'_field.bug_field_id='.ARTIFACT.'_field_usage.bug_field_id ';

  $res_defaults = db_query($sql);

  # Now put all used fields in a global array for faster access
  # Index both by field_name and bug_field_id
  while ($field_array = db_fetch_array($res_defaults))
    {
      $BF_USAGE_BY_ID[$field_array['bug_field_id'] ] = $field_array;
      $BF_USAGE_BY_NAME[$field_array['field_name'] ] = $field_array;
    }

  # Then select  all project specific entries
  $sql='SELECT  '.ARTIFACT.'_field.bug_field_id, field_name, display_type, '.
     'display_size,label, description,scope,required,empty_ok,keep_history,special, custom, '.
     'group_id, use_it, show_on_add, show_on_add_members, place, custom_label,'.
     'custom_description,custom_display_size,custom_empty_ok,custom_keep_history '.
     'FROM '.ARTIFACT.'_field,  '.ARTIFACT.'_field_usage '.
     'WHERE group_id='.$group_id.
     ' AND  '.ARTIFACT.'_field.bug_field_id= '.ARTIFACT.'_field_usage.bug_field_id ';


  $res_project = db_query($sql);

  # And override entries in the default array
  while ($field_array = db_fetch_array($res_project))
    {
      $BF_USAGE_BY_ID[$field_array['bug_field_id'] ] = $field_array;
      $BF_USAGE_BY_NAME[$field_array['field_name'] ] = $field_array;
    }

  #Debug code
  #print "<br />DBG - At end of bug_get_all_fields: $rows";
  #reset($BF_USAGE_BY_NAME);
  #while (list($key, $val) = each($BF_USAGE_BY_NAME))
  #{
  #print "<br />DBG - $key -> use_it: $val[use_it], $val[place]";
  #}

  # rewind internal pointer of global arrays
  reset($BF_USAGE_BY_ID);
  reset($BF_USAGE_BY_NAME);
  $AT_START = true;
}


function trackers_data_get_item_group($item_id)
{
  return  db_result(
                    db_query("SELECT group_id FROM ".ARTIFACT." WHERE bug_id='$item_id'"),
                    0,
                    'group_id');
}

function &trackers_data_get_notification_settings($group_id, $tracker_name)
{
  $result = db_query("SELECT * FROM groups WHERE group_id=$group_id");
  if (db_numrows($result) < 1)
    {
      exit_no_group();
    }

  $settings = array();

  $settings['glnotif'] = db_result($result,0,$tracker_name."_glnotif");
  $settings['glsendall'] = db_result($result,0,"send_all_".$tracker_name);
  $settings['glnewad'] = db_result($result,0,"new_".$tracker_name."_address");
  $settings['private_exclude'] = db_result($result,0,$tracker_name."_private_exclude_address");

  $cat_field_name = "category_id";
  # Warning: The hardcoded fiels names: bug_fv_id and bug_field_id will need to be changed
  #          one day to generic names since they apply to bugs but also to suuports,tasks,
  #          and patch related tables. For now these fileds are called bug_xxx whatever
  #          the service related tables. Too much work to make all the changes in the code.
  $sql="SELECT ".$tracker_name."_field_value.bug_fv_id,".$tracker_name."_field_value.value,".$tracker_name."_field_value.email_ad,".$tracker_name."_field_value.send_all_flag ".
    "FROM ".$tracker_name."_field, ".$tracker_name."_field_value ".
    "WHERE ".$tracker_name."_field_value.group_id='$group_id' ".
    "AND ".$tracker_name."_field.field_name='$cat_field_name' ".
    "AND ".$tracker_name."_field_value.bug_field_id=".$tracker_name."_field.bug_field_id ".
    "AND ".$tracker_name."_field_value.status!='H'";

  $result=db_query($sql);
  $settings['nb_categories']=db_numrows($result);
  $settings['category'] = array();
  for ($i=0; $i < $settings['nb_categories'] ; $i++) {

    $settings['category'][$i] = array();
    $settings['category'][$i]['name'] = db_result($result, $i, 'value');
    $settings['category'][$i]['fv_id'] = db_result($result, $i, 'bug_fv_id');
    $email = db_result($result, $i, 'email_ad');
    if ($email == '100')
      { $email = ""; }
    $settings['category'][$i]['email'] = $email;
    $settings['category'][$i]['send_all_flag'] = db_result($result, $i, 'send_all_flag');
  }
  return $settings;
}

function trackers_data_show_notification_settings($group_id, $tracker_name, $show_intro_msg)
{
  $grtrsettings = &trackers_data_get_notification_settings($group_id, $tracker_name);

  if (user_ismember($group_id,'A'))
    {
      if ($grtrsettings['glnotif'] == 0) {
        $categoryradio = "CHECKED";
        $globalradio = "";
        $bothradio = "";
      }
      if ($grtrsettings['glnotif'] == 1) {
        $categoryradio = "";
        $globalradio = "CHECKED";
        $bothradio = "";
      }
      if ($grtrsettings['glnotif'] == 2) {
        $categoryradio = "";
        $globalradio = "";
        $bothradio = "CHECKED";
      }
      if ($grtrsettings['nb_categories'] > 0) {
        if ($show_intro_msg != 0) {
          print '<p>'.sprintf(_("As a project administrator you must decide if the list of persons to be systematically notified on new %s submissions (and possibly updates) depend on the categories or not and you must provide the corresponding email addresses (comma separated list)."), $tracker_name).'</p>';

        }
        print '
           <INPUT TYPE="RADIO" NAME="'.$tracker_name.'_notif_scope" VALUE="global" '.$globalradio.' />&nbsp;&nbsp;<span class="preinput">'._("Notify persons in the global list only").'</span><br />
          <INPUT TYPE="RADIO" NAME="'.$tracker_name.'_notif_scope" VALUE="category" '.$categoryradio.' />&nbsp;&nbsp;<span class="preinput">'._("Notify persons in the category related list instead of the global list").'</span><br />
          <INPUT TYPE="RADIO" NAME="'.$tracker_name.'_notif_scope" VALUE="both" '.$bothradio.' />&nbsp;&nbsp;<span class="preinput">'._("Notify persons in the category related list in addition to the global list").'</span><br />


          <h4>'._("Category related lists").'</h4>';
        print '<INPUT TYPE="HIDDEN" NAME="'.$tracker_name.'_nb_categories" VALUE="'.$grtrsettings['nb_categories'].'" />';

        for ($i=0; $i < $grtrsettings['nb_categories'] ; $i++)
          {
            print '<INPUT TYPE="HIDDEN" NAME="'.$tracker_name.'_cat_'.$i.'_bug_fv_id" VALUE="'.$grtrsettings['category'][$i]['fv_id'].'" />';
            print '<span class="preinput">'.$grtrsettings['category'][$i]['name'].'</span><br />&nbsp;&nbsp;<INPUT TYPE="TEXT" NAME="'.$tracker_name.'_cat_'.$i.'_email" VALUE="'.$grtrsettings['category'][$i]['email'].'" SIZE="50" MAXLENGTH="255" />
          &nbsp;&nbsp;<span class="preinput">(
          <INPUT TYPE="CHECKBOX" NAME="'.$tracker_name.'_cat_'.$i.'_send_all_flag" VALUE="1" '. (($grtrsettings['category'][$i]['send_all_flag'])?'checked="checked"':'') .' />'._("Send on all updates").')</span><br />
';
          }

        print '<h4>'._("Global list").'</h4>';

      } else {
        if ($show_intro_msg != 0) {
          print '<p>'.sprintf(_("As a project administrator you must decide if the list of persons to be systematically notified on new %s submissions (and possibly updates) depend on the categories or not and you must provide the corresponding email addresses (comma separated list)."), $tracker_name).'</p>';
        }
      }
      print '<span class="preinput">'._("Global List:").'</span><br />&nbsp;&nbsp;<INPUT TYPE="TEXT" NAME="'.$tracker_name.'_new_item_address" VALUE="'.$grtrsettings['glnewad'].'" SIZE="50" MAXLENGTH="255" />
      &nbsp;&nbsp;<span class="preinput">(<INPUT TYPE="CHECKBOX" NAME="'.$tracker_name.'_send_all_changes" VALUE="1" '. (($grtrsettings['glsendall'])?'CHECKED':'') .'>'._("Send on all updates").')</span>';

        print '<h4>'._("Private items exclude list").'</h4>';
        if ($show_intro_msg != 0) {
          print '<p>'._("Addresses registered in this list will be excluded from default mail notification for private items.").'</p>';
        }

        print '<span class="preinput">'._("Exclude List:").'</span><br />&nbsp;&nbsp;<INPUT TYPE="TEXT" NAME="'.$tracker_name.'_private_exclude_address" VALUE="'.$grtrsettings['private_exclude'].'" SIZE="50" MAXLENGTH="255" /><br />';

    }
}

function trackers_data_post_notification_settings($group_id, $tracker_name)
{

  global $feedback;

  $local_feedback = "";
  # build the variable names related to elements always present in the form
  # and get their values

  $notif_scope_name = $tracker_name."_notif_scope";
  $notif_scope = $GLOBALS[$notif_scope_name];
  $new_item_address_name = $tracker_name."_new_item_address";
  $new_item_address = $GLOBALS[$new_item_address_name];
  $send_all_changes_name = $tracker_name."_send_all_changes";
  $send_all_changes = $GLOBALS[$send_all_changes_name];
  $nb_categories_name = $tracker_name."_nb_categories";
  $nb_categories = $GLOBALS[$nb_categories_name];
  $private_exclude_address_name = $tracker_name."_private_exclude_address";
  $private_exclude_address = $GLOBALS[$private_exclude_address_name];

  if ($notif_scope != "global") {
    if ($notif_scope == "category") {
      $notif_value = 0;
    }
    if ($notif_scope == "both") {
      $notif_value = 2;
    }
  } else {
    $notif_value = 1;
  }

  # set global notification info for this group
  $res_gl=db_query("UPDATE groups SET "
        .$tracker_name."_glnotif='$notif_value', "
        ."send_all_".$tracker_name."='$send_all_changes', "
        ."new_".$tracker_name."_address=".($new_item_address? "'$new_item_address' " : "''").", "
        .$private_exclude_address_name.'='.($private_exclude_address? "'$private_exclude_address' " : "''")
        . " WHERE group_id=$group_id");
  if (!$res_gl)
    { $local_feedback .= _("groups table Update failed.").' '.db_error(); }

  $ok = 0;
  if ($nb_categories > 0) {
    for ($i=0; $i<$nb_categories; $i++) {
      $current_fv_name = $tracker_name."_cat_".$i."_bug_fv_id";
      $current_fv_id = $GLOBALS[$current_fv_name];
      $current_email_name = $tracker_name."_cat_".$i."_email";
      $current_email = $GLOBALS[$current_email_name];
#      if ($current_email && !validate_email($current_email))
#        {
#          $local_feedback .= _("[".$tracker_name."]  notification address: ".$current_email." appeared Invalid");
#          $current_email='';
#        }
      $current_send_all_name = $tracker_name."_cat_".$i."_send_all_flag";
      $current_send_all_flag = $GLOBALS[$current_send_all_name];

      $res_cat=db_query("UPDATE ".$tracker_name."_field_value SET "
             ."email_ad='$current_email', "
             ."send_all_flag='$current_send_all_flag' "
             ." WHERE bug_fv_id=$current_fv_id");
      if ($res_cat) {
        $ok++;
      } else {
        $local_feedback .= _($tracker_name."_field_value table Update failed.").' '.db_error();
      }
    }
  }
  if (($res_gl) && ($ok == $nb_categories) && ($local_feedback == ""))  {
    return 1;
  } else {
    if ($local_feedback != "") { fb($local_feedback); }
    return 0;
  }
}

function trackers_data_get_item_notification_info($item_id, $artifact, $updated)
{
  $emailad = "";
  $sendemail = 0;
  # Get group information bur new entity notification settings
  $sql="SELECT groups.$artifact"."_glnotif, groups.send_all_"."$artifact, groups.new_"."$artifact"."_address ".
     "FROM "."$artifact, groups ".
     "WHERE "."$artifact.bug_id='$item_id' ".
     "AND groups.group_id="."$artifact.group_id";
  $result=db_query($sql);

  $glnotif = db_result($result,0,$artifact."_glnotif");
  $glsendall = db_result($result,0,"send_all_".$artifact);
  $glnewad = db_result($result,0,"new_".$artifact."_address");
  if ($glnotif != 1) {   # if not 'global only'
    $cat_field_name = "category_id";

    $sql="SELECT "."$artifact"."_field_value.email_ad, "."$artifact"."_field_value.send_all_flag ".
       "FROM "."$artifact"."_field_value, "."$artifact"."_field, $artifact ".
       "WHERE "."$artifact.bug_id='$item_id' ".
       "AND "."$artifact"."_field.field_name='$cat_field_name' ".
       "AND "."$artifact"."_field_value.bug_field_id="."$artifact"."_field.bug_field_id ".
       "AND "."$artifact"."_field_value.group_id="."$artifact.group_id ".
       "AND "."$artifact"."_field_value.value_id="."$artifact.category_id";

    $result=db_query($sql);
    $rows=db_numrows($result);
    if ($rows > 0) {
      $sendallflag = db_result($result, 0, 'send_all_flag');
      if (($updated == 0) || (($updated == 1) && ($sendallflag == 1))) {
        $emailad .= db_result($result, 0, 'email_ad');
      }
    } else {
      # could be that administrator closes category notification and forgot
      # to define categories BUT in most cases it means the submitter selected
      # the 'NONE' category for this bug
      if (($updated == 0) || (($updated == 1) && ($glsendall == 1))) {
        $emailad .= $glnewad;
      }
    }
  }
  if ($glnotif > 0) {   # if not 'category only'
    if (($updated == 0) || (($updated == 1) && ($glsendall == 1))) {
      if ($emailad != "") {
        $emailad .= ',';
      }
      $emailad .= $glnewad;
    }
  }
  if (trim($emailad) != "") {
    $sendemail = 1;
  }
  #  print "EMAILAD=$emailad SENDEMAIL=$sendemail";
  return array($emailad, $sendemail);
}


function cmp_place($ar1, $ar2)
{
  if ($ar1['place']< $ar2['place'])
    return -1;
  else if ($ar1['place']>$ar2['place'])
    return 1;
  return 0;
}

function cmp_place_query($ar1, $ar2)
{
  if ($ar1['place_query']< $ar2['place_query'])
    return -1;
  else if ($ar1['place_query']>$ar2['place_query'])
    return 1;
  return 0;
}

function cmp_place_result($ar1, $ar2)
{
  if ($ar1['place_result']< $ar2['place_result'])
    return -1;
  else if ($ar1['place_result']>$ar2['place_result'])
    return 1;
  return 0;
}

function trackers_data_get_all_report_fields($group_id=false,$report_id=100)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;

  /*
           Get all the bug fields involved in the bug report.
           WARNING: This function ust only be called after bug_init()
  */

  # Build the list of fields involved in this report
  $sql = "SELECT * FROM ".ARTIFACT."_report_field WHERE report_id='".safeinput($report_id)."'";
  $res = db_query($sql);

  while ($arr = db_fetch_array($res))
    {
      $field = $arr['field_name'];
      $field_id = trackers_data_get_field_id($field);
      $BF_USAGE_BY_NAME[$field]['show_on_query'] =
         $BF_USAGE_BY_ID[$field_id]['show_on_query'] = $arr['show_on_query'];

      $BF_USAGE_BY_NAME[$field]['show_on_result'] =
         $BF_USAGE_BY_ID[$field_id]['show_on_result'] = $arr['show_on_result'];

      $BF_USAGE_BY_NAME[$field]['place_query'] =
         $BF_USAGE_BY_ID[$field_id]['place_query'] = $arr['place_query'];

      $BF_USAGE_BY_NAME[$field]['place_result'] =
         $BF_USAGE_BY_ID[$field_id]['place_result'] = $arr['place_result'];

      $BF_USAGE_BY_NAME[$field]['col_width'] =
         $BF_USAGE_BY_ID[$field_id]['col_width'] = $arr['col_width'];
    }
}

function trackers_data_get_field_predefined_values ($field, $group_id=false, $checked=false,$by_field_id=false,$active_only=true)
{

  /*
             Return all possible values for a select box field
             Rk: if the checked value is given then it means that we want this value
                  in the list in any case (even if it is hidden and active_only is requested)
  */
  $field_id = ($by_field_id ? $field : trackers_data_get_field_id($field));
  $field_name = ($by_field_id ? trackers_data_get_field_name($field) : $field);

  # The "Assigned_to" box requires some special processing
  # because possible values  are project members) and they are
  # not stored in the trackers_field_value table but in the user_group table
  if ($field_name == 'assigned_to')
    {
      $res_value = trackers_data_get_technicians($group_id);
    }
  else if ($field_name == 'submitted_by')
    {
      $res_value = trackers_data_get_submitters($group_id);
    }
  else
    {

      # If only active field
      if ($active_only)
        {
          if ($checked)
            {
              $status_cond = "AND  (status IN ('A','P') OR value_id='$checked') ";
            }
          else
            {
              $status_cond = "AND  status IN ('A','P') ";
            }
        }

      # CAUTION !! the fields value_id and value must be first in the
      # select statement because the output is used in the html_build_select_box
      # function

      # yeupou@gnu.org 2003-11-24
      # FIXME!!!!! WHAT IS THIS CRAP!
      # It _on purpose_ ignores the permanent values for the
      # system when a group have his own values.
      # And  when creating group specific values, it insert the permanent
      # system values in the group specific values.
      #
      # Can someone bring a reasonnable explanation for such a behavior?
      #   - permanent field must by nature be permanent, in any case!
      #   - database must never duplicates information without good reason
      #
      # When improving this code, please change that so it uses the permanent
      # values in any case, whatever the group specific values may be.

      # Look for project specific values first
      $sql="SELECT value_id,value,bug_fv_id,bug_field_id,group_id,description,order_id,status ".
         "FROM ".ARTIFACT."_field_value ".
         "WHERE group_id=$group_id AND bug_field_id=$field_id ".
         $status_cond." ORDER BY order_id,value ASC";
      $res_value = db_query($sql);
      $rows=db_numrows($res_value);

      # If no specific value for this group then look for default values
      if ($rows == 0)
        {
          $sql="SELECT value_id,value,bug_fv_id,bug_field_id,group_id,description,order_id,status ".
             "FROM ".ARTIFACT."_field_value ".
             "WHERE group_id=100 AND bug_field_id=$field_id ".
             $status_cond." ORDER BY order_id,value ASC";
          $res_value = db_query($sql);
          $rows=db_numrows($res_value);
        }
    }

  return($res_value);

}

function trackers_data_use_field_predefined_values ($field, $group_id)
{
  # Check whether a group field values are the default one or not.
  # If no entry in the database for the relevant field value belong to the
  # group, then it uses default values (fallback)
  $field_id = trackers_data_get_field_id($field);
  $sql="SELECT bug_fv_id FROM ".ARTIFACT."_field_value ".
             "WHERE group_id=".$group_id." AND bug_field_id=$field_id";

  $result = db_query($sql);
  return db_numrows($result);
}


function trackers_data_is_custom($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['custom']: $BF_USAGE_BY_NAME[$field]['custom']);
}

function trackers_data_is_special($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['special']: $BF_USAGE_BY_NAME[$field]['special']);
}

# deprecated
function trackers_data_is_empty_ok ($field, $by_field_id=false)
{
  return trackers_data_mandatory_flag($field, $by_field_id);
}

function trackers_data_mandatory_flag ($field, $by_field_id=false)
{
  # 1 = not mandatory
  # 0 = relaxed mandatory (mandatory if it was to the submitter)
  # 3 = mandatory whenever possible
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $val = $BF_USAGE_BY_ID[$field]['custom_empty_ok'];
      if (!isset($val))
        { $val = $BF_USAGE_BY_ID[$field]['empty_ok']; }
    }  else
      {
        $val = $BF_USAGE_BY_NAME[$field]['custom_empty_ok'];
        if (!isset($val))
          { $val = $BF_USAGE_BY_NAME[$field]['empty_ok']; }
      }
  return($val);
}


function trackers_data_do_keep_history($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $val = $BF_USAGE_BY_ID[$field]['custom_keep_history'];
      if (!isset($val))
        { $val = $BF_USAGE_BY_ID[$field]['empty_keep_history']; }
    }  else
      {
        $val = $BF_USAGE_BY_NAME[$field]['custom_keep_history'];
        if (!isset($val))
          { $val = $BF_USAGE_BY_NAME[$field]['keep_history']; }
      }
  return($val);
}

function trackers_data_is_required($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['required']: $BF_USAGE_BY_NAME[$field]['required']);
}

function trackers_data_is_used($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['use_it']: $BF_USAGE_BY_NAME[$field]['use_it']);
}

function trackers_data_is_showed_on_query($field)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['show_on_query']: $BF_USAGE_BY_NAME[$field]['show_on_query']);

}

function trackers_data_is_showed_on_result($field)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['show_on_result']: $BF_USAGE_BY_NAME[$field]['show_on_result']);
}

# return a TRUE value if non project members who still are
# logged in users should be able to access this field
# (first bit of show_on_add set)
function trackers_data_is_showed_on_add($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['show_on_add'] & 1: $BF_USAGE_BY_NAME[$field]['show_on_add'] & 1);
}

# return a TRUE value if non logged in users should be able to
# access this field (second bit of show_on_add set)
function trackers_data_is_showed_on_add_nologin($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['show_on_add'] & 2: $BF_USAGE_BY_NAME[$field]['show_on_add'] & 2);
}

# return a TRUE value if project members should be able to
# access this field
function trackers_data_is_showed_on_add_members($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['show_on_add_members']: $BF_USAGE_BY_NAME[$field]['show_on_add_members']);
}

function trackers_data_is_date_field($field, $by_field_id=false)
{
  return(trackers_data_get_display_type($field, $by_field_id) == 'DF');
}

function trackers_data_is_text_field($field, $by_field_id=false)
{
  return(trackers_data_get_display_type($field, $by_field_id) == 'TF');
}

function trackers_data_is_text_area($field, $by_field_id=false)
{
  return(trackers_data_get_display_type($field, $by_field_id) == 'TA');
}

function trackers_data_is_select_box($field, $by_field_id=false)
{
  return(trackers_data_get_display_type($field, $by_field_id) == 'SB');
}

function trackers_data_is_username_field($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID;
  if ($by_field_id)
    {
      $field = trackers_data_get_field_name($field);
    }
  return(($field == 'assigned_to') || ($field == 'submitted_by'));
}

function trackers_data_is_project_scope($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      return($BF_USAGE_BY_ID[$field]['scope'] == 'P');
    }
  else
    {
      return($BF_USAGE_BY_NAME[$field]['scope'] == 'P');
    }
}

function trackers_data_is_status_closed($status)
{
  if ($status == '3')
    { return 1; }
  return 0;
}

function trackers_data_get_field_name($field_id)
{
  global $BF_USAGE_BY_ID;
  return($BF_USAGE_BY_ID[$field_id]['field_name']);
}

function trackers_data_get_field_id($field_name)
{
  global $BF_USAGE_BY_NAME;
  return($BF_USAGE_BY_NAME[$field_name]['bug_field_id']);
}

function trackers_data_get_group_id($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['group_id'] : $BF_USAGE_BY_NAME[$field]['group_id']);
}

function trackers_data_get_label($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $lbl = $BF_USAGE_BY_ID[$field]['custom_label'];
      if (!isset($lbl))
        { $lbl = $BF_USAGE_BY_ID[$field]['label']; }
    }  else
      {
        $lbl = $BF_USAGE_BY_NAME[$field]['custom_label'];
        if (!isset($lbl))
          { $lbl = $BF_USAGE_BY_NAME[$field]['label']; }
      }
  return($lbl);
}

function trackers_data_get_description($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $desc = $BF_USAGE_BY_ID[$field]['custom_description'];
      if (!isset($desc))
        { $desc = $BF_USAGE_BY_ID[$field]['description']; }
    }  else
      {
        $desc = $BF_USAGE_BY_NAME[$field]['custom_description'];
        if (!isset($desc))
          { $desc = $BF_USAGE_BY_NAME[$field]['description']; }
      }
  return($desc);
}

function trackers_data_get_display_type($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['display_type'] : $BF_USAGE_BY_NAME[$field]['display_type']);
}

function trackers_data_get_display_type_in_clear($field, $by_field_id=false)
{
  if (trackers_data_is_select_box($field, $by_field_id))
    {
      return 'Select Box';
    }
  else if (trackers_data_is_text_field($field, $by_field_id))
    {
      return 'Text Field';
    }
  else if (trackers_data_is_text_area($field, $by_field_id))
    {
      return 'Text Area';
    }
  else if (trackers_data_is_date_field($field, $by_field_id))
    {
      return 'Date Field';
    }
  else
    {
      return '?';
    }
}


function trackers_data_get_keep_history($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $val = $BF_USAGE_BY_ID[$field]['custom_keep_history'];
      if (!isset($val))
        { $val = $BF_USAGE_BY_ID[$field]['keep_history']; }
    }  else
      {
        $val = $BF_USAGE_BY_NAME[$field]['custom_keep_history'];
        if (!isset($val))
          { $val = $BF_USAGE_BY_NAME[$field]['keep_history']; }
      }
  return($val);
}

function trackers_data_get_place($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['place'] : $BF_USAGE_BY_NAME[$field]['place']);
}

function trackers_data_get_scope($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['scope'] : $BF_USAGE_BY_NAME[$field]['scope']);
}

function trackers_data_get_col_width($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  return($by_field_id ? $BF_USAGE_BY_ID[$field]['col_width'] : $BF_USAGE_BY_NAME[$field]['col_width']);
}

function trackers_data_get_display_size($field, $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  if ($by_field_id)
    {
      $val = $BF_USAGE_BY_ID[$field]['custom_display_size'];
      if (!isset($val))
        { $val = $BF_USAGE_BY_ID[$field]['display_size']; }
    }  else
      {
        $val = $BF_USAGE_BY_NAME[$field]['custom_display_size'];
        if (!isset($val))
          { $val = $BF_USAGE_BY_NAME[$field]['display_size']; }
      }
  return(explode('/',$val));
}

function trackers_data_get_default_value($field,  $by_field_id=false)
{
  global $BF_USAGE_BY_ID,$BF_USAGE_BY_NAME;
  /*
      Return the default value associated to a field_name as defined in the
      bug table (SQL definition)
  */
  if ($by_field_id)
    {
      $field = trackers_data_get_field_name($field);
    }

  $result = db_query('describe bug '.$field);
  return (db_result($result,0,'Default'));

}

function trackers_data_get_max_value_id($field, $group_id, $by_field_id=false)
{

  /*
      Find the maximum value for the value_id of a field for a given group
      Return -1 if  no value exist yet
  */

  if (!$by_field_id)
    {
      $field_id = trackers_data_get_field_id($field);
    }

  $sql="SELECT max(value_id) as max FROM ".ARTIFACT."_field_value ".
     "WHERE bug_field_id='$field_id' AND group_id='$group_id' ";
  $res = db_query($sql);
  $rows = db_numrows($res);

  # If no max value found then it means it's the first value for this field
  # in this group. Return -1 in this case
  if ($rows == 0)
    {
      return(-1);
    }  else
      {
        return(db_result($res,0,'max'));
      }

}

function trackers_data_is_value_set_empty($field, $group_id, $by_field_id=false)
{

  /*
      Return true if there is an existing set of values for given field for a
      given group and false if it is empty
  */

  if (!$by_field_id)
    {
      $field_id = trackers_data_get_field_id($field);
    }
  $sql="SELECT value_id FROM ".ARTIFACT."_field_value ".
     "WHERE bug_field_id='$field_id' AND group_id='$group_id' ";
  $res = db_query($sql);
  $rows=db_numrows($res);

  return (($rows<=0));
}


function trackers_data_copy_default_values($field, $group_id, $by_field_id=false)
{
  /*
      Initialize the set of values for a given field for a given group by using
      the system default (default values belong to group_id 'None' =100)
  */

  if (!$by_field_id)
    {
      $field_id = trackers_data_get_field_id($field);
    }

  # if group_id=100 (None) it is a null operation
  # because default values belong to group_id 100 by definition
  if ($group_id != 100)
    {

      # First delete the exisiting value if any
      $sql="DELETE FROM ".ARTIFACT."_field_value ".
         "WHERE bug_field_id='$field_id' AND group_id='$group_id' ";
      $res = db_query($sql);

      # Second insert default values (if any) from group 'None'
      # Rk: The target table of the INSERT statement cannot appear in
      # the FROM clause of the SELECT part of the query because it's forbidden
      # in ANSI SQL to SELECT . So do it by hand !
      #

      $sql = "SELECT value_id,value,description,order_id,status ".
         "FROM ".ARTIFACT."_field_value ".
         "WHERE bug_field_id='$field_id' AND group_id='100'";
      $res = db_query($sql);
      $rows = db_numrows($res);

      for ($i=0; $i<$rows; $i++)
        {

          $value_id = addslashes(db_result($res,$i,'value_id'));
          $value = db_result($res,$i,'value');
          $description = addslashes(db_result($res,$i,'description'));
          $order_id = db_result($res,$i,'order_id');
          $status  = db_result($res,$i,'status');


          $sql="INSERT INTO ".ARTIFACT."_field_value ".
             "(bug_field_id,group_id,value_id,value,description,order_id,status) ".
             "VALUES ('$field_id','$group_id','$value_id','$value','$description','$order_id','$status')";
          #print "<BR>DBG - $sql";
          $res_insert = db_query($sql);

          if (db_affected_rows($res_insert) < 1)
            {
              fb(_("Insert of default value failed."), 0);
              db_error();
            }
        }
    }
}

function trackers_data_get_cached_field_value($field,$group_id,$value_id)
{
  global $BF_VALUE_BY_NAME;

  if (!isset($BF_VALUE_BY_NAME[$field][$value_id]))
    {
      $res = trackers_data_get_field_predefined_values ($field, $group_id,false,false,false);

      while ($fv_array = db_fetch_array($res))
        {
          # $fv_array[0] has the value_id and [1] is the value
          $BF_VALUE_BY_NAME[$field][$fv_array['value_id']] = $fv_array[1];
        }
    }

  return $BF_VALUE_BY_NAME[$field][$value_id];
}

function trackers_data_get_field_value ($item_fv_id)
{
  /*
      Get all the columns associated to a given field value
  */

  $sql = "SELECT * FROM ".ARTIFACT."_field_value WHERE bug_fv_id='$item_fv_id'";
  $res = db_query($sql);
  return($res);
}

function trackers_data_is_default_value ($item_fv_id)
{
  /*
      See if this field value belongs to group None (100). In this case
      it is a so called default value.
  */

  $sql = "SELECT bug_field_id,value_id FROM ".ARTIFACT."_field_value WHERE bug_fv_id='$item_fv_id' AND group_id='100'";
  $res = db_query($sql);

  return ( (db_numrows($res) >= 1) ? $res : false);
}

function trackers_data_create_value ($field, $group_id, $value, $description,$order_id,$status='A',$by_field_id=false)
{

  global $feedback,$ffeedback;

  /*
      Insert a new value for a given field for a given group
  */

  # An empty field value is not allowed
  if (preg_match ("/^\s*$/", $value))
    {
      fb(_("Empty field value not allowed"), 0);
      return;
    }

  if (!$by_field_id)
    {
      $field_id = trackers_data_get_field_id($field);
    }

  # if group_id=100 (None) then do nothing
  # because no real project should have the group number '100'
  if ($group_id != 100)
    {

      # if the current value set for this project is empty
      # then copy the default values first (if any)
      if (trackers_data_is_value_set_empty($field,$group_id))
        {
          trackers_data_copy_default_values($field,$group_id);
        }

      # Find the next value_id to give to this new value. (Start arbitrarily
      # at 200 if no value exists (and therefore max is undefined)
      $max_value_id = trackers_data_get_max_value_id($field, $group_id);

      if ($max_value_id < 0)
        {
          $value_id = 200;
        }
      else
        {
          $value_id = $max_value_id +1;
        }


      $sql = "INSERT INTO ".ARTIFACT."_field_value ".
         "(bug_field_id,group_id,value_id,value,description,order_id,status) ".
         "VALUES ('$field_id','$group_id','$value_id','$value','$description','$order_id','$status')";
      db_query($sql);

      if (db_affected_rows($result) < 1)
        {
          fb(_("Insert failed."), 0);
          ' - '.db_error();
        }
      else

        {
          fb(_("New field value inserted."));
        }
    }
}


function trackers_data_update_value ($item_fv_id,$field,$group_id,$value,$description,$order_id,$status='A')
{

  global $feedback,$ffeedback;
  /*
      Insert a new value for a given field for a given group
  */

  # An empty field value is not allowed
  if (preg_match ("/^\s*$/", $value))
    {
      fb(_("Empty field value not allowed"), 0);
      return;
    }

  # Updating a bug field value that belong to group 100 (None) is
  # forbidden. These are default values that cannot be changed so
  # make sure to copy the default values first in the project context first

  if ($res = trackers_data_is_default_value($item_fv_id))
    {
      trackers_data_copy_default_values($field,$group_id);

      $arr = db_fetch_array($res);
      $where_cond = 'bug_field_id='.$arr['bug_field_id'].
         ' AND value_id='.$arr['value_id']." AND group_id='$group_id' ";
    }
  else
    {
      $where_cond = "bug_fv_id='$item_fv_id' AND group_id<>'100'";
    }

  # Now perform the value update
  $sql = "UPDATE ".ARTIFACT."_field_value ".
     "SET value='$value',description='$description',order_id='$order_id',status='$status' ".
     "WHERE $where_cond";
  $result = db_query($sql);

  #print "<BR>DBG - $sql";

  if (db_affected_rows($result) < 1)
    {
      fb(_("Update of field value failed."));
    }
  else
    {
      fb(_("New field value updated."));
    }
}

function trackers_data_reset_usage($field_name,$group_id)
{
  global $feedback;
  /*
      Reset a field settings to its defaults usage (values are untouched). The defaults
      always belong to group_id 100 (None) so make sure we don;t delete entries for
      group 100
  */
  $field_id = trackers_data_get_field_id($field_name);
  if ($group_id != 100)
    {
      $sql = "DELETE FROM ".ARTIFACT."_field_usage ".
         "WHERE group_id='$group_id' AND bug_field_id='$field_id'";
      db_query($sql);
      fb(_("Field value successfully reset to defaults."));

    }
}

function trackers_data_update_usage($field_name,
                                    $group_id,
                                    $label,
                                    $description,
                                    $use_it,
                                    $rank,
                                    $display_size,
                                    $empty_ok,
                                    $keep_history,
                                    $show_on_add_members=0,
                                    $show_on_add=0,
                                    $transition_default_auth='A')

{

  /*
      Update a field settings in the trackers_usage_table
      Rk: All the show_on_xxx boolean parameters are set to 0 by default because their
           values come from checkboxes and if not checked the form variable
           is not set at all. It must be 0 to be ok with the SQL statement
  */

  # if it's a required field then make sure the use_it flag is true
  if (trackers_data_is_required($field_name))
    {
      $use_it = 1;
    }

  $field_id = trackers_data_get_field_id($field_name);

  # if it's a custom field then take label into account else store NULL
  #    if (trackers_data_is_custom($field_name))  {
  $lbl = isset($label) ? "'$label'" : 'NULL';
  $desc = isset($description) ? "'$description'" : 'NULL';
  $disp_size = isset($display_size) ? "'$display_size'" : 'NULL';
  $empty = isset($empty_ok) ? "'$empty_ok'" : 'NULL';
  $keep_hist = isset($keep_history) ? "'$keep_history'" : 'NULL';
  #    }  else    {
  #     $lbl = $desc = $disp_size = $empty = $keep_hist = "NULL";
  #    }

  # See if this field usage exists in the table for this project
  $sql = 'SELECT bug_field_id FROM '.ARTIFACT.'_field_usage '.
     "WHERE bug_field_id='$field_id' AND group_id='$group_id'";
  $result = db_query($sql);
  $rows = db_numrows($result);

  # if it does exist then update it else insert a new usage entry for this field.
  if ($rows)
    {
      $sql = 'UPDATE '.ARTIFACT.'_field_usage '.
         "SET use_it='$use_it',show_on_add='$show_on_add',".
         "show_on_add_members='$show_on_add_members',place='$rank', ".
         "custom_label=$lbl,  custom_description=$desc,".
         "custom_display_size=$disp_size,  custom_empty_ok=$empty,".
         "custom_keep_history=$keep_hist, ".
         "transition_default_auth='$transition_default_auth' ".
         "WHERE bug_field_id='$field_id' AND group_id='$group_id'";
      $result = db_query($sql);
    }
  else
    {
      $sql = 'INSERT INTO '.ARTIFACT.'_field_usage '.
         "VALUES ('$field_id','$group_id','$use_it','$show_on_add',".
         "'$show_on_add_members','$rank',$lbl,$desc,$disp_size,$empty,$keep_hist,'$transition_default_auth')";
      $result = db_query($sql);
    }

  if (db_affected_rows($result) < 1)
    { fb(_("Update of field usage failed."), 1); }
  else
    { fb(_("Field usage updated.")); }

}

# Get a list of technicians for a tracker
function trackers_data_get_technicians ($group_id)
{
  # FIXME: The cleanest thing would be to issue one SQL command.
  # But we have to handle the fact that "no setting" = get back
  # to the group, or even group type, setting.

  # In fact, this is terrible, we cannot return something else than
  # a mysql result if we do not want to rewrite 25 functions.
  # So we get the appropriate list of users... and finally issue a
  # mysql command only to be able to return a mysql result.
  # Please, propose something better at savannah-dev@gnu.org


  # Get list of members
  $members_sql =  "SELECT user.user_id AS user_id "
     . "FROM user,user_group "
     . "WHERE user.user_id=user_group.user_id AND user_group.group_id=$group_id ";
  $members_res = db_query($members_sql);
  # Build the sql command
  $sql = "SELECT user_id,user_name FROM user WHERE ";
  while ($member = db_fetch_array($members_res))
    {
      if (member_check($member['user_id'], $group_id, member_create_tracker_flag(ARTIFACT).'1'))
        {
          if ($notfirst)
            { $sql .= " OR "; }
          $sql .= " user_id='".$member['user_id']."'";
          $notfirst = 1;
        }
    }
  $sql .= " ORDER BY user_name";
  return db_query($sql);
}

# Get transitions valid for a given tracker as an array