/** * Returns workload information for the specified date range and interval. * * @access public * @param string $interval The interval to use in this report. * @param string $type If this report is aggregate or individual * @param string $start The start date of this report. * @param string $end The end date of this report. * @return array An array containing workload data. */ function getWorkloadByDateRange($interval, $type, $start, $end) { $data = array(); $start = Misc::escapeString($start); $end = Misc::escapeString($end); // figure out the correct format code switch ($interval) { case "day": $format = '%m/%d/%y'; $order_by = "%1\$s"; break; case "dow": $format = '%W'; $order_by = "IF(DATE_FORMAT(%1\$s, '%%w') = 0, 7, DATE_FORMAT(%1\$s, '%%w'))"; break; case "week": if ($type == "aggregate") { $format = '%v'; } else { $format = '%v/%y'; } $order_by = "%1\$s"; break; case "dom": $format = '%d'; break; case "month": if ($type == "aggregate") { $format = '%b'; $order_by = "DATE_FORMAT(%1\$s, '%%m')"; } else { $format = '%b/%y'; $order_by = "%1\$s"; } break; } // get issue counts $stmt = "SELECT\n DATE_FORMAT(iss_created_date, '{$format}'),\n count(*)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n iss_prj_id=" . Auth::getCurrentProject() . " AND\n iss_created_date BETWEEN '{$start}' AND '{$end}'\n GROUP BY\n DATE_FORMAT(iss_created_date, '{$format}')"; if (!empty($order_by)) { $stmt .= "\nORDER BY " . sprintf($order_by, 'iss_created_date'); } $res = $GLOBALS["db_api"]->dbh->getAssoc($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return array(); } $data["issues"]["points"] = $res; if (count($res) > 0) { $stats = new Math_Stats(); $stats->setData($res); $data["issues"]["stats"] = array("total" => $stats->sum(), "avg" => $stats->mean(), "median" => $stats->median(), "max" => $stats->max()); } else { $data["issues"]["stats"] = array("total" => 0, "avg" => 0, "median" => 0, "max" => 0); } // get email counts $stmt = "SELECT\n DATE_FORMAT(sup_date, '{$format}'),\n count(*)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_account\n WHERE\n sup_ema_id=ema_id AND\n ema_prj_id=" . Auth::getCurrentProject() . " AND\n sup_date BETWEEN '{$start}' AND '{$end}'\n GROUP BY\n DATE_FORMAT(sup_date, '{$format}')"; if (!empty($order_by)) { $stmt .= "\nORDER BY " . sprintf($order_by, 'sup_date'); } $res = $GLOBALS["db_api"]->dbh->getAssoc($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return array(); } $data["emails"]["points"] = $res; if (count($res) > 0) { $stats = new Math_Stats(); $stats->setData($res); $data["emails"]["stats"] = array("total" => $stats->sum(), "avg" => $stats->mean(), "median" => $stats->median(), "max" => $stats->max()); } else { $data["emails"]["stats"] = array("total" => 0, "avg" => 0, "median" => 0, "max" => 0); } return $data; }
/** * Returns workload information for the specified date range and interval. * * @param string $interval The interval to use in this report. * @param string $type If this report is aggregate or individual * @param string $start The start date of this report. * @param string $end The end date of this report. * @param integer $category_id The category to restrict this report to * @return array An array containing workload data. */ public static function getWorkloadByDateRange($interval, $type, $start, $end, $category_id) { $data = array(); $category_id = (int) $category_id; // figure out the correct format code switch ($interval) { case 'day': $format = '%m/%d/%y'; $order_by = "%1\$s"; break; case 'dow': $format = '%W'; $order_by = "CASE WHEN DATE_FORMAT(%1\$s, '%%w') = 0 THEN 7 ELSE DATE_FORMAT(%1\$s, '%%w') END"; break; case 'week': if ($type == 'aggregate') { $format = '%v'; } else { $format = '%v/%y'; } $order_by = "%1\$s"; break; case 'dom': $format = '%d'; break; case 'month': if ($type == 'aggregate') { $format = '%b'; $order_by = "DATE_FORMAT(%1\$s, '%%m')"; } else { $format = '%b/%y'; $order_by = "%1\$s"; } break; default: throw new LogicException('Invalid interval'); } // get issue counts $stmt = 'SELECT DATE_FORMAT(iss_created_date, ?), count(*) FROM {{%issue}} WHERE iss_prj_id=? AND iss_created_date BETWEEN ? AND ?'; $params = array($format, Auth::getCurrentProject(), $start, $end); if (!empty($category_id)) { $stmt .= ' AND iss_prc_id = ?'; $params[] = $category_id; } $stmt .= ' GROUP BY DATE_FORMAT(iss_created_date, ?)'; $params[] = $format; if (!empty($order_by)) { $stmt .= "\nORDER BY " . sprintf($order_by, 'iss_created_date'); } try { $res = DB_Helper::getInstance()->fetchAssoc($stmt, $params); } catch (DbException $e) { return array(); } $data['issues']['points'] = $res; $data['issues']['stats'] = array('total' => 0, 'avg' => 0, 'median' => 0, 'max' => 0); if ($res) { $stats = new Math_Stats(); $stats->setData($res); $data['issues']['stats'] = array('total' => $stats->sum(), 'avg' => $stats->mean(), 'median' => $stats->median(), 'max' => $stats->max()); } // get email counts $params = array(); $stmt = 'SELECT DATE_FORMAT(sup_date, ?), count(*) FROM {{%support_email}}, {{%email_account}}'; $params[] = $format; if (!empty($category_id)) { $stmt .= ', {{%issue}}'; } $stmt .= ' WHERE sup_ema_id=ema_id AND ema_prj_id=? AND sup_date BETWEEN ? AND ?'; $params[] = Auth::getCurrentProject(); $params[] = $start; $params[] = $end; if (!empty($category_id)) { $stmt .= ' AND sup_iss_id = iss_id AND iss_prc_id = ?'; $params[] = $category_id; } $stmt .= ' GROUP BY DATE_FORMAT(sup_date, ?)'; $params[] = $format; if (!empty($order_by)) { $stmt .= "\nORDER BY " . sprintf($order_by, 'sup_date'); } try { $res = DB_Helper::getInstance()->fetchAssoc($stmt, $params); } catch (DbException $e) { return array(); } $data['emails']['points'] = $res; if (count($res) > 0) { $stats = new Math_Stats(); $stats->setData($res); $data['emails']['stats'] = array('total' => $stats->sum(), 'avg' => $stats->mean(), 'median' => $stats->median(), 'max' => $stats->max()); } else { $data['emails']['stats'] = array('total' => 0, 'avg' => 0, 'median' => 0, 'max' => 0); } return $data; }
/** * Returns time tracking information for a certain category, or all categories if no category is passed. * * @access public * @param $ttc_id The id of the time tracking category. Default false * @return array Array of time tracking information */ function getIndividualTimeTracking($ttc_id = false) { $stmt = "SELECT\n ttr_time_spent\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n ttr_iss_id = iss_id"; if ($ttc_id != false) { $stmt .= "\n AND ttr_ttc_id = {$ttc_id}"; } $stmt .= "\nAND " . $this->getWhereClause("iss_customer_id", "ttr_created_date"); $res = $GLOBALS["db_api"]->dbh->getCol($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return array(); } if (count($res) > 0) { $stats = new Math_Stats(); $stats->setData($res); $total = $stats->sum(); $avg = $stats->mean(); $median = $stats->median(); return array("total" => $total, "total_formatted" => Misc::getFormattedTime($total, true), "avg" => $avg, "avg_formatted" => Misc::getFormattedTime($avg), "median" => $median, "median_formatted" => Misc::getFormattedTime($median)); } else { return array(); } }