#!/usr/bin/perl -w
#*****************************************************************************
#
# Filename:	mysql.pl
#
# CVS Revision:	$Revision: 2.4 $
#
# Description:	This perl CGI script reads the EXERCISE logs databases.
#		It is secured by using the REMOTE_USER environment variable.
#
#*****************************************************************************

# QUICK! send browser something always and define custom message
use CGI::Carp qw(fatalsToBrowser set_message);

BEGIN {
   sub handle_errors {
      my $error = shift;

      print "<h2>Software Logic Error:</h2>
<pre>$error</pre><br>
<h1>Please Contact the Web Administrator</h1>";
   }
   set_message(\&handle_errors);
}


#*****************************************************************************
#
# initialize variables
#
#*****************************************************************************
# compute arithmetic in integer instead of double
##use integer;
# restrict unsafe constructs
use strict;
# access mysql database
use DBI;
# program name
$ENV{SCRIPT_NAME} = $0;
my @Program = split("/", $ENV{SCRIPT_NAME});
$API::program = $Program[$#Program];
my $here = $API::program.'::main';
# diagnostic level
$API::diag = 0;
# secure my PATH, required by taint mode
$ENV{PATH} = "/usr/local/bin:/usr/bin";

# count the bytes sent
$API::queries = 0;

require "./.mmtc_passwd.pl";


#*****************************************************************************
#
# Routine:	get_racers
#
#*****************************************************************************
sub get_racers {
   my $here = $API::program.'::get_racers';

   my $rid = shift(@_);
    
   my $sth = $API::dbh->prepare("SELECT \
COUNT(meta_id) AS MMTC_Racers \
FROM wp_postmeta \
WHERE meta_key = 'race_date' \
AND meta_value = $rid;");
   $API::queries++;
   $sth->execute();

   my $ref = $sth->fetchrow_hashref();
   return $ref->{MMTC_Racers};
}


#*****************************************************************************
#
# Routine:	get_race_info
#
#*****************************************************************************
sub get_race_info {
   my $here = $API::program.'::get_race_info';

   my $rid = shift(@_);
   my $key = shift(@_);

   my $sth = $API::dbh->prepare("SELECT \
meta_value \
FROM wp_postmeta
WHERE meta_key = 'race' \
AND post_id = $rid;");
   $API::queries++;
   $sth->execute();

   my $ref = $sth->fetchrow_hashref();
   my $race = $ref->{meta_value};
   ##return "<cite>Race Date ID: $rid<br>\nRace ID: $ref->{meta_value}</cite>";
   $sth->finish();

   $sth = $API::dbh->prepare("SELECT \
post_title, meta_value \
FROM wp_postmeta, wp_posts \
WHERE post_status = 'publish' \
AND meta_key = '$key' \
AND post_id = $race \
AND meta_value = ID;");
   $API::queries++;
   $sth->execute();

   $ref = $sth->fetchrow_hashref();
   return "<td title='Date ID: $rid, Race ID: $race'>$ref->{post_title}</td>";
}


#*****************************************************************************
#
# Routine:	get_race_from_meta
#
#*****************************************************************************
sub get_race_from_meta {
   my $here = $API::program.'::get_race_from_meta';

   my $id = shift(@_);

   my $sth = $API::dbh->prepare("SELECT \
* \
FROM wp_postmeta \
WHERE post_id = $id \
ORDER BY meta_key;");
   $API::queries++;
   $sth->execute();

   my %META;
   my $tr_title = '';
   while (my $ref = $sth->fetchrow_hashref()) {
      $META{$ref->{meta_key}} = $ref->{meta_value};
      $tr_title .= "$ref->{meta_key}: $ref->{meta_value}\n";
   }

   if ($META{time} == 0) {
      return 0;
   }

   my $out = '';
   if ($META{comment}) {
      $out .= "<tr><th>Comment</th><td>$META{comment}</td></tr>\n";
   }
   if ($META{age_group_place} && $META{age_group_total}) {
      my $title = sprintf("%.1f%%", $META{age_group_place}/$META{age_group_total} * 100);
      $out .= "<tr title='$title'><th>AG Place</th><td>$META{age_group_place}/$META{age_group_total}</td></tr>\n";
   }
   if ($META{overall_place} && $META{overall_total}) {
      my $title = sprintf("%.1f%%", $META{overall_place}/$META{overall_total} * 100);
      $out .= "<tr title='$title'><th>Overall</th><td>$META{overall_place}/$META{overall_total}</td></tr>\n";
   }

   my @Booleans = (
      'no_pr',
      'dnf');
   foreach my $bool (@Booleans) {
      if ($META{$bool}) {
         $out .= "<tr><th>$bool</th><td><i class='fa fa-check-square-o'></i></td></tr>\n";
      }
   }

   $sth = $API::dbh->prepare("SELECT \
LEFT(SEC_TO_TIME(meta_value), 8) AS TIME \
FROM wp_postmeta \
WHERE post_id = $id \
AND meta_key = 'time' \
ORDER BY meta_key;");
   $API::queries++;
   $sth->execute();

   my $ref = $sth->fetchrow_hashref();
   $out .= "<tr title='$tr_title'><th>Time</th><td>$ref->{TIME}</td></tr>\n";

   return $out;
}


#*****************************************************************************
#
# Routine:	select_from_wp_posts
#
#*****************************************************************************
sub select_from_wp_posts {
   my $here = $API::program.'::select_from_wp_posts';

   my $order = shift(@_);
   my $value = shift(@_);
   if ($API::diag > 2) {
      print STDERR "$here: order=$order, value=$value\n";
   }

   my $sth = $API::dbh->prepare("SELECT \
wp_posts.ID AS RID, \
STR_TO_DATE(SUBSTRING(post_title FROM 5+LOCATE('day,', post_title)), '%M %d, %Y') AS Date, \
guid, display_name, post_title, post_content, post_type, post_name \
FROM wp_posts, wp_users \
WHERE post_author = wp_users.ID \
AND post_type = 'mmtc_race_result' \
AND post_status = 'publish' \
AND DATEDIFF(CURDATE(), DATE(post_date)) = ? \
ORDER BY $order \
LIMIT 99;");
   $API::queries++;
   $sth->execute($value);

   my $out = '';
   if ($ENV{HTTP_USER_AGENT}) { 
      $out = "Content-type: text/html\n\n";
   }
   $out .= "
<html>
<head>
  <title>MMTC Race Reports</title>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1'>
  <link rel='stylesheet' type='text/css' href='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css'>
  <link rel='stylesheet' type='text/css' href='http://fontawesome.io/assets/font-awesome/css/font-awesome.css'>
</head>
<body>

<div class='container'>
<center>
<div class='container-fluid'><h4><a href='https://www.midmdtriclub.org/'>MMTC</a> Race Reports</h4></div>
</center>\n";

   my $created = '';
   while (my $ref = $sth->fetchrow_hashref()) {
      my $from_meta = &get_race_from_meta($ref->{RID});
      unless ($from_meta) {
         next;
      }

      $created = $ref->{Date};

      $out .= "\
<table class='table table-striped table-hover table-bordered table-condensed sortable'>\n
<tr>\n";
      my $author = "Created by $ref->{display_name}";
      my $title = $ref->{post_title};
      my @Title = split("-", $title, 2);
      $out .= "<tr><th title='$author'>Racer</th><td><a title='$ref->{post_title}' href='$ref->{guid}' target='_blank'>$Title[0]</a></td></tr>\n";
      $out .= "<tr><th>Race</th><td><a title='$ref->{post_title}'>$Title[1]</a></td></tr>\n";
      $out .= $from_meta;

      my $report = $ref->{post_content};
      $report =~ s/\. /\.<br>/g;
      $report =~ s/\! /\!<br>/g;
      # translate apostrophe when using utf-8
      $report =~ s/'/&##39;/g;
      $out .= "<th>Race Report</th><td>$report</td>\n";
      ##$out .= "<th>Race Report</th><td>$ref->{post_content}</td>";

      $out .= "</tr>\n";
      $out .= "</table>\n";
   }
   $sth->finish();

   # no reports found, alert the caller
   if ($created eq '') {
      if ($ENV{HTTP_USER_AGENT}) {
         $out .= "<h1>No Reports</h1>\n";
      } else {
         print STDERR "$here: No reports in $API::queries queries\n";
         exit 1;
      }
   }

   my $title = "$API::queries queries";
   $out .= "<a name=END></a>
</center>
</body>

</div>
<foot title='$title'>Created: $created</foot>
</html>\n";

   print $out;
}


#*****************************************************************************
#
# Routine:      open_database
#
#*****************************************************************************
sub open_database {
   my $here = $API::program.'::open_database';

   my $db_name = shift(@_);
   my $db_user = shift(@_);
   my $db_passwd = shift(@_);
   if ($API::diag > 1) {
      print STDERR "$here: db_name=$db_name db_user=$db_user\n";
   }

   # Connect to the database.
   my $dbh = DBI->connect($db_name,
                          $db_user,
                          $db_passwd,
                          {'RaiseError' => 1});
   return $dbh;
}


#*****************************************************************************
#
# Routine:	main
#
#*****************************************************************************
$API::Site{connect} = "DBI:mysql:database=$API::Site{sql_db};host=$API::Site{sql_host}";

$API::dbh = &open_database($API::Site{connect},
			   $API::Site{account},
			   $API::Site{passwd});

&select_from_wp_posts('post_date DESC', 1);

# Disconnect from the database.
$API::dbh->disconnect();