Adding projectname column to tasklog report

[markymark]

We frequently use the tasklogreport ( /modules/projects/reports)
But we would like to see a column which also shows the projectname how can i add this to the file taskslogs.php?

[pedroa]

1) Add the field in the sql
2) Follow the display structure and add the field

Pedro A.

[martijnb]

Hi,

I was doing the same (adding project names) in the gui.
So I just wanted to have a look at this.
Haven't tested it well, but the report and the pdf seem ok.

Let me know if it's ok,

Martijn

<?php /* PROJECTS $Id: tasklogs.php,v 1.12 2004/11/12 14:43:04 cyberhorse Exp $ */
/**
* Generates a report of the task logs for given dates
*/
error_reporting( E_ALL );
$perms =& $AppUI->acl();
if (! $perms->checkModule('tasks', 'view'))
redirect('m=public&a=access_denied');
$do_report = dPgetParam( $_GET, "do_report", 0 );
$log_all = dPgetParam( $_GET, 'log_all', 0 );
$log_pdf = dPgetParam( $_GET, 'log_pdf', 0 );
$log_ignore = dPgetParam( $_GET, 'log_ignore', 0 );
$log_userfilter = dPgetParam( $_GET, 'log_userfilter', '0' );
$log_allprojects = dPgetParam( $_GET, 'log_allprojects', '0' );

$log_start_date = dPgetParam( $_GET, "log_start_date", 0 );
$log_end_date = dPgetParam( $_GET, "log_end_date", 0 );

// create Date objects from the datetime fields
$start_date = intval( $log_start_date ) ? new CDate( $log_start_date ) : new CDate();
$end_date = intval( $log_end_date ) ? new CDate( $log_end_date ) : new CDate();

if (!$log_start_date) {
$start_date->subtractSpan( new Date_Span( "14,0,0,0" ) );
}
$end_date->setTime( 23, 59, 59 );

?>
<script language="javascript">
var calendarField = '';

function popCalendar( field ){
calendarField = field;
idate = eval( 'document.editFrm.log_' + field + '.value' );
window.open( 'index.php?m=public&a=calendar&dialog=1&callback=setCalendar&date=' + idate, 'calwin', 'width=250, height=220, scollbars=false' );
}

/**
* @param string Input date in the format YYYYMMDD
* @param string Formatted date
*/
function setCalendar( idate, fdate ) {
fld_date = eval( 'document.editFrm.log_' + calendarField );
fld_fdate = eval( 'document.editFrm.' + calendarField );
fld_date.value = idate;
fld_fdate.value = fdate;
}
</script>

<table cellspacing="0" cellpadding="4" border="0" width="100%" class="std">

<form name="editFrm" action="" method="GET">
<input type="hidden" name="m" value="projects" />
<input type="hidden" name="a" value="reports" />
<input type="hidden" name="project_id" value="<?php echo $project_id;?>" />
<input type="hidden" name="report_type" value="<?php echo $report_type;?>" />

<tr>
<td align="right" nowrap="nowrap"><?php echo $AppUI->_('For period');?>:</td>
<td nowrap="nowrap">
<input type="hidden" name="log_start_date" value="<?php echo $start_date->format( FMT_TIMESTAMP_DATE );?>" />
<input type="text" name="start_date" value="<?php echo $start_date->format( $df );?>" class="text" disabled="disabled" style="width: 80px" />
<a href="#" onClick="popCalendar('start_date')">
<img src="./images/calendar.gif" width="24" height="12" alt="<?php echo $AppUI->_('Calendar');?>" border="0" />
</a>
</td>
<td align="right" nowrap="nowrap"><?php echo $AppUI->_('to');?></td>
<td nowrap="nowrap">
<input type="hidden" name="log_end_date" value="<?php echo $end_date ? $end_date->format( FMT_TIMESTAMP_DATE ) : '';?>" />
<input type="text" name="end_date" value="<?php echo $end_date ? $end_date->format( $df ) : '';?>" class="text" disabled="disabled" style="width: 80px"/>
<a href="#" onClick="popCalendar('end_date')">
<img src="./images/calendar.gif" width="24" height="12" alt="<?php echo $AppUI->_('Calendar');?>" border="0" />
</a>
</td>

<TD NOWRAP>
<?php echo $AppUI->_('User');?>:
<SELECT NAME="log_userfilter" CLASS="text" STYLE="width: 80px">

<?php
$usersql = "
SELECT user_id, user_username, contact_first_name, contact_last_name
FROM users
LEFT JOIN contacts ON user_contact = contact_id
";

if ( $log_userfilter == 0 ) echo '<OPTION VALUE="0" SELECTED>'.$AppUI->_('All users' );
else echo '<OPTION VALUE="0">All users';

if (($rows = db_loadList( $usersql, NULL )))
{
foreach ($rows as $row)
{
if ( $log_userfilter == $row["user_id"])
echo "<OPTION VALUE='".$row["user_id"]."' SELECTED>".$row["user_username"];
else
echo "<OPTION VALUE='".$row["user_id"]."'>".$row["user_username"];
}
}

?>

</SELECT>
</TD>

<td nowrap="nowrap">
<input type="checkbox" name="log_allprojects" <?php if ($log_allprojects) echo "checked" ?> />
<?php echo $AppUI->_( 'All Projects' );?>
</td>

<td nowrap="nowrap">
<input type="checkbox" name="log_all" <?php if ($log_all) echo "checked" ?> />
<?php echo $AppUI->_( 'Log All' );?>
</td>

<td nowrap="nowrap">
<input type="checkbox" name="log_pdf" <?php if ($log_pdf) echo "checked" ?> />
<?php echo $AppUI->_( 'Make PDF' );?>
</td>

<td nowrap="nowrap">
<input type="checkbox" name="log_ignore" />
<?php echo $AppUI->_( 'Ignore 0 hours' );?>
</td>

<td align="right" width="50%" nowrap="nowrap">
<input class="button" type="submit" name="do_report" value="<?php echo $AppUI->_('submit');?>" />
</td>
</tr>
</form>
</table>

<?php
if ($do_report) {

$sql = "SELECT t.*, CONCAT_WS(' ',contact_first_name,contact_last_name) AS creator, projects.project_name "
."\nFROM task_log AS t, tasks"
."\nLEFT JOIN users AS u ON user_id = task_log_creator"
."\nLEFT JOIN contacts ON user_contact = contact_id"
."\nLEFT JOIN projects ON project_id = task_project"
."\nWHERE task_log_task = task_id";
if (!$log_allprojects)
{
$sql .= "\nAND task_project = $project_id";
}
if (!$log_all) {
$sql .= "\n AND task_log_date >= '".$start_date->format( FMT_DATETIME_MYSQL )."'"
."\n AND task_log_date <= '".$end_date->format( FMT_DATETIME_MYSQL )."'";
}
if ($log_ignore) {
$sql .= "\n AND task_log_hours > 0";
}
if ($log_userfilter) {
$sql .= "\n AND task_log_creator = $log_userfilter";
}

$proj =& new CProject;
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'task_project');
if (count($allowedProjects)) {
$sql .= "\n AND " . implode(" AND ", $allowedProjects);
}

$sql .= " ORDER BY task_log_date";

//echo "<pre>$sql</pre>";

$logs = db_loadList( $sql );
echo db_error();
?>
<table cellspacing="1" cellpadding="5" border="0" class="tbl">
<tr>
<th nowrap="nowrap"><?php echo $AppUI->_('Created by');?></th>
<th><?php echo $AppUI->_('Project');?></th>
<th><?php echo $AppUI->_('Summary');?></th>
<th><?php echo $AppUI->_('Description');?></th>
<th><?php echo $AppUI->_('Date');?></th>
<th><?php echo $AppUI->_('Hours');?></th>
<th><?php echo $AppUI->_('Cost Code');?></th>
</tr>
<?php
$hours = 0.0;
$pdfdata = array();

foreach ($logs as $log) {
$date = new CDate( $log['task_log_date'] );
$hours += $log['task_log_hours'];

$pdfdata[] = array(
$log['creator'],
$log['project_name'],
$log['task_log_name'],
$log['task_log_description'],
$date->format( $df ),
sprintf( "%.2f", $log['task_log_hours'] ),
$log['task_log_costcode'],
);
?>
<tr>
<td><?php echo $log['creator'];?></td>
<td><?php echo $log['project_name'];?></td>
<td>
<a href="index.php?m=tasks&a=view&tab=1&task_id=<?php echo $log['task_log_task'];?>&task_log_id=<?php echo $log['task_log_id'];?>"><?php echo $log['task_log_name'];?></a>
</td>
<td><?php
// dylan_cuthbert: auto-transation system in-progress, leave these lines for time-being
$transbrk = "\n[translation]\n";
$descrip = str_replace( "\n", "<br />", $log['task_log_description'] );
$tranpos = strpos( $descrip, str_replace( "\n", "<br />", $transbrk ) );
if ( $tranpos === false) echo $descrip;
else
{
$descrip = substr( $descrip, 0, $tranpos );
$tranpos = strpos( $log['task_log_description'], $transbrk );
$transla = substr( $log['task_log_description'], $tranpos + strlen( $transbrk ) );
$transla = trim( str_replace( "'", '"', $transla ) );
echo $descrip."<div style='font-weight: bold; text-align: right'><a title='$transla' class='hilite'>[".$AppUI->_("translation")."]</a></div>";
}
// dylan_cuthbert; auto-translation end
?></td>
<td><?php echo $date->format( $df );?></td>
<td align="right"><?php printf( "%.2f", $log['task_log_hours'] );?></td>
<td><?php echo $log['task_log_costcode'];?></td>
</tr>

[martijnb]

<?php
}
$pdfdata[] = array(
'',
'',
'',
'',
$AppUI->_('Total Hours').':',
sprintf( "%.2f", $hours ),
'',
);
?>
<tr>
<td align="right" colspan="5"><?php echo $AppUI->_('Total Hours');?>:</td>
<td align="right"><?php printf( "%.2f", $hours );?></td>
</tr>
</table>
<?php
if ($log_pdf) {
// make the PDF file
if (!$log_allprojects){
$sql = "SELECT project_name FROM projects WHERE project_id=$project_id";
$pname = db_loadResult( $sql );
}
else
$pname = "All Projects";
echo db_error();

$font_dir = dPgetConfig( 'root_dir' )."/lib/ezpdf/fonts";
$temp_dir = dPgetConfig( 'root_dir' )."/files/temp";
$base_url = dPgetConfig( 'base_url' );
require( $AppUI->getLibraryClass( 'ezpdf/class.ezpdf' ) );

$pdf =& new Cezpdf();
$pdf->ezSetCmMargins( 1, 2, 1.5, 1.5 );
$pdf->selectFont( "$font_dir/Helvetica.afm" );

$pdf->ezText( dPgetConfig( 'company_name' ), 12 );
// $pdf->ezText( dPgetConfig( 'company_name' ).' :: '.dPgetConfig( 'page_title' ), 12 );

$date = new CDate();
$pdf->ezText( "\n" . $date->format( $df ) , 8 );

$pdf->selectFont( "$font_dir/Helvetica-Bold.afm" );
$pdf->ezText( "\n" . $AppUI->_('Task Log Report'), 12 );
$pdf->ezText( "$pname", 15 );
if ($log_all) {
$pdf->ezText( "All task log entries", 9 );
} else {
$pdf->ezText( "Task log entries from ".$start_date->format( $df ).' to '.$end_date->format( $df ), 9 );
}
$pdf->ezText( "\n\n" );

$title = 'Task Logs';

$pdfheaders = array(
$AppUI->_('Created by'),
$AppUI->_('Project'),
$AppUI->_('Summary'),
$AppUI->_('Description'),
$AppUI->_('Date'),
$AppUI->_('Hours'),
$AppUI->_('Cost Code')
);

$options = array(
'showLines' => 1,
'fontSize' => 8,
'rowGap' => 2,
'colGap' => 5,
'xPos' => 50,
'xOrientation' => 'right',
'width'=>'500'
);

$pdf->ezTable( $pdfdata, $pdfheaders, $title, $options );

if ($fp = fopen( "$temp_dir/temp$AppUI->user_id.pdf", 'wb' )) {
fwrite( $fp, $pdf->ezOutput() );
fclose( $fp );
echo "<a href=\"$base_url/files/temp/temp$AppUI->user_id.pdf\" target=\"pdf\">";
echo $AppUI->_( "View PDF File" );
echo "</a>";
} else {
echo "Could not open file to save PDF. ";
if (!is_writable( $temp_dir )) {
"The files/temp directory is not writable. Check your file system permissions.";
}
}
}
}
?>[/

[markymark]

1.
the report and pdf part works fine thank you very much. Could you maybe assist me how i can make the projectname linkable in the report part? like eg. the taskname? i don't know which variable i need to use in the link.

2.
How do i sort tasklogs.php first by:
projectname

and then by:
tasklogdate

is this possible by altering the mysql query of does it have to be done with php-code?

[martijnb]

Hi Marky,

2 chnages to the SQL, 1 html link. all in PHP code. ;-)

LINE 136:
$sql = "SELECT t.*, CONCAT_WS(' ',contact_first_name,contact_last_name) AS creator, projects.project_id, projects.project_name "

LINE 163
$sql .= " ORDER BY project_name, task_log_date";

LINE 200
<td><a href='index.php?m=projects&a=view&project_id=<?php echo $log['project_id'];?>'><?php echo $log['project_name'];?></a></td>

[dimagi]

Thanks, Martijn, for all your help in this.

One other thing I'd like to be able to see in this view is the task name, between the project name and the task log summary. If you could provide the code for this it would be much appreciated. :)

[martijnb]

Hi,

The 'Summary' column _IS_ the 'task_log_name'.
Description is 'task_log_description'.

Hope this solves the 'problem'.. :-)

[dimagi]

You're right, the summary column is the task log name; but what I would like to see is the name of the task, not of the log within the task.

As far as I understand it, the nesting goes: Project > Task > Task Log. I would like to be able to display all three levels of that hierarchy.

Thanks for your help!

[martijnb]

Hi,

Yes, you are right.
I added the task name to the summary column, because the columns are becoming rather small on the PDF.
The Project column also includes the company name.. that's because I use it that way.
Because I dont know how to attach a file and the file is too big(?) to fit in this window, download it here:
http://www.freshlive.com/tasklogs.zip
Let me know what you think of it.

[dimagi]

Thanks again, martijn; this has been a big help.

I have two more reports I would very much appreciate help with. The first is a slight modification of the one you just posted; I would like the company name removed, the task summary removed (so that only the task name appears in the "Summary" column), and the "Cost Code" column removed to make room for a wider "Description" column. If it would be possible to make this in landscape layout, that would be extremely helpful as well.

A second report I'm trying to build would show total hours spent on each project for a given time period, as well as total hours spent on each task within that project, and possibly the user's name as well. This would help give a broad overview of all projects.

If this is too tall an order, I understand; thanks so much again for your help so far.

[MacOfTheEast]

Yes, you are right. I added the task name to the summary column, because the columns are becoming rather small on the PDF.
The Project column also includes the company name.. that's because I use it that way.Great work on the report modification, martijnb. Many of us here in the dP community appreciate your efforts.

A suggestion for this one: If, instead of using the current name for the report, you use the file name "tasklogsextended.php" and create a "tasklogsextended.en.txt" file containing only these two lines...

Task Log (Extended)
View extended version of the user task logs

...then we can enjoy having both the old and your modified version available to us. Anyway, that's what I did here and now they both appear in the list of available reports :D

Regardless, thanks again for your work.

MOTE

[martijnb]

reply to dimagi:
I will have a look at these reports next week, when I have more time.
Have to find out how to make it create pdf in landscape..

reply to macoftheeast:
Thanks, didn't know that. It all started with minor changes and a bit of reverse-engineering. Never ment to create a new report.
I'll create separate files from now on ;-)

[dimagi]

Hi marijn-

Any chance of getting some help changing this report? I'm still not familiar enough with the pdf production code to make the changes I need.

Thanks!