/**
* Returns data for the custom fields weekly report, based on the field and options passed in.
*
* @param integer $fld_id The id of the custom field.
* @param array $cfo_ids An array of option ids.
* @param string $start_date
* @param string $end_date
* @param boolean $per_user Show time spent per user
* @return array An array of data.
*/
public static function getCustomFieldWeeklyReport($fld_id, $cfo_ids, $start_date, $end_date, $per_user = false)
{
$fld_id = (int) $fld_id;
$cfo_ids = (array) $cfo_ids;
// get field values
$options = Custom_Field::getOptions($fld_id, $cfo_ids);
$params = array();
$sql = 'SELECT
iss_id,
SUM(ttr_time_spent) ttr_time_spent_sum,
iss_summary,
iss_customer_id,
iss_private
';
if ($per_user) {
$sql .= ', usr_full_name ';
}
$sql .= '
FROM
{{%time_tracking}},';
if ($per_user) {
$sql .= '{{%user}}, ';
}
$sql .= '
{{%issue}}
WHERE
iss_prj_id=? AND
ttr_created_date BETWEEN ? AND ? AND
ttr_iss_id = iss_id AND
';
$params[] = Auth::getCurrentProject();
$params[] = "{$start_date} 00:00:00";
$params[] = "{$end_date} 23:59:59";
if ($per_user) {
$sql .= ' usr_id = ttr_usr_id AND ';
}
$sql .= '
ttr_iss_id = iss_id
';
if (count($options) > 0) {
$ids = array_keys($options);
$list = DB_Helper::buildList($ids);
$sql .= " AND (\n SELECT\n count(*)\n FROM\n {{%issue_custom_field}} a\n WHERE\n a.icf_fld_id = ? AND\n a.icf_value IN({$list}) AND\n a.icf_iss_id = ttr_iss_id\n ) > 0";
$params[] = $fld_id;
$params = array_merge($params, $ids);
}
if ($per_user) {
$sql .= '
GROUP BY
iss_id, ttr_usr_id';
} else {
$sql .= '
GROUP BY
iss_id';
}
try {
$res = DB_Helper::getInstance()->getAll($sql, $params);
} catch (DbException $e) {
return array();
}
foreach ($res as &$row) {
$row['field_value'] = Custom_Field::getDisplayValue($row['iss_id'], $fld_id);
$row['ttr_time_spent_sum_formatted'] = Misc::getFormattedTime($row['ttr_time_spent_sum'], false);
}
return $res;
}
$tpl = new Template_Helper();
$tpl->setTemplate('reports/custom_fields.tpl.html');
Auth::checkAuthentication();
if (!Access::canAccessReports(Auth::getUserID())) {
echo 'Invalid role';
exit;
}
$prj_id = Auth::getCurrentProject();
// get list of fields and convert info useful arrays
$fields = Custom_Field::getListByProject($prj_id, '');
$custom_fields = array();
$options = array();
if (is_array($fields) && count($fields) > 0) {
foreach ($fields as $field) {
$custom_fields[$field['fld_id']] = $field['fld_title'];
$options[$field['fld_id']] = Custom_Field::getOptions($field['fld_id']);
}
} else {
echo ev_gettext('No custom fields for this project');
exit;
}
if (!empty($_REQUEST['start']['Year']) && !empty($_REQUEST['start']['Month']) && !empty($_REQUEST['start']['Day'])) {
$start = implode('-', $_REQUEST['start']);
} else {
$start = false;
}
if (!empty($_REQUEST['end']['Year']) && !empty($_REQUEST['end']['Month']) && !empty($_REQUEST['end']['Day'])) {
$end = implode('-', $_REQUEST['end']);
} else {
$end = false;
}
/**
* Returns an array of active filters
*
* @param array $options The options array
* @return array
*/
public static function getActiveFilters($options)
{
$prj_id = Auth::getCurrentProject();
$filter_info = self::getFiltersInfo();
$return = array();
foreach ($filter_info as $filter_key => $filter) {
$display = false;
if (isset($filter['param']) && isset($options[$filter['param']])) {
$filter_details = $options[$filter['param']];
}
if (isset($filter['is_custom'])) {
// custom fields
$fld_id = $filter['fld_id'];
if (!isset($options['custom_field'][$fld_id]) || empty($options['custom_field'][$fld_id])) {
continue;
} elseif ($filter['fld_type'] == 'date' && empty($options['custom_field'][$fld_id]['Year'])) {
continue;
} elseif ($filter['fld_type'] == 'integer') {
if (!isset($options['custom_field'][$fld_id]['value']) || empty($options['custom_field'][$fld_id]['value'])) {
continue;
} else {
$filter_details = $options['custom_field'][$fld_id];
switch ($filter_details['filter_type']) {
case 'ge':
$display = ev_gettext('%1$s or greater', $filter_details['value']);
break;
case 'le':
$display = ev_gettext('%1$s or less', $filter_details['value']);
break;
case 'gt':
$display = ev_gettext('Greater than %1$s', $filter_details['value']);
break;
case 'lt':
$display = ev_gettext('Less than %1$s', $filter_details['value']);
break;
default:
$display = $filter_details['value'];
}
}
} elseif (in_array($filter['fld_type'], array('multiple', 'combo'))) {
$display = implode(', ', Custom_Field::getOptions($fld_id, $options['custom_field'][$fld_id]));
} else {
$display = $options['custom_field'][$fld_id];
}
} elseif (!isset($options[$filter['param']]) || empty($options[$filter['param']]) || in_array($filter_key, array('sort_order', 'sort_by', 'rows', 'search_type'))) {
continue;
} elseif (isset($filter['is_date']) && $filter['is_date'] == true) {
if (!empty($filter_details['Year']) || isset($filter_details['time_period'])) {
switch ($filter_details['filter_type']) {
case 'in_past':
$display = ev_gettext('In Past %1$s hours', $filter_details['time_period']);
break;
case 'null':
$display = ev_gettext('Is NULL');
break;
case 'between':
$end = $options[$filter['param'] . '_end'];
$display = ev_gettext('Is between %1$s-%2$s-%3$s AND %4$s-%5$s-%6$s', $filter_details['Year'], $filter_details['Month'], $filter_details['Day'], $end['Year'], $end['Month'], $end['Day']);
break;
case 'greater':
$display = ev_gettext('Is greater than %1$s-%2$s-%3$s', $filter_details['Year'], $filter_details['Month'], $filter_details['Day']);
break;
case 'less':
$display = ev_gettext('Is less than %1$s-%2$s-%3$s', $filter_details['Year'], $filter_details['Month'], $filter_details['Day']);
}
}
} elseif ($filter['param'] == 'status') {
$statuses = Status::getAssocStatusList($prj_id);
$display = $statuses[$filter_details];
} elseif ($filter['param'] == 'category') {
$categories = Category::getAssocList($prj_id);
if (is_array($filter_details)) {
$active_categories = array();
foreach ($filter_details as $category) {
$active_categories[] = $categories[$category];
}
$display = implode(', ', $active_categories);
} else {
$display = $categories[$filter_details];
}
} elseif ($filter['param'] == 'priority') {
$priorities = Priority::getAssocList($prj_id);
$display = $priorities[$filter_details];
} elseif ($filter['param'] == 'severity') {
$severities = Severity::getAssocList($prj_id);
$display = $severities[$filter_details];
} elseif ($filter['param'] == 'users') {
if ($filter_details == -1) {
$display = ev_gettext('un-assigned');
} elseif ($filter_details == -2) {
$display = ev_gettext('myself and un-assigned');
} elseif ($filter_details == -3) {
$display = ev_gettext('myself and my group');
} elseif ($filter_details == -4) {
$display = ev_gettext('myself, un-assigned and my group');
} elseif (substr($filter_details, 0, 3) == 'grp') {
$display = ev_gettext('%1$s Group', Group::getName(substr($filter_details, 4)));
} else {
$display = User::getFullName($filter_details);
}
} elseif ($filter['param'] == 'hide_closed') {
if ($filter_details == true) {
$display = ev_gettext('Yes');
}
} elseif ($filter['param'] == 'reporter') {
$display = User::getFullName($filter_details);
} elseif ($filter['param'] == 'release') {
$display = Release::getTitle($filter_details);
} elseif ($filter['param'] == 'customer_id') {
try {
$crm = CRM::getInstance($prj_id);
$customer = $crm->getCustomer($filter_details);
$display = $customer->getName();
} catch (CRMException $e) {
$display = $filter_details;
}
} elseif ($filter['param'] == 'product') {
$display = Product::getTitle($filter_details);
} else {
$display = $filter_details;
}
if ($display != false) {
$return[$filter['title']] = array('value' => $display, 'remove_link' => 'list.php?view=clearandfilter&' . self::buildUrl($filter_info, $options, $filter_key, true));
}
}
return $return;
}
/**
* Method used to get the details of a specific custom field.
*
* @access public
* @param integer $fld_id The custom field ID
* @return array The custom field details
*/
function getDetails($fld_id)
{
static $returns;
if (isset($returns[$fld_id])) {
return $returns[$fld_id];
}
$stmt = "SELECT\n *\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field\n WHERE\n fld_id=" . Misc::escapeInteger($fld_id);
$res = $GLOBALS["db_api"]->dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
$res["projects"] = @array_keys(Custom_Field::getAssociatedProjects($fld_id));
$t = array();
$options = Custom_Field::getOptions($fld_id);
foreach ($options as $cfo_id => $cfo_value) {
$res["field_options"]["existing:" . $cfo_id . ":" . $cfo_value] = $cfo_value;
}
$returns[$fld_id] = $res;
return $res;
}
}
include_once APP_INC_PATH . "class.template.php";
include_once APP_INC_PATH . "class.auth.php";
include_once APP_INC_PATH . "class.report.php";
include_once APP_INC_PATH . "class.custom_field.php";
include_once APP_INC_PATH . "db_access.php";
$tpl = new Template_API();
$tpl->setTemplate("reports/custom_fields.tpl.html");
Auth::checkAuthentication(APP_COOKIE);
if (Auth::getCurrentRole() <= User::getRoleID("Customer")) {
echo "Invalid role";
exit;
}
$prj_id = Auth::getCurrentProject();
// get list of fields and convert info useful arrays
$fields = Custom_Field::getListByProject($prj_id, '', "combo");
$custom_fields = array();
$options = array();
if (is_array($fields) && count($fields) > 0) {
foreach ($fields as $field) {
$custom_fields[$field["fld_id"]] = $field["fld_title"];
$options[$field["fld_id"]] = Custom_Field::getOptions($field["fld_id"]);
}
} else {
echo "No custom fields for this project";
exit;
}
if (count(@$_GET['custom_field']) > 0) {
$data = Report::getCustomFieldReport(@$HTTP_GET_VARS["custom_field"], @$HTTP_GET_VARS["custom_options"], @$HTTP_GET_VARS["group_by"], true);
}
$tpl->assign(array("custom_fields" => $custom_fields, "custom_field" => @$HTTP_GET_VARS["custom_field"], "options" => $options, "custom_options" => @$HTTP_GET_VARS["custom_options"], "group_by" => @$HTTP_GET_VARS["group_by"], "selected_options" => @$_REQUEST['custom_options'], "data" => @$data));
$tpl->displayTemplate();
if (count($groups) > 0 && Auth::getCurrentRole() > User::getRoleID("Customer")) {
foreach ($groups as $grp_id => $grp_name) {
$assign_options["grp:{$grp_id}"] = "Group: " . $grp_name;
}
}
$assign_options += $users;
// get display values for custom fields
$custom_fields_display = array();
if (is_array($options['custom_field']) && count($options['custom_field']) > 0) {
foreach ($options['custom_field'] as $fld_id => $search_value) {
if (empty($search_value)) {
continue;
}
$field = Custom_Field::getDetails($fld_id);
if ($field['fld_type'] == 'combo' || $field['fld_type'] == 'multiple') {
$custom_fields_display[$fld_id] = join(', ', Custom_Field::getOptions($fld_id, $search_value));
}
}
}
$list = Issue::getListing($prj_id, $options, $pagerRow, $rows);
$tpl->assign("list", $list["list"]);
$tpl->assign("list_info", $list["info"]);
$tpl->assign("csv_data", base64_encode(@$list["csv"]));
$tpl->assign("columns", Display_Column::getColumnsToDisplay($prj_id, 'list_issues'));
$tpl->assign("priorities", Priority::getAssocList($prj_id));
$tpl->assign("status", Status::getAssocStatusList($prj_id));
$tpl->assign("open_status", Status::getAssocStatusList($prj_id, true));
$tpl->assign("users", $users);
$tpl->assign("assign_options", $assign_options);
$tpl->assign("custom", Filter::getAssocList($prj_id));
$tpl->assign("csts", Filter::getListing(true));
