#!/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::bytes = 0;
$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 $title = '';
   while (my $ref = $sth->fetchrow_hashref()) {
       $META{$ref->{meta_key}} = $ref->{meta_value};
       $title .= "$ref->{meta_key}: $ref->{meta_value}\n";
   }

   if (($META{time} eq '0') ||
       ($META{time} eq '00:00:00')) {
       ##print STDERR "$here: time=$META{time}\n";
       return 0;
   }
   my $xxx = $title;

   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}) {
       $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}) {
      $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='$xxx'><th>Time</th><td>$ref->{TIME}</td></tr>\n";

   return $out;
}

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

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

   my $sth = $API::dbh->prepare("SELECT \
wp_posts.ID AS RID, DATE_FORMAT(post_date,'%W, %b %D, %Y') AS Date, \
guid, display_name, post_title, post_content, post_type, post_name \
FROM wp_posts, wp_users \
WHERE post_status = 'publish' AND \
post_author = wp_users.ID AND post_type = 'mmtc_race_result' \
ORDER BY post_date DESC \
LIMIT ?;");
   $API::queries++;
   $sth->execute(111);

   my $out = "Content-type: text/html

<html>
<head>
  <title>MMTC Race Reports</title>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1'>
  <link rel='stylesheet' href='/bootstrap.min.css'>
  <script language='javascript' type='text/javascript' src='/jquery-2.1.3.min.js'></script>
  <script language='javascript' type='text/javascript' src='/bootstrap.min.js'></script>
  <script language='javascript' type='text/javascript' src='/sorttable.js'></script>
</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
<table class='table table-striped table-hover table-bordered table-condensed sortable'>\n";

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

      $created = $ref->{Date};
      my $author = "Created by $ref->{display_name}";
      my $title = $ref->{post_title};
      my @Title = split("-", $title, 2);
      my $href = $ref->{guid};
      my $report = $ref->{post_content};
      $report =~ s/\. /\.<br>/g;
      $report =~ s/\! /\!<br>/g;
      # translate apostrophe when using utf-8
      $report =~ s/'/&##39;/g;

      $out .= "<tr>\n";
      $out .= "<tr><th>Racer</th><td><a title='$author'            >$Title[0]</a></td></tr>\n";
      $out .= "<tr><th>Race </th><td><a title='$title' href='$href'>$Title[1]</a></td></tr>\n";
      $out .= $tr_from_meta;
      $out .= "<tr><th>Race Report</th><td>$report</td></tr>\n";
      ##$out .= "<tr><th>Race Report</th><td>$ref->{post_content}</td></tr>\n";
      $out .= "<tr><th bgcolor='silver' colspan=9></th></tr>";
   }
   $sth->finish();

   # no reports found, alert the caller
   if ($created eq '') {
      exit 1;
   }

   $out .= "</table>\n";
   $out .= "<a name=END></a>
</center>
</body>

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

   print $out;
   $API::bytes += length($out);
}

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

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

   my $out = "<a name=END></a>
</center>
</body>

</div>
<foot>$msg</foot>
</html>\n";
   print $out;
   $API::bytes += length($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_star('midmdtriclub_org', 'wp_posts', 'post_type', 'mmtc_race_result');

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