mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00321
[Merge] lp:~leegoodrich/mvhub/refactor_reports into lp:mvhub
Lee Goodrich has proposed merging lp:~leegoodrich/mvhub/refactor_reports into lp:mvhub.
Requested reviews:
MVHub devs with commit rights (mvhub-commit)
Moved the reports to be under the public reports link accessible from the home page. Sanitized the newsletter report to remove private contact info, and moved three reports about missing contact info to a new admin_reports.pl file that is in the location of the old reports.pl file in the admin menu.
--
https://code.launchpad.net/~leegoodrich/mvhub/refactor_reports/+merge/28232
Your team MVHub Developers is subscribed to branch lp:mvhub.
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin.pl 2009-12-18 18:09:42 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin.pl 2010-06-22 19:27:29 +0000
@@ -49,8 +49,11 @@
),
' | ',
$cgi->a(
- { href => "/cgi-bin/mvhub/admin/reports.pl", TARGET => "main_frame" },
- "Reports" ),
+ { href => "/cgi-bin/mvhub/admin/admin_reports.pl",
+ TARGET => "main_frame"
+ },
+ "Reports"
+ ),
' | ',
$cgi->a(
{ href => "/cgi-bin/mvhub/agency_form.pl", TARGET => "main_frame" },
=== added file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl 1970-01-01 00:00:00 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl 2010-06-22 19:27:29 +0000
@@ -0,0 +1,138 @@
+#!/usr/bin/perl
+
+# $Revision: 1573 $
+# A very rudimentary and probably short-lived script for spitting out
+# query results into HTML pages.
+
+use warnings;
+use strict;
+
+use CGI;
+use CGI::Carp;
+use Date::Calc;
+use DBI;
+use DBIx::XHTML_Table;
+use HTML::Template;
+use MVHub::Utils::ConfigSimple qw/create_config_from/;
+use MVHub::Common;
+
+use MVHub::Reports qw/ print_report_list
+ print_report
+ get_sql_num_and_percent_up_to_date/;
+
+my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+# These date formulas cry out for a database function:
+# age formula is to account for DST anomalies
+my $age_formula = "age(last_updated + interval '2 hours')";
+my $month_formula
+ = "( date_part('year', $age_formula) * 12 + date_part('month',$age_formula) ) AS months_since_update";
+my $cfg
+ = MVHub::Utils::ConfigSimple::create_config_from( $ENV{MV_CONFIG_FILE} );
+my $max_notifications = $cfg->param('NOTIFICATION.max_notifications');
+
+my @REPORTS = (
+
+ # Use this as a template:
+ # {
+ # title => '',
+ # sql => qq/
+ #
+ # /
+ # },
+ { title => 'Agencies not responding to email or with bad contact email',
+ comment =>
+ 'If the agency or program contact does not update their records after 4 emails we pick up the phone',
+ sql => q/
+ SELECT
+ a.agency_name,
+ a.contact_phone,
+ a.contact_first_name || ' '
+ || a.contact_last_name AS contact,
+ a.contact_email,
+ ' MAIN AGENCY RECORD' AS program_name
+ FROM
+ agency a
+ WHERE
+ a.contact_email like '%bad@xxxxxxxxxxxxxx%'
+ OR a.contact_email = 'none'
+ OR a.contact_email = 'unknown'
+ OR a.reminders_sent >= ?
+ UNION
+ SELECT
+ a.agency_name,
+ p.contact_phone,
+ p.contact_first_name || ' '
+ || p.contact_last_name AS contact,
+ p.contact_email,
+ p.program_name
+ FROM
+ program p
+ JOIN agency a ON a.agency_id = p.agency_id
+ WHERE
+ p.contact_email like '%bad@xxxxxxxxxxxxxx%'
+ OR p.contact_email = 'none'
+ OR p.contact_email = 'unknown'
+ OR p.reminders_sent >=?
+ ORDER BY
+ agency_name,program_name
+ /,
+ bind_values => [ $max_notifications, $max_notifications ]
+ },
+ { title => 'Agencies with Missing or Unknown Contact Information',
+ sql => q/
+ SELECT agency_name, address1, email, main_phone, contact_first_name,
+ contact_last_name, contact_email, contact_phone
+ FROM agency
+ WHERE
+ address1 ~* '(unknown|contact|\\\\?)'
+ or email ~* 'unknown'
+ or contact_email ~* 'unknown'
+ or contact_first_name ~* '(unknown|contact|\\\\?)'
+ or contact_last_name ~* '(unknown|contact|\\\\?)'
+ /
+ },
+ { title => 'Programs with Missing or Unknown Contact Information',
+ sql => q/
+ SELECT p.program_name, a.agency_name, p.address1, p.email, p.main_phone,
+ p.contact_first_name, p.contact_last_name, p.contact_email,
+ p.contact_phone
+ FROM program p, agency a
+ WHERE
+ (
+ p.address1 ~* '(unknown|contact|\\\\?)'
+ or p.email ~* 'unknown'
+ or p.contact_email ~* 'unknown'
+ or p.contact_first_name ~* '(unknown|contact|\\\\?)'
+ or p.contact_last_name ~* '(unknown|contact|\\\\?)'
+ )
+ and a.agency_id = p.agency_id
+ /
+ }
+); # @REPORTS
+
+{
+ my $cgi = CGI->new();
+ my $report_number = $cgi->param('report_number');
+ my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
+
+ my $output;
+ print $cgi->header,
+ $cgi->start_html(
+ -title => 'Administrative Reports',
+ -style => { -src => '/all/css/admin.css' }
+ ),
+ $cgi->h1('Administrative Reports');
+
+ if ( !defined $report_number ) {
+ $output = MVHub::Reports::print_report_list( $cgi,
+ '/cgi-bin/mvhub/admin/admin_reports.pl', @REPORTS );
+ }
+ else {
+ $output = MVHub::Reports::print_report( $dbh, $cgi, $report_number,
+ '/cgi-bin/mvhub/admin/admin_reports.pl', @REPORTS );
+ }
+ print $output;
+ print $cgi->end_html;
+}
+
=== renamed file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl' => 'app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl 2010-04-02 19:31:16 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl 2010-06-22 19:27:29 +0000
@@ -13,8 +13,12 @@
use DBI;
use DBIx::XHTML_Table;
use HTML::Template;
+
use MVHub::Utils::ConfigSimple qw/create_config_from/;
use MVHub::Common;
+use MVHub::Reports qw/ print_report_list
+ print_report
+ get_sql_num_and_percent_up_to_date/;
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
@@ -97,11 +101,11 @@
{ title =>
'Number & percentage of Agencies & Programs updated in the Last 6 Months',
sql => 'SELECT * FROM ('
- . get_sql_num_and_percent_up_to_date( $dbh, 'agency', 'agencies',
- 6 )
+ . MVHub::Reports::get_sql_num_and_percent_up_to_date( $dbh,
+ 'agency', 'agencies', 6 )
. ') as agency_query, ('
- . get_sql_num_and_percent_up_to_date( $dbh, 'program', 'programs',
- 6 )
+ . MVHub::Reports::get_sql_num_and_percent_up_to_date( $dbh,
+ 'program', 'programs', 6 )
. ') as program_query '
},
@@ -137,45 +141,6 @@
GROUP BY agency_name ORDER BY "Oldest Record (yyyy-mm-dd)", agency_name
/
},
- { title => 'Agencies not responding to email or with bad contact email',
- comment =>
- 'If the agency or program contact does not update their records after 4 emails we pick up the phone',
- sql => q/
- SELECT
- a.agency_name,
- a.contact_phone,
- a.contact_first_name || ' '
- || a.contact_last_name AS contact,
- a.contact_email,
- ' MAIN AGENCY RECORD' AS program_name
- FROM
- agency a
- WHERE
- a.contact_email like '%bad@xxxxxxxxxxxxxx%'
- OR a.contact_email = 'none'
- OR a.contact_email = 'unknown'
- OR a.reminders_sent >= ?
- UNION
- SELECT
- a.agency_name,
- p.contact_phone,
- p.contact_first_name || ' '
- || p.contact_last_name AS contact,
- p.contact_email,
- p.program_name
- FROM
- program p
- JOIN agency a ON a.agency_id = p.agency_id
- WHERE
- p.contact_email like '%bad@xxxxxxxxxxxxxx%'
- OR p.contact_email = 'none'
- OR p.contact_email = 'unknown'
- OR p.reminders_sent >=?
- ORDER BY
- agency_name,program_name
- /,
- bind_values => [ $max_notifications, $max_notifications ]
- },
{ title => 'Number of Programs under each Category',
# We use a left join below so that we also see which categories are unused.
@@ -207,36 +172,6 @@
ORDER BY categories DESC, subq.program_and_agency_name
/
},
- { title => 'Agencies with Missing or Unknown Contact Information',
- sql => q/
- SELECT agency_name, address1, email, main_phone, contact_first_name,
- contact_last_name, contact_email, contact_phone
- FROM agency
- WHERE
- address1 ~* '(unknown|contact|\\\\?)'
- or email ~* 'unknown'
- or contact_email ~* 'unknown'
- or contact_first_name ~* '(unknown|contact|\\\\?)'
- or contact_last_name ~* '(unknown|contact|\\\\?)'
- /
- },
- { title => 'Programs with Missing or Unknown Contact Information',
- sql => q/
- SELECT p.program_name, a.agency_name, p.address1, p.email, p.main_phone,
- p.contact_first_name, p.contact_last_name, p.contact_email,
- p.contact_phone
- FROM program p, agency a
- WHERE
- (
- p.address1 ~* '(unknown|contact|\\\\?)'
- or p.email ~* 'unknown'
- or p.contact_email ~* 'unknown'
- or p.contact_first_name ~* '(unknown|contact|\\\\?)'
- or p.contact_last_name ~* '(unknown|contact|\\\\?)'
- )
- and a.agency_id = p.agency_id
- /
- },
{ title => 'Program Names that Repeat the Agency Name',
sql => q/
SELECT program_name, agency_name FROM program p, agency a
@@ -291,7 +226,7 @@
'All of the following programs have newsletters (though only some provided descriptions).',
sql => qq/
SELECT get_agency_name(agency_id) as agency_name, program_name,
- contact_first_name || ' ' || contact_last_name as contact_name, contact_email, contact_phone, news_letter_description
+ email, main_phone, news_letter_description
FROM program WHERE news_letter = 'Yes'
ORDER BY agency_name, program_name;
/
@@ -303,92 +238,28 @@
my $report_number = $cgi->param('report_number');
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
- print $cgi->header,
- $cgi->start_html(
- -title => 'Administrative Reports',
- -style => { -src => '/all/css/admin.css' }
- ),
- $cgi->h1('Administrative Reports');
+ my $output;
if ( !defined $report_number ) {
- print_report_list($cgi);
- }
- else {
- print_report( $dbh, $cgi, $report_number );
- }
- print $cgi->end_html;
-}
-
-# Since data entry reports have multiple screens, they're located outside
-# the @REPORTS array.
-sub print_report_list {
- my $cgi = shift;
-
- for ( my $i = 0; $i < @REPORTS; $i++ ) {
- print( "<a href='reports.pl?report_number=$i'>"
- . $REPORTS[$i]->{title}
- . "</a><br/><br/>\n" );
- }
-}
-
-sub print_report {
- my ( $dbh, $cgi, $report_number ) = @_;
- my $sth = $dbh->prepare( $REPORTS[$report_number]->{sql} );
-
- if ( defined $REPORTS[$report_number]->{bind_values} ) {
- my @bind_values = @{ $REPORTS[$report_number]->{bind_values} };
- $sth->execute(@bind_values);
- }
- else {
- $sth->execute();
- }
-
- my $headers_aref = $sth->{'NAME'};
- foreach (@$headers_aref) {
- $_ = ucfirst($_);
- s/_(\w)/ \u\L$1/g;
- }
- my $rows_aref = $sth->fetchall_arrayref();
-
- my $table = DBIx::XHTML_Table->new( $rows_aref, $headers_aref );
- $table->modify( 'table' => { border => 1 } );
-
- print "<a href='reports.pl'>Back to Report T.O.C.</a><br/>\n";
- print "<h2>$REPORTS[$report_number]->{title}</h2>\n";
- if ( $REPORTS[$report_number]->{comment} ) {
- my $comment = $REPORTS[$report_number]->{comment};
- $comment =~ s|\n|<br/>|g;
- print "<p>$comment</p>\n";
- }
- print $table->output() . "<br/>\n";
- print( ( scalar @$rows_aref ) . " records returned.<br/>\n" );
-}
-
-# Returns a SQL statement that selects
-# the number and percentage of records from table $table
-# updated in the past $prior_months, as a single column, named
-# $table_plural.
-sub get_sql_num_and_percent_up_to_date {
- my $dbh = shift;
- my $table = shift;
- my $table_plural = shift;
- my $prior_months = shift;
- my $sql = qq [
- SELECT updated_records.num
- || ' of '
- || all_records.num
- || ' ('
- || ((updated_records.num * 100) / all_records.num)
- || '\% )' as $table_plural
- FROM
- ( SELECT count(*) as num
- FROM $table
- WHERE last_updated > (current_date - interval '$prior_months months')
- )as updated_records,
-
- ( SELECT count(*) as num FROM $table
- )as all_records
- ];
- return $sql;
+ $output = $cgi->h1('Administrative Reports') . "\n";
+ $output .= MVHub::Reports::print_report_list( $cgi,
+ '/cgi-bin/mvhub/reports.pl', @REPORTS );
+ print $cgi->header();
+ }
+ else {
+ $output = MVHub::Reports::print_report( $dbh, $cgi, $report_number,
+ '/html/reports.shtml', @REPORTS );
+ $output = MVHub::Page::prepare_page(
+ body => $output,
+ transfer_internal_style_sheet => 1,
+ show_left_menu => 1,
+ show_search_bar => 0,
+ site_code => $cfg->param('SITE.website_code'),
+ );
+ print $cgi->header,
+ $cgi->start_html( -title => 'Administrative Reports', );
+ }
+
+ print $output;
}
=== modified file 'app-mvhub/DocumentRoot/static/mvh/html/reports.shtml'
--- app-mvhub/DocumentRoot/static/mvh/html/reports.shtml 2010-05-19 16:01:14 +0000
+++ app-mvhub/DocumentRoot/static/mvh/html/reports.shtml 2010-06-22 19:27:29 +0000
@@ -16,6 +16,7 @@
<div id="contentwrapper">
<div id="contentcolumn">
<div class="innertube">
+ <h1>PDF Reports</h1>
<p> <a href='/reports/MVHub.com_agencies_and_all_programs.pdf'>
All Agency and Program info
</a>
@@ -28,6 +29,7 @@
formatted for printing (.pdf)
</p>
+ <!--#include virtual="/cgi-bin/mvhub/reports.pl"-->
</div><!-- id=innertube -->
</div><!-- id='contentcolumn' -->
</div><!-- id='contentwrapper' -->
=== modified file 'app-mvhub/DocumentRoot/static/nsp/html/reports.shtml'
--- app-mvhub/DocumentRoot/static/nsp/html/reports.shtml 2010-05-19 16:01:14 +0000
+++ app-mvhub/DocumentRoot/static/nsp/html/reports.shtml 2010-06-22 19:27:29 +0000
@@ -16,6 +16,7 @@
<div id="contentwrapper">
<div id="contentcolumn">
<div class="innertube">
+ <h1>PDF Reports</h1>
<p> <a href='/reports/NorthShorePort.org_agencies_and_all_programs.pdf'>
All Agency and Program info
</a>
@@ -28,6 +29,7 @@
formatted for printing (.pdf)
</p>
+ <!--#include virtual="/cgi-bin/mvhub/reports.pl"-->
</div><!-- id=innertube -->
</div><!-- id='contentcolumn' -->
</div><!-- id='contentwrapper' -->
=== modified file 'app-mvhub/t/mech/pages.t'
--- app-mvhub/t/mech/pages.t 2010-02-01 04:20:39 +0000
+++ app-mvhub/t/mech/pages.t 2010-06-22 19:27:29 +0000
@@ -85,7 +85,7 @@
'tests_to_skip' => '',
'skip_reason' => '',
},
- { 'url' => "http://$host/cgi-bin/mvhub/admin/reports.pl";,
+ { 'url' => "http://$host/cgi-bin/mvhub/admin/admin_reports.pl";,
'expected_content' => "Administrative Reports",
'tests_to_skip' => '',
'skip_reason' => '',
=== added file 'lib-mvhub/lib/MVHub/Reports.pm'
--- lib-mvhub/lib/MVHub/Reports.pm 1970-01-01 00:00:00 +0000
+++ lib-mvhub/lib/MVHub/Reports.pm 2010-06-22 19:27:29 +0000
@@ -0,0 +1,100 @@
+package MVHub::Reports;
+use strict;
+use warnings;
+
+our ($VERSION) = '$Revision: 1740 $' =~ /([.\d]+)/xm;
+
+use CGI;
+use CGI::Carp;
+use Date::Calc;
+use DBI;
+use DBIx::XHTML_Table;
+use HTML::Template;
+use MVHub::Common;
+
+use base 'Exporter';
+our @EXPORT_OK = qw(
+ print_report_list
+ print_report
+ get_sql_num_and_percent_up_to_date
+);
+
+sub print_report_list {
+ my $cgi = shift;
+ my $path = shift;
+ my @reports = @_;
+ my $output;
+
+ for ( my $i = 0; $i < @reports; $i++ ) {
+ $output
+ .= "<a href='$path?report_number=$i'>"
+ . $reports[$i]->{title}
+ . "</a><br/><br/>\n";
+ }
+ return $output;
+}
+
+sub print_report {
+ my ( $dbh, $cgi, $report_number, $path, @reports ) = @_;
+ my $sth = $dbh->prepare( $reports[$report_number]->{sql} );
+ my $output;
+
+ if ( defined $reports[$report_number]->{bind_values} ) {
+ my @bind_values = @{ $reports[$report_number]->{bind_values} };
+ $sth->execute(@bind_values);
+ }
+ else {
+ $sth->execute();
+ }
+
+ my $headers_aref = $sth->{'NAME'};
+ foreach (@$headers_aref) {
+ $_ = ucfirst($_);
+ s/_(\w)/ \u\L$1/g;
+ }
+ my $rows_aref = $sth->fetchall_arrayref();
+
+ my $table = DBIx::XHTML_Table->new( $rows_aref, $headers_aref );
+ $table->modify( 'table' => { border => 1 } );
+
+ $output = "<a href='$path'>Back to Report T.O.C.</a><br/>\n";
+ $output .= "<h2>$reports[$report_number]->{title}</h2>\n";
+ if ( $reports[$report_number]->{comment} ) {
+ my $comment = $reports[$report_number]->{comment};
+ $comment =~ s|\n|<br/>|g;
+ $output .= "<p>$comment</p>\n";
+ }
+ $output .= $table->output() . "<br/>\n";
+ $output .= ( scalar @$rows_aref ) . " records returned.<br/>\n";
+
+ return $output;
+}
+
+# Returns a SQL statement that selects
+# the number and percentage of records from table $table
+# updated in the past $prior_months, as a single column, named
+# $table_plural.
+sub get_sql_num_and_percent_up_to_date {
+ my $dbh = shift;
+ my $table = shift;
+ my $table_plural = shift;
+ my $prior_months = shift;
+ my $sql = qq [
+ SELECT updated_records.num
+ || ' of '
+ || all_records.num
+ || ' ('
+ || ((updated_records.num * 100) / all_records.num)
+ || '\% )' as $table_plural
+ FROM
+ ( SELECT count(*) as num
+ FROM $table
+ WHERE last_updated > (current_date - interval '$prior_months months')
+ )as updated_records,
+
+ ( SELECT count(*) as num FROM $table
+ )as all_records
+ ];
+ return $sql;
+}
+
Follow ups
