Eventum to TaskJuggler

The following code when added to Eventum 1.7.1 will provide a new 'report' in the reporting interface that outputs open issues according to a simple keyword search. The output is formatted in the syntax of TaskJuggler so that you can then play with planning, prioritizing, scheduling these issues using TaskJuggler.

WARNING: Beta quality.

The code is a working prototype, but there are surely improvements to be made. There is no sanitization of user input. The idea with this release is to get more eyeballs early to enlist more ideas and release improvements often. Of course, one next step is to see what if anything needs to change to make this work in version 2.0 of Eventum.

This code still needs to be authorized for release to MySQL according to their contribution guidelines.

Additions to existing Files

include/class.date.php

     /**
      * Method used to convert the user date (that might be in a
      * specific timezone) to an ISO compliant format understood by TaskJuggler.
      *
      * @access  public
      * @param   string $date The user based date
      * @return  string The date in the GMT timezone
      */
     function getDateTJ($date)
     {
         $dt = new Date($date);
         $dt->setTZbyID(Date_API::getPreferredTimezone());
         $dt->toUTC();
         return $dt->format('%Y-%m-%d-%H:%M:%S'); // must have the dash between days and hours
     }

include/class.history.php

     /**
      * Returns the date when 'work' began. Defined as the event when the issue was first "assigned" to someone.
      *
      * In Eventum, this is actually referred to normally as 'user_associated', however
      * there are also other history types like auto assignments, remote assignments, and round-robin assignments that
      * are event types we care about.
      *
      * Since we can assign multiple persons simultaneously (resulting in multiple records), we only need the first event to get the date.
      *
      * In all the history types, there are many that we could confuse with a user being assigned to an issue if we only
      * relied on a string match for %associated% or %assigned%
      * email_associated, email_disassociated, issue_all_unassociated, issue_associated, issue_auto_assigned, issue_unassociated,
      * remote_assigned, rr_issue_assigned, scm_checkin_associated, user_all_unassociated, user_associated, user_unassociated
      * In other words, this will NOT work ((htt_name LIKE '%associated%') OR (htt_name LIKE '%assigned%'))
      * @see class.issue.php for details about assignment events.
      * Issue bulk update can create an assignment event, but the history log will only show 'bulk update' so this is not
      * fine-grained enough for this to really work.  Should fix this in eventum's logging or otherwise build a more reliable way to
      * record when an issue 'starts'.
      *
      * @param   integer $issue_id The ID of the issue
      * @return  date $start The start date used in the TaskJuggler report
      */
     function getInitialAssignmentDate($issue_id)
     {
         $sql = "SELECT
                     his_created_date
                 FROM
                     " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_history
                 LEFT JOIN
                     " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "history_type
                 ON issue_history.his_htt_id = history_type.htt_id
                 WHERE
                     his_iss_id = " . Misc::escapeInteger($issue_id) . " AND "
                     . "htt_name IN ('user_associated', 'issue_auto_assigned', 'remote_assigned',  'rr_issue_assigned', 'issue_bulk_updated')
                 ORDER BY
                     his_created_date ASC
                 LIMIT 1";
         // print $sql;
         $res = $GLOBALS["db_api"]->dbh->getOne($sql);
         if (PEAR::isError($res)) {
             Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
             return 0;
         }
         return $res;
     }

include/class.report.php

     /**
      * Method used to get open issues using a keyword search (and later report them in TaskJuggler syntax.)
      *
      * @access  public
      * @param   integer $prj_id The project ID
      * @param   string $keyword The search string to filter issues
      * @return  array The list of issues
      */
     function getOpenIssuesByKeyword($prj_id, $keyword)
     {
         $prj_id = Misc::escapeInteger($prj_id);
         $keyword = Misc::escapeString($keyword);
         $ts = Date_API::getCurrentUnixTimestampGMT();

         $stmt = "
             SELECT
                 iss_id,
                 iss_prj_id,
                 iss_prc_id,
                 iss_pre_id,
                 iss_created_date,
                 iss_updated_date,
                 iss_last_response_date,
                 iss_first_response_date,
                 iss_expected_resolution_date,
                 iss_summary,
                 iss_description,
                 iss_dev_time,
                 iss_percent_complete,
                 iss_last_internal_action_date ,
                 iss_last_internal_action_type,
                 project_priority.pri_title,
                 GROUP_CONCAT( user.usr_full_name SEPARATOR ', ' ) AS assigned,
                 status.sta_title,
                 sta_color
             FROM
             " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue
             LEFT JOIN
             " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "status
             ON issue.iss_sta_id = status.sta_id
             LEFT JOIN
             " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_priority
             ON issue.iss_pri_id = project_priority.pri_id
             LEFT JOIN
             " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_user
             ON issue.iss_id = issue_user.isu_iss_id
             LEFT JOIN
             " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "user
             ON issue_user.isu_usr_id = user.usr_id
             WHERE
                 status.sta_is_closed =0
             AND
                 (iss_summary LIKE '%" . Misc::escapeString($keyword) . "%'
                     OR
                 iss_description LIKE '%" . Misc::escapeString($keyword) . "%')
             GROUP BY iss_id
             ";

         $res = $GLOBALS["db_api"]->dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
         if (PEAR::isError($res)) {
             Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
             return "";
         } else {
             // this call will populate 'time_spent'
             Time_Tracking::getTimeSpentByIssues($res);
             // now loop through our result set and massage the data
             /**
              * We could use the Eventum notion of 'first response date' but that is from the POV that somebody from
              * ACME responded to the customer about the issue.  First response does not mean that somebody actually
              * accepted the ticket or it was assigned.  From a project management perspective, we are more interested
              * in trying to figure out when work started on the issue, rather than when we communicated to the 'customer'
              * To really come up with a TaskJuggler 'start' attribute, we should grep the issue log for assignment,
              * or the first assignement.  This turns out to be difficult due to bulk edit function which does not log assignment.
              */
             for ($i = 0; $i < count($res); $i++) {
                 $res[$i]['priority'] = round(((1/intval($res[$i]['pri_title']))*1000), 0) . " # {$res[$i]['pri_title']}" ;
                 // get a reasonable 'start' time based on when the issue was first assigned
                 $res[$i]['start'] = Date_API::getDateTJ(History::getInitialAssignmentDate($res[$i]['iss_id']));
                 // reformat the assigned string into a TaskJuggler resource id format
                 // in other words, we can't have spaces in the names
                 $res[$i]['assigned'] = preg_replace('/[^a-zA-Z,]/', '', $res[$i]['assigned']);
                 $res[$i]['assigned'] = str_replace(',', ', ', $res[$i]['assigned']);
                 // change time spent from minutes to hours, rounded to two decimal places
                 $res[$i]['time_spent'] = round ($res[$i]['time_spent']/60, 2);
                 // before formatting of dates, do some date math (although it should work with any of our date formats)
                 $res[$i]['last_update'] = Date_API::getFormattedDateDiff($ts, Date_API::getUnixTimestamp($res[$i]['iss_updated_date'], Date_API::getDefaultTimezone()));
                 // change '2006-06-13 12:20:04' to 'Tue, 13 Jun 2006, 08:20:04 EDT'
                 $res[$i]['iss_created_date'] = Date_API::getFormattedDate($res[$i]['iss_created_date']);
                 $res[$i]['iss_updated_date'] = (empty($res[$i]['iss_updated_date']))? $res[$i]['iss_created_date']
                     : Date_API::getFormattedDate($res[$i]['iss_updated_date']);
                 $res[$i]['iss_last_response_date'] = (empty($res[$i]['iss_last_response_date']))? $res[$i]['iss_created_date']
                     : Date_API::getFormattedDate($res[$i]['iss_last_response_date']);
             }
             return $res;
         }
     }


     /**
      * Method used to create a series of TaskJuggler resource supplement
      * statements (per user) given a list of issues.
      *
      * Need to create an output of the 'supplement' statements that look like this

 supplement resource r1 {
   # This is the work that has been done up until now by r1.
   booking t1 2003-06-06 +8h { sloppy 2 }
   booking t1 2003-06-08 +4h,
              2003-06-09 +4h { sloppy 2 }
   # Book interval that extends into off-hours.
   booking t1 2003-06-11-8:00 +10h { overtime 1 }
 }
      * The way that we will do this is to
      *  foreach user,
      *      foreach issue,
      *          SUM the time tracking detail for that issue + user
      *
      * @access  public
      * @param   array $result The result set which is passed by reference just to conserve memory
      * however this function does not produce side-effects on that result set
      * @return  string $ret a built up suplement statement
      *
      */
     function makeSupplementStatements(&$result)
     {
         $ret = '';
         $ids = array();
         for ($i = 0; $i < count($result); $i++) {
             $ids[] = $result[$i]["iss_id"];
         }
         if (count($ids) == 0) {
             return false; // got bad data in, can't do anything with it
         }
         $ids = implode(", ", Misc::escapeInteger($ids));

         /**
          * Find the users who are assigned to any of the issues in our collection of issues
          */
         $stmt = "SELECT DISTINCT
             usr_full_name,
             usr_id
             FROM issue_user, user
             WHERE isu_iss_id
             IN ( $ids )
             AND isu_usr_id = usr_id
         ";
         $res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
         if (PEAR::isError($res)) {
             Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
         } else {
             if (count($res)<1) {
                 return false; // there may not be any assignments for the set of issues, so there are no supplement statements to make
             }
             foreach ($res as $full_name => $user_id) {
                 $userHasBookings = false;
                 // start this resource (user) supplement
                 $supplementStatement = "<pre>\nsupplement resource " . preg_replace('/[^a-zA-Z,]/', '', $full_name) . " {\n";
                 // add a booking for each task
                 for ($i = 0; $i < count($result); $i++) {
                     $bookings = Report::getBookingByIssueAndUser($result[$i]['iss_id'], $user_id);
                     if (count($bookings) > 0) {
                         $userHasBookings = true;
                         $supplementStatement .= Report::formatBookingStatement($bookings);
                     }
                 }
                 $supplementStatement .= "}\n</pre>"; // close off the supplement statement
                 if ($userHasBookings) {
                     $ret .= $supplementStatement;
                 }
             }
             return $ret;
         }
     }


     /**
      * Method used to get the total time spent on a specific issue by a specific user.
      *
      * @access  public
      * @param   integer $issue_id The issue ID
      * @param   integer $user_id The user ID
      * @return  array The time tracking records for a particular combination of user and issue id
      */
     function getBookingByIssueAndUser($issue_id, $user_id)
     {
         $stmt = "SELECT
                     ttr_iss_id,
                     ttr_created_date,
                     ttr_time_spent,
                     ttr_summary
                  FROM
                     " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking
                  WHERE
                     ttr_iss_id=" . Misc::escapeInteger($issue_id)
                  ."
                  AND ttr_usr_id=" . Misc::escapeInteger($user_id);
         $res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
         // print "$stmt";
         if (PEAR::isError($res)) {
             Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
             return false;
         } else {
             return $res;
         }
     }


     /**
      * Method to return a 'booking' property in TaskJuggler syntax given
      * an array of records from the time_tracking table
      * For a particular issue (task), return the iss_id, date and time spent
      * @access  public
      * @param   array $records from the time_tracking_table
      * @return  string e.g. booking t1 2003-06-08 +4h
      * @todo create abstraction so that issue identifier is internal, but a
      * consistent prepend string is used for TaskJuggler export since TJ tasks can't be
      * only numeric.  Need this for both the main TJ report, and the booking statements
      *
      * Made all bookings as sloppy as they can be so that the program does not kick back errors
      * when somebody books a weekend, vacation or even another booking.
      * @todo reveal booking sloppiness as a UI option.
      */
     function formatBookingStatement($records)
     {
         if(!is_array($records)) return false;
         $ret = '';
         /*
         for ($i=0; $i<count($records); $i++) {
             //$ret .= "\tbooking {$records[$i]['ttr_iss_id']} {$records[$i]['ttr_created_date']} {$records[$i]['ttr_time_spent']}h\n";
             $ret .= "\tbooking {$records[$i]} {$records[$i][0]} {$records[$i][1]}h # {$records[$i][2]} \n";
         }
         */
         foreach ($records as $k => $v) {
             $formattedId = "iss$k";
             $timeAsHours = '+' . round($v[1]/60, 2) . 'h';
             $formattedDate = Date_API::getDateTJ($v[0]);
             $ret .= "\tbooking $formattedId $formattedDate $timeAsHours { sloppy 2 } # {$v[2]}\n";
         }
         return $ret;
     }

templates/en/reports/tree.tpl.html

tree.add(12, 0, 'Project Management Export', 'taskJuggler.php', '', 'basefrm');

New Files

reports/taskJuggler.php

<?php
/**
 * Copyright (c) 2007 OASIS
 * Authors: Greg Rundlett <greg.rundlett@oasis-open.org>                |
 */
include_once("../config.inc.php");
include_once(APP_INC_PATH . "class.template.php");
include_once(APP_INC_PATH . "class.auth.php");
include_once(APP_INC_PATH . "class.time_tracking.php");
include_once(APP_INC_PATH . "class.report.php");
include_once(APP_INC_PATH . "db_access.php");


// not sure which of the following I also need to include.. certainly date
include_once(APP_INC_PATH . "class.misc.php");
include_once(APP_INC_PATH . "class.user.php");
include_once(APP_INC_PATH . "class.date.php");
include_once(APP_INC_PATH . "class.status.php");
include_once(APP_INC_PATH . "class.priority.php");


$tpl = new Template_API();
$tpl->setTemplate("reports/taskJuggler.tpl.html");

Auth::checkAuthentication(APP_COOKIE);
/**
 * @todo set auth check to Manager role
 */
if (Auth::getCurrentRole() <= User::getRoleID("Customer")) {
    echo "Invalid role";
    exit;
}

$prj_id = Auth::getCurrentProject();

/**
 * What do we want to filter on?
 * Pehaps include some mechanism to look only at 'category' or 'priority>x'
 *
 */
if (empty($HTTP_GET_VARS['keyword'])) {
    $keyword = 'drupal';
} else {
    $keyword = $HTTP_GET_VARS['keyword'];
}
$tpl->assign("keyword", $keyword);



/**
 * What data do we want to pull from the database
 * The data is massaged and manipulated in the record set itself
 * so we can just throw it over the wall to the template system
 */
$res = Report::getOpenIssuesByKeyword($prj_id, $keyword);
$tpl->assign("issues", $res);

// add in all suplement statements
$supplement = Report::makeSupplementStatements($res);
$tpl->assign("supplement", $supplement);


/**
 * What other information do we want to show in our report?
 */

$tpl->assign('reportDate', Date_API::getFormattedDate($_SERVER['REQUEST_TIME']));
// we coulde show a total item count with <div id="total">{$smarty.foreach.foo.total} items</div>
// but that is only available inside or after SMARTY goes through the loop
$tpl->assign('issueCount', count($res));

$tpl->displayTemplate();
?>

templates/en/reports/taskJuggler.tpl.html

{include file="header.tpl.html"}
<h1>Project Management Export</h1>
<div style="font : italic medium serif; padding : 0 0 0 5px; text-transform : none;">
    {$reportDate}<br />
    {$issueCount} items
</div>
<div style="padding-left:5px;">
    <br />
    <form method="get" action="{$smarty.server.PHP_SELF}">
        <fieldset style="border:dotted blue 1px; width:auto; margin:auto 25% auto 25%;"><legend>Filter</legend>
        Enter a keyword to filter issues:<br />
        <input class="default" type="text" size="25" name="keyword" value="{$keyword}">
        <input type="submit" value="Submit" class="shortcut">
        </fieldset>
    </form>
    <p style="font : italic medium serif; padding : 0 0 0 5px; text-transform : none;">
        Note that the filter is only rudimentary at this stage of development.</p>
    <p>
        The scheduler in TaskJuggler looks for dependencies or start dates plus the amount of effort to figure out when things are
        going to happen.  Since we do not always have estimates of how much work is involved, nor do the historical start dates of when an issue was created correspond to relevant scheduling dates, we're forced to use 'guesstimates' for effort.
    </p>
    <p>
        TaskJuggler schedules things ASAP (or ALAP, but that's not recommended practice - <a href="http://www.taskjuggler.org/manual/property_scheduling.html">see the manual: scheduling</a>) based on dependencies, start dates, work effort and resource availability.  Since there is no way to nest tasks in Eventum, we must make all tasks depend on a pre-defined milestone.  So, that means starting off your project file with something like the following.
        {literal}
        <pre>
task start "kickoff" {
    milestone
    start 2006-06-07
}
        </pre>
        {/literal}
        Then, once you have a visual and all the numbers sliced five different ways, you can adjust from there in determining priorities, dependencies and do "what-if" scenarios to determine a good project plan.
    </p>
    <hr />
    Showing all open issues for <strong>{$keyword}</strong> ({$issueCount} items).
    <ol>
        <li><a href="#tasks">Tasks</a></li>
        <li><a href="#supplement">Supplement</a></li>
    </ol>
    <h2 name="tasks" id="tasks">Tasks</h2>
    <p>This section can be saved or added to a TaskJuggler task file</p>
    {foreach key=key item=issue from=$issues name=foo}
    <div style="border: solid 1px gray; padding: 3px; margin-bottom:1em; background-color:{$issue.sta_color}">
    task <a href="/view.php?id={$issue.iss_id}">iss{$issue.iss_id}</a> "{$issue.iss_summary|replace:'"':"'"|escape:"html"}" {literal}{{/literal}
        <div style="margin-left:30px;">
        {* if '' != $issue.start}start {$issue.start}<br />{/if *}
        depends !start<br />
        priority {$issue.priority}<br />
        {if '' != $issue.iss_expected_resolution_date}maxend {$issue.iss_expected_resolution_date}<br />{/if}
        {if '' != $issue.assigned}allocate {$issue.assigned} <br />{/if}
        {if 0 != $issue.iss_dev_time}effort {$issue.iss_dev_time}h<br />
        {else}effort 3h # using guesstimate<br />{/if}
        {if '' != $issue.time_spent}# {$issue.time_spent}h recorded in bookings<br />{/if}
        {if 0 != $issue.iss_percent_complete}complete {$issue.iss_percent_complete}<br />{/if}
{*
This part is temporarily disabled
because it can not be used in a taskjuggler file without
escaping the quote (") characters in the content;
alternately, it could be made collapsible so that it shows on screen,
but is collapsed for use in TJ
More realistically, we could build a text view for export and a screen view for the browser
        note <div style="background : #ECE4F0; padding : 3px; overflow : auto; width : 600px; whitespace:pre;">"
        created {$issue.iss_created_date}<br />
        status {$issue.sta_title}<br />
        last response {$issue.iss_last_response_date}<br />
        updated {$issue.iss_updated_date}<br />
        recent activity {$issue.last_update}<br />
        description {$issue.iss_description|truncate:300:"...":true|replace:'"':"'"}"</div>
*}
        </div>
    {literal}}{/literal}
    </div>
    {/foreach}
    {$issueCount} items

    <hr />

{* Smarty comment
Need to create an output of the 'supplement' statements that look like this

supplement resource r1 {
  # This is the work that has been done up until now by r1.
  booking t1 2003-06-06 +8h { sloppy 2 }
  booking t1 2003-06-08 +4h,
             2003-06-09 +4h { sloppy 2 }
  # Book interval that extends into off-hours.
  booking t1 2003-06-11-8:00 +10h { overtime 1 }
}
*}
    <h2 name="supplement" id="supplement">Supplement</h2>
    <p>This section can be saved or added to a TaskJuggler resource file</p>
    {$supplement}
</div>

{include file="footer.tpl.html"}

Application Programming Interface

short for Software Application.

PHP Extension and Application Repository = a collection of code started in 1999 to enhance the utility of PHP.

Organization for the Advancement of Structured Information Standards = a global standards organization

HyperText Transfer Protocol = the foundation of data communication for the World Wide Web. See HTML

PHP is the HyperText Preprocessor = the most popular programming language for the web