mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00060
[Branch ~mvhub-dev/mvhub/trunk] Rev 358: merged report_4_reminder_email
Merge authors:
Dan MacNeil (omacneil)
HsienWen Hsu (hsienwen2007)
Related merge proposals:
https://code.launchpad.net/~hsienwen2007/mvhub/report_4_reminder_email/+merge/22724
proposed by: HsienWen Hsu (hsienwen2007)
review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 358 [merge]
committer: Dan MacNeil <dan@xxxxxxxxxx>
branch nick: trunk
timestamp: Sat 2010-04-03 12:50:57 -0400
message:
merged report_4_reminder_email
modified:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl
--
lp:mvhub
https://code.launchpad.net/~mvhub-dev/mvhub/trunk
Your team mvhub-dev is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-dev/mvhub/trunk/+edit-subscription
=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl 2010-03-26 16:42:55 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl 2010-04-02 19:31:16 +0000
@@ -13,8 +13,9 @@
use DBI;
use DBIx::XHTML_Table;
use HTML::Template;
-
+use MVHub::Utils::ConfigSimple qw/create_config_from/;
use MVHub::Common;
+
my $dbh = MVHub::Utils::DB::get_dbh( $ENV{MV_CONFIG_FILE} );
# These date formulas cry out for a database function:
@@ -22,6 +23,9 @@
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 = (
@@ -137,7 +141,7 @@
comment =>
'If the agency or program contact does not update their records after 4 emails we pick up the phone',
sql => q/
- SELECT
+ SELECT
a.agency_name,
a.contact_phone,
a.contact_first_name || ' '
@@ -150,7 +154,7 @@
a.contact_email like '%bad@xxxxxxxxxxxxxx%'
OR a.contact_email = 'none'
OR a.contact_email = 'unknown'
- OR a.reminders_sent >= 4
+ OR a.reminders_sent >= ?
UNION
SELECT
a.agency_name,
@@ -166,10 +170,11 @@
p.contact_email like '%bad@xxxxxxxxxxxxxx%'
OR p.contact_email = 'none'
OR p.contact_email = 'unknown'
- OR p.reminders_sent >=4
+ OR p.reminders_sent >=?
ORDER BY
agency_name,program_name
- /
+ /,
+ bind_values => [ $max_notifications, $max_notifications ]
},
{ title => 'Number of Programs under each Category',
@@ -308,9 +313,6 @@
if ( !defined $report_number ) {
print_report_list($cgi);
}
- elsif ( $report_number =~ /^\d{8}-\d{8}$/ ) {
- print_data_entry_report( $dbh, $cgi, $report_number );
- }
else {
print_report( $dbh, $cgi, $report_number );
}
@@ -329,124 +331,17 @@
}
}
-sub print_data_entry_report_list {
- my $dbh = shift;
- my $cgi = shift;
-
- print "<a href='reports.pl'>Main Reports List</a><br />\n";
- print "<h2>Data Entry Reports by Week:</h2>\n";
-
-# What do I want this to do? I need to get a list of all data entry users.
-# For each user, I need to know:
-#
-# Time period | Name of Data Entry Person | Data Error | Total number of records entered | percentage errors
-#
-# So when should I start the time periods? I guess beginning the week of July 9th.
-# Do I even need overall (not per-user) stats? I'll leave them out for now.
-#
-# So the first thing I need is a list of weeks, or at least a way to generate a list of weeks.
-# To make testing easier, I'll just generate my reports based on the earliest record in the
-# data entry tables, and have one for each of the subsequent weeks.
-#
-# I also need to know all the data entry users and be able to access their information on a per-week basis.
-#
-# Finally, I need to have statistics for each of these users. I need to be able to say which fields
-# were changed by the agency. The source for all this stuff will need to be the 'approved' versus
-# 'submitted' status levels. I won't bother with the 'saved' stuff. If an agency or program has multiple
-# submitted records, that's fine. I'll take the most recent of these records per user. So if Jim
-# submits a record, then Joe comes along, changes it, and submits it again, both Jim and Joe will have
-# statistics for this record. I'll leave the 'errors' part to fend for itself, as Jim's changes will
-# show up as changed fields for Joe.
-#
-# only list weeks where there's actually a report.
-# look at the data_entry_agency and data_entry_program
-# tables. I make a simplified query, take the union of the
-# results, sort by date, and return the first
-# entry. Then I find the current date and fill in the
-# blanks with a loop. I'll use the standard
-# yyyy-mm-dd format to make life easiest
-
- my $sql
- = "SELECT update_date FROM data_entry_agency "
- . "UNION "
- . "SELECT update_date FROM data_entry_program "
- . "ORDER BY update_date";
-
- my @dates = $dbh->selectrow_array($sql);
- my $earliest_record_date = $dates[0];
-
- # Chop off the time as only the day really matters here.
- $earliest_record_date =~ s/(\d{4}-\d{2}-\d{2}).*/$1/;
-
- # Where do we want our weeks to start? Sunday or Monday?
- # Five-day or seven-day weeks?
- # I'll be traditional and have seven-day weeks starting on Sundays.
- # $year, $month, and $day are working variables that get reused a bunch
- my ( $year, $month, $day ) = split /-/, $earliest_record_date;
- my $earliest_record_day = Date::Calc::Day_of_Week( $year, $month, $day );
-
- my ( $start_year, $start_month, $start_day );
-
- # Date::Calc's weeks start on Mondays, so 7 corresponds to Sunday.
- if ( $earliest_record_day == 7 ) {
- ( $start_year, $start_month, $start_day ) = ( $year, $month, $day );
- }
- else {
-
-# If the earliest record was created on a Monday, subtract 1 day
-# (Monday's Date::Calc date code) from the date of the earliest record--and so forth
-# Using Date::Calc to do this to allow for non-base-10 nature of dates (a regular integer
-# subtraction would fall flat on its face here)
- ( $start_year, $start_month, $start_day )
- = Date::Calc::Add_Delta_YMD( $year, $month, $day, 0, 0,
- $earliest_record_day * -1 );
- }
-
-# We now have the date to start the weeks. Now we need the ending date (now).
- my ( $end_year, $end_month, $end_day ) = (localtime)[ 5, 4, 3 ];
- $end_year += 1900; # To account for localtime's 1900 start point
- $end_month++; # To account for array's 0 start point
-
- # Time to generate links for each of the weeks!
- # Since I have three vars, a for loop obviously wouldn't cut it here.
- print "<ul>\n";
- ( $year, $month, $day ) = ( $start_year, $start_month, $start_day );
- while (
- $year <= $end_year
- && ( $month < $end_month
- || ( $month == $end_month && $day <= $end_day ) )
- )
- {
- $month = "0$month" if $month =~ /^\d$/;
- $day = "0$day" if $day =~ /^\d$/;
-
- my ( $end_week_year, $end_week_month, $end_week_day )
- = Date::Calc::Add_Delta_YMD( $year, $month, $day, 0, 0, 6 );
- $end_week_month = "0$end_week_month" if $end_week_month =~ /^\d$/;
- $end_week_day = "0$end_week_day" if $end_week_day =~ /^\d$/;
-
- print "<li><a href='reports.pl?report_number="
- . $year
- . $month
- . $day . "-"
- . $end_week_year
- . $end_week_month
- . $end_week_day . "'>"
- . Date::Calc::Month_to_Text($month)
- . " $day, $year" . " to "
- . Date::Calc::Month_to_Text($end_week_month)
- . " $end_week_day, $end_week_year"
- . "</a></li>\n";
- ( $year, $month, $day )
- = Date::Calc::Add_Delta_YMD( $year, $month, $day, 0, 0, 7 );
- }
- print "</ul>\n";
-}
-
sub print_report {
my ( $dbh, $cgi, $report_number ) = @_;
my $sth = $dbh->prepare( $REPORTS[$report_number]->{sql} );
- $sth->execute();
+
+ 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) {
@@ -469,282 +364,6 @@
print( ( scalar @$rows_aref ) . " records returned.<br/>\n" );
}
-sub print_data_entry_report {
- my ( $dbh, $cgi, $report_number ) = @_;
-
- print "<a href='reports.pl'>Main Reports List</a><br />\n";
- print
- "<a href='reports.pl?report_number=data_entry'>Data Entry Reports List</a><br />\n";
-
- # $report_number is in format of yyyymmdd-yyyymmdd
- my ( $report_start_date, $report_end_date ) = split /-/, $report_number;
-
- my $start_year = substr( $report_start_date, 0, 4 );
- my $start_month = substr( $report_start_date, 4, 2 );
- my $start_day = substr( $report_start_date, 6, 2 );
-
- my $end_year = substr( $report_end_date, 0, 4 );
- my $end_month = substr( $report_end_date, 4, 2 );
- my $end_day = substr( $report_end_date, 6, 2 );
-
- print "<h2>Data Entry: "
- . Date::Calc::Month_to_Text($start_month)
- . " $start_day, $start_year to "
- . Date::Calc::Month_to_Text($end_month)
- . " $end_day, $end_year</h2>";
-
- # Create links to stats for each data entry user
- print "<ul>\n";
-
- # Get basic user information (id, username, and full name)
- my $users_sql = "SELECT user_id, username, first_name, last_name "
- . "FROM users WHERE user_type = 1 ORDER BY user_id";
- my $users_aref = $dbh->selectall_arrayref( $users_sql, { Slice => {} } );
-
- @$users_aref = sort { $a->{last_name} cmp $b->{last_name} } @$users_aref;
-
- foreach my $user_href (@$users_aref) {
- print "<li><a href='#$user_href->{username}'>"
- . $user_href->{first_name} . " "
- . $user_href->{last_name}
- . "</a></li>";
- }
- print "</ul>\n";
- print "<hr />\n";
-
- foreach my $user_href (@$users_aref) {
- my $user_full_name
- = "$user_href->{first_name} $user_href->{last_name}";
- print
- "<h3><a name='$user_href->{username}'>$user_full_name</a></h3>\n";
-
- my $number_records_submitted = 0;
- my $number_records_approved = 0;
- my $number_fields_submitted = 0;
- my $number_fields_changed = 0;
-
-# Get all the agency records submitted by this user during this particular
-# week. I'm neglecting overlaps for now, but it's quite possible that
-# someone may submit a record on a Friday, then go back and submit
-# it again on a Monday. This would count as two separate records from the
-# report's point of view, as it has no way to distinguish one from the other.
-#
-# A possible way around this would be to only pull the last submitted record for each agency,
-# meaning that a multiply-submitted record would only show up the last time
-# it was submitted.
- my ( $submitted_agency_aref, $submitted_program_aref );
- foreach my $record_type qw(agency program) {
- my $sql
- = "SELECT DISTINCT on ("
- . $record_type
- . "_id) * FROM "
- . "(SELECT * FROM data_entry_"
- . $record_type
- . " WHERE updated_by = ? AND update_status = 2 "
- . " AND (update_date >= "
- . $dbh->quote("$start_year-$start_month-$start_day")
- . " AND update_date <= "
- . $dbh->quote("$end_year-$end_month-$end_day")
- . " ) ORDER BY update_date DESC) as subq";
- my $submitted_records_aref
- = $dbh->selectall_arrayref( $sql, { Slice => {} },
- $user_href->{user_id} );
-
- # generate $submitted_agency_aref or $submitted_program_aref
- if ( $record_type eq 'agency' ) {
- $submitted_agency_aref = $submitted_records_aref;
- }
- if ( $record_type eq 'program' ) {
- $submitted_program_aref = $submitted_records_aref;
- }
- }
-
- # I still need some way to sort the fields of each hash reference.
-
- # No output if no submitted agency records
- print "<h4>Agency Records</h4>\n";
- if ( scalar @$submitted_agency_aref != 0 ) {
- print
- "<table class='data_entry_reports' cellspacing='0' cellpadding='0'>";
- foreach my $submitted_agency_href (@$submitted_agency_aref) {
- $number_records_submitted++;
- my $agency_name = $submitted_agency_href->{agency_name};
- my $approved_agency_href = get_approved_record(
- $dbh, 'data_entry_agency',
- $submitted_agency_href->{agency_id},
- $submitted_agency_href->{update_date}
- );
- $number_records_approved++ if defined $approved_agency_href;
- my ( $submitted_fields, $changed_fields )
- = generate_record_table_and_record_fields(
- $submitted_agency_href, $approved_agency_href,
- $agency_name );
- $number_fields_submitted += $submitted_fields;
- $number_fields_changed += $changed_fields;
- }
- print "</table>";
- }
- else {
- print "<p>No agency records were submitted this week.</p>\n";
- }
-
- print "<h4>Program Records</h4>\n";
- if ( scalar @$submitted_program_aref != 0 ) {
- print
- "<table class='data_entry_reports' cellspacing='0' cellpadding='0'>";
- foreach my $submitted_program_href (@$submitted_program_aref) {
- $number_records_submitted++;
- my $program_name = $submitted_program_href->{program_name};
- my $approved_program_href = get_approved_record(
- 'data_entry_program',
- $submitted_program_href->{program_id},
- $submitted_program_href->{update_date}
- );
- $number_records_approved++ if defined $approved_program_href;
- my ( $submitted_fields, $changed_fields )
- = generate_record_table_and_record_fields(
- $submitted_program_href, $approved_program_href,
- $program_name );
- $number_fields_submitted += $submitted_fields;
- $number_fields_changed += $changed_fields;
- }
- print "</table>";
- }
- else {
- print "<p>No program records were submitted this week.</p>\n";
- }
-
- my $percent_fields_changed
- = 100 * ( $number_fields_changed / $number_fields_submitted )
- if $number_fields_submitted != 0;
-
- print <<HTML;
-<div class='user_stats'>
-<p>\n
-<strong>Number of records submitted this week:</strong> $number_records_submitted<br />\n
-<strong>Number of records approved this week:</strong> $number_records_approved</p>\n
-
-<p>\n
-<strong>Number of fields submitted this week:</strong> $number_fields_submitted<br />\n
-<strong>Number of fields changed this week:</strong> $number_fields_changed</p>\n
-HTML
- printf
- "<p><strong>Percentage of fields changed this week: </strong>%2.1f%%</p>\n",
- $percent_fields_changed
- if defined $percent_fields_changed;
- print "</div>\n";
-
- print "<hr />\n";
- }
-}
-
-sub print_traffic_summary {
- print "<p><a href='reports.pl'>Main Reports List</a></p>";
- local $\;
- open TRAFFIC_SUMMARY,
- "<../../../reports/agency_usage_reports/html/summary.inc"
- or die "$! : failed to open summary.inc";
- print <TRAFFIC_SUMMARY>;
- close TRAFFIC_SUMMARY;
-}
-
-# Generates an HTML table (displaying changed fields) for
-# a single agency or program record.
-sub generate_record_table_and_record_fields {
- my $submitted_record_href = shift;
- my $approved_record_href = shift;
- my $record_name = shift;
-
- # Don't display system fields--they'll skew stats
- # and make things harder to read.
- delete $submitted_record_href->{id};
- delete $submitted_record_href->{agency_id};
- delete $submitted_record_href->{program_id}
- if defined $submitted_record_href->{program_id};
- delete $submitted_record_href->{update_date};
- delete $submitted_record_href->{updated_by};
- delete $submitted_record_href->{update_status};
-
-# Keep a count of the number of submitted fields and the number of changed fields so
-# we can keep track of these on a per-user basis.
- my $number_submitted_fields = 0;
- my $number_changed_fields = 0;
-
- # Generate the actual HTML
- print
- "<tr><th colspan='3' class='agency_program_name'>$record_name</th></tr>\n";
- print "<tr class='headings'>\n";
- print "<th> </th>\n";
- print "<th>Submitted Value</th>\n";
- print "<th>Approved Value(s)</th>\n";
- print "</tr>\n";
- foreach ( keys %$submitted_record_href ) {
- my $field_name = $_;
- my $submitted_value = $submitted_record_href->{$_};
- my $approved_value = $approved_record_href->{$_};
-
- $number_submitted_fields++;
-
- if ( defined $approved_value && $submitted_value ne $approved_value )
- {
- $number_changed_fields++;
-
- print "<tr>\n";
- print "<th scope='row' class='field_name'>$field_name</th>\n";
-
- # For multi-valued fields, remove delimiters and change into list
- foreach ( $approved_value, $submitted_value ) {
- if (/!DELIM!/) {
- $_ = "<ul>\n<li>" . $_ . "</li>\n</ul>\n";
- s#!DELIM!#</li>\n<li>#g;
- }
- }
- if ( defined $submitted_value ) {
- print "<td class='submitted_value'>$submitted_value</td>\n";
- }
- else {
- print "<td></td>\n";
- }
- print "<td class='approved_value'>$approved_value</td>\n";
- print "</tr>\n";
- }
- }
-
- return $number_submitted_fields, $number_changed_fields;
-}
-
-# If someone's submitted a record, either for a program or an agency,
-# check for an approved record, returning an href for the approved
-# record nearest in date to the submitted record.
-sub get_approved_record {
- my $dbh = shift;
- my $db_table = shift;
- my $id = shift;
- my $submit_timestamp = shift;
-
- my $record_type;
- if ( $db_table =~ /agency/ ) {
- $record_type = 'agency';
- }
- elsif ( $db_table =~ /program/ ) {
- $record_type = 'program';
- }
- else {
- return undef;
- }
-
- my $sql
- = "SELECT * FROM $db_table WHERE "
- . $record_type
- . "_id = $id AND "
- . "update_status = 3 AND "
- . "update_date > "
- . $dbh->quote($submit_timestamp)
- . " ORDER BY update_date";
-
- return $dbh->selectrow_hashref($sql);
-}
-
# 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
@@ -773,6 +392,3 @@
return $sql;
}
-sub get_submitted_records_query {
-
-}
