mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00354
[Branch ~mvhub-commit/mvhub/trunk] Rev 426: merged refactor_reports
Merge authors:
Lee Goodrich (leegoodrich)
Related merge proposals:
https://code.launchpad.net/~leegoodrich/mvhub/refactor_reports/+merge/28232
proposed by: Lee Goodrich (leegoodrich)
review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 426 [merge]
committer: Lee Goodrich <lgoodrich@xxxxxxxxxx>
branch nick: trunk
timestamp: Fri 2010-06-25 10:59:02 -0400
message:
merged refactor_reports
added:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin_reports.pl
lib-mvhub/lib/MVHub/Reports.pm
renamed:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl => app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl
modified:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/admin.pl
app-mvhub/DocumentRoot/static/mvh/html/reports.shtml
app-mvhub/DocumentRoot/static/nsp/html/reports.shtml
app-mvhub/t/mech/pages.t
app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl
--
lp:mvhub
https://code.launchpad.net/~mvhub-commit/mvhub/trunk
Your team MVHub Developers is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-commit/mvhub/trunk/+edit-subscription
=== 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:20:55 +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:00:02 +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-06-21 19:47:59 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/reports.pl 2010-06-25 14:58:28 +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:07:08 +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:00:02 +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:20:55 +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:20:55 +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;
+}
+
