[WTF CVS] wtf/lib/WTF/Pages Reports.pm,1.15,1.16

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Update of /cvsroot/wtf-tracker/wtf/lib/WTF/Pages
In directory sc8-pr-cvs9.sourceforge.net:/tmp/cvs-serv1183/lib/WTF/Pages

Modified Files:
	Reports.pm 
Log Message:
Feature add from bug 1656452: Activity Excel download

Index: Reports.pm
===================================================================
RCS file: /cvsroot/wtf-tracker/wtf/lib/WTF/Pages/Reports.pm,v
retrieving revision 1.15
retrieving revision 1.16
diff -C2 -d -r1.15 -r1.16
*** Reports.pm	9 Mar 2007 22:35:13 -0000	1.15
--- Reports.pm	21 Mar 2007 17:26:31 -0000	1.16
***************
*** 310,313 ****
--- 310,317 ----
  	my $req          = WTF::Utils::get_req($r);
  
+ 	# build and return an XLS if the user requested a download
+ 	return xls_activity_summary( $r, $req )
+ 		if ( defined $req->param('view') and $req->param('view') eq 'excel' );
+ 
  	# fetch data to populate the months drop-down select box
  	$sth->{'activity_months'}->execute( $req->param('month') || '' ) or die $dbh->errstr();
***************
*** 396,399 ****
--- 400,625 ----
  }
  
+ sub xls_activity_summary {
+ 	my ( $r, $req ) = @_;
+ 
+ 	# fetch activity (lowest-level) list
+ 	$sth->{'activity_xls_activities'}->execute() or die $dbh->errstr();
+ 	my @activities = @{ $sth->{'activity_xls_activities'}->fetchall_arrayref({}) };
+ 	$sth->{'activity_xls_activities'}->finish();
+ 
+ 	# fetch parent activity list
+ 	my ( $last_parent_id, @parent_activities );
+ 	for (@activities) {
+ 		if ( @parent_activities and $_->{'parent_id'} eq $last_parent_id ) {
+ 			# increase the colspan (width in columns of the parent activity cell
+ 			$parent_activities[-1]->{'colspan'}++;
+ 		}
+ 		else {
+ 			$last_parent_id = $_->{'parent_id'};
+ 	
+ 			$sth->{'parent_activity'}->execute( $_->{'parent_id'} ) or die $dbh->errstr();
+ 			push @parent_activities, $sth->{'parent_activity'}->fetchrow_hashref();
+ 			$parent_activities[-1]->{'colspan'} = 1;
+ 			$sth->{'parent_activity'}->finish();
+ 		}
+ 	}
+ 
+ 	# build employee data structure
+ 	my ( @employees, $index );
+ 	$sth->{'activity_xls_percentages'}->execute( $req->param('month') ) or die $dbh->errstr();
+ 	while ( my $row = $sth->{'activity_xls_percentages'}->fetchrow_hashref() ) {
+ 
+ 		if ( not @employees or $employees[-1]{'employee_id'} != $row->{'employee_id'} ) {
+ 			push @employees, $row;
+ 			$index = 0;
+ 		}
+ 
+ 		# include percentage data for the given employee
+ 		while ( $index < @activities ) {
+ 			push @{ $employees[-1]->{'percentages'} }, (
+ 				exists $activities[$index]->{'id'} and 
+ 				$row->{'activity_id'} and
+ 				$row->{'avg_percentage'} and
+ 				$row->{'activity_id'} == $activities[$index]->{'id'} 
+ 			) ? $row->{'avg_percentage'} : undef;
+ 
+ 			$index++;
+ 			last if ( defined $employees[-1]->{'percentages'}[-1] );
+ 		}
+ 
+ 	}
+ 	$sth->{'activity_xls_percentages'}->finish();
+ 
+ 	# level the average percentages and add vacation and sick data
+ 	for my $employee (@employees) {
+ 		push @{ $employee->{'percentages'} }, undef 
+ 			while ( scalar @{ $employee->{'percentages'} } < scalar @activities );
+ 
+ 		my $total_percentage = 0;
+ 		$total_percentage += $_ || 0 for ( @{ $employee->{'percentages'} } );
+ 
+ 		for ( @{ $employee->{'percentages'} } ) {
+ 			$_ = sprintf( "\%.5f", $_ / $total_percentage ) if ( defined $_ );
+ 		}
+ 
+ 		# include vacation and sick data
+ 		$sth->{'activity_xls_vacation_sick'}->execute(
+ 			$employee->{'employee_id'}, 
+ 			$req->param('month'),
+ 		) or die $dbh->errstr();
+ 		(
+ 			$employee->{'vacation'},
+ 			$employee->{'sick'},
+ 		) = $sth->{'activity_xls_vacation_sick'}->fetchrow_array();
+ 		$sth->{'activity_xls_vacation_sick'}->finish();
+ 	}
+ 
+ 	# build excel workbook
+ 	my $workbook  = WTF::Utils::make_excel_workbook( $r, 'activity_' . $req->param('month') . '.xls' );
+ 	my $formats   = WTF::Utils::make_excel_formats($workbook);
+ 	my $worksheet = $workbook->add_worksheet( $req->param('month') );
+ 
+ 	$worksheet->freeze_panes( 4, 0 );
+ 	$worksheet->write( 0, 0, 'Activity Summary for ' . $req->param('month'), $formats->{'title'} );
+ 	$worksheet->set_column( 0, 0, 26 );
+ 	$worksheet->set_column( 1, scalar @activities, 14 );
+ 
+ 	my @excel_bg_colors = ( 24, 29, 22, 26, 27, 31, 42, 50 .. 53, 2, 3, 5 .. 7 );
+ 	my $column = 1;
+ 	my %activity_formats;
+ 
+ 	my $get_header_format = sub {
+ 		return $workbook->add_format(
+ 			'bg_color'  => $_[0],
+ 			'bold'      => $_[1],
+ 			'top'       => 1,
+ 			'bottom'    => 1,
+ 			'left'      => 1,
+ 			'right'     => 1,
+ 			'text_wrap' => 1,
+ 			'align'     => 'center',
+ 		);
+ 	};
+ 	my %picked_colors;
+ 
+ 	# add parent activities
+ 	for (@parent_activities) {
+ 		$picked_colors{ $_->{'id'} } = shift @excel_bg_colors;
+ 
+ 		if ( $_->{'colspan'} > 1 ) {
+ 			$worksheet->merge_range( 
+ 				2, $column, 2, $column + $_->{'colspan'} - 1, $_->{'name'}, 
+ 				$get_header_format->( $picked_colors{ $_->{'id'} }, 1 ),
+ 			);
+ 		}
+ 		else {
+ 			$worksheet->write( 
+ 				2, $column, $_->{'name'}, 
+ 				$get_header_format->( $picked_colors{ $_->{'id'} }, 1 ),
+ 			);
+ 		}
+ 		$column += $_->{'colspan'};
+ 	}
+ 
+ 	# add supplemental data (vacation and sick days) headers
+ 	$picked_colors{'supplemental'} = shift @excel_bg_colors;
+ 	$worksheet->merge_range( 
+ 		2, $column, 2, $column + 1, 'Other Events', 
+ 		$get_header_format->( $picked_colors{'supplemental'}, 1 ),
+ 	);
+ 
+ 	# add activity headers
+ 	$column = 1;
+ 	for (@activities) {
+ 		$worksheet->write( 
+ 			3, $column, $_->{'name'}, 
+ 			$get_header_format->( $picked_colors{ $_->{'parent_id'} }, 0 )
+ 		);
+ 		$column++;
+ 	}
+ 
+ 	# add vacation and sick day headers
+ 	for ( 'Vacation Days', 'Sick Days' ) {
+ 		$worksheet->write( 
+ 			3, $column, $_, 
+ 			$get_header_format->( $picked_colors{'supplemental'}, 0 ),
+ 		);
+ 		$column++;
+ 	}
+ 
+ 	$worksheet->write( 3, 0, 'Employee Name', $workbook->add_format( 'bold' => 1 ) );
+ 
+ 	my $row = 4;
+ 	my $format_percentage = $workbook->add_format(
+ 		'left'  => 1,
+ 		'right' => 1,
+ 	);
+ 	$format_percentage->set_num_format('0.0%');
+ 	my $format_days = $workbook->add_format(
+ 		'left'  => 1,
+ 		'right' => 1,
+ 	);
+ 
+ 	# add percentage and vacation/sick day data for each employee
+ 	for (@employees) {
+ 		$worksheet->write( 
+ 			$row, 0, $_->{'employee_name'}, 
+ 			( not $_->{'active'} ) ? $workbook->add_format( 'italic' => 1 ) : undef,
+ 		);
+ 		$worksheet->write_row( $row, 1, $_->{'percentages'}, $format_percentage );
+ 
+ 		$worksheet->write_row( $row, scalar @activities + 1, [
+ 			$_->{'vacation'},
+ 			$_->{'sick'},
+ 		], $format_days );
+ 
+ 		$row++;
+ 	}
+ 
+ 	$worksheet->write( $row, 0, 'Weighted Average/Total', $workbook->add_format(
+ 		'top'    => 1,
+ 		'right'  => 1,
+ 		'bold'   => 1,
+ 		'align'  => 'right',
+ 	) );
+ 
+ 	my $format_total_percent = $workbook->add_format(
+ 		'top'    => 1,
+ 		'left'   => 1,
+ 		'right'  => 1,
+ 		'bottom' => 1,
+ 		'bold'   => 1,
+ 	);
+ 	$format_total_percent->set_num_format('0.0%');
+ 
+ 	# setup the summary/totals row at the bottom of the spreadsheet
+ 	for ( 1 .. @activities ) {
+ 		$worksheet->write_formula( 
+ 			$row, $_, 
+ 			sprintf(
+ 				"=SUM(\%s5:\%s\%d)/SUM(B5:\%s\%d)", 
+ 				chr( 65 + $_ ), chr( 65 + $_ ), $row, chr( 65 + scalar @activities ), $row,
+ 			),
+ 			$format_total_percent,
+ 		);
+ 	}
+ 	$worksheet->write_formula(
+ 		$row, scalar @activities + $_,
+ 		sprintf(
+ 			"=SUM(\%s5:\%s\%d)", 
+ 			chr( 65 + @activities + $_ ), chr( 65 + @activities + $_ ), $row
+ 		),
+ 		$workbook->add_format(
+ 			'top'    => 1,
+ 			'bottom' => 1,
+ 			'right'  => 1,
+ 			'bold'   => 1,
+ 		),
+ 	) for ( 1, 2 );
+ 
+ 	$workbook->close();
+ 	return;
+ }
+ 
  sub xls_team_view_build {
  	my ( $r, $filename, $title, $date_start, $date_end, $team_ids ) = @_;