[WTF CVS] wtf/lib/WTF/Pages Reports.pm,1.15,1.16
Brought to you by:
gryphonshafer
Menu
▾
▴
[WTF CVS] wtf/lib/WTF/Pages Reports.pm,1.15,1.16
|
From: Gryphon S. <gry...@us...> - 2007-03-21 17:26:42
|
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 ) = @_; |
