#!/Perl/bin/perl.exe -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;

use Getopt::Std;            # Use Perl5 built-in program argument handler

require "./.mmtc_passwd.pl";
##require "./Bootstrap_API.pl";

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

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

   $API::Date{day} = (localtime)[3];
   $API::Date{mmm} =
       ('Jan',
        'Feb',
        'Mar',
        'Apr',
        'May',
        'Jun',
        'Jul',
        'Aug',
        'Sep',
        'Oct',
        'Nov',
        'Dec') [(localtime)[4]];
   $API::Date{month} = (localtime)[4] + 1;
   $API::Date{year} = (localtime)[5]+1900;
   $API::Date{day_of_week} =
       ('Sun',
        'Mon',
        'Tue',
        'Wed',
        'Thu',
        'Fri',
        'Sat') [(localtime)[6]];

   # to calculate week, add for January 1st offset
   use Time::Local;
   my $gmtime = timegm(0,0,0,1,0,$API::Date{year}-1900);
   my @Gmtime = gmtime($gmtime);
   my $off = $Gmtime[6] - 1;
   if ($API::diag > 3) {
      my ($day_of_week) = gmtime($gmtime) =~ /^(\S+)/; # fetch first word
      print STDERR "$here: off=$off, Gmtime[6]=$Gmtime[6], day_of_week=$day_of_week\n";
   }
   $API::Date{week} = int(((localtime)[7] + $off)/7);

   my $day = $API::Date{day};
   my $mon = (localtime)[4]+1;
   my $year = $API::Date{year};
   if ($field eq 'Due' ||
       $field eq 'No_Later_Than'||
       $field eq 'Due_Date' ||
       $field eq 'End_Date' ||
       $field eq 'Delivery_Date') {
      my $days = 30;
      $day += 14;
      if ($day > $days) {
         $day -= $days;
         if (++$mon > 12) {
            $mon = 1;
            $year++;
         }
      }
   }
   if ($day < 10) {
      $day = "0$day";
   }
   if ($mon < 10) {
      $mon = "0$mon";
   }
   return "$year-$mon-$day";
}


#*****************************************************************************
#
# 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} == 0) {
       return 0;
   }
   my $xxx = $title;

   my $out = "<table class='table table-striped table-hover table-bordered table-condensed sortable'>\n";
   ##///$out .= "<caption>$title</caption>\n";
   if ($META{age_group_total}) {
       $title = $META{age_group_place}/$META{age_group_total};
       $out .= "<tr title='$title%'><th><nobr>Age Group Place</nobr></th><td>$META{age_group_place}/$META{age_group_total}</td></tr>\n";
   }
   if ($META{overall_total}) {
       $title = $META{overall_place}/$META{overall_total};
       $out .= "<tr title='$title%'><th><nobr>Overall Place</nobr></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";

   $out .= "</table>\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 @Columns = ('Date',
                  'Racer',
                  'Metrics',
                  'Race Report');

   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_author = wp_users.ID AND post_type = 'mmtc_race_result' AND post_status = 'publish' \
AND wp_posts.ID = ? \
ORDER BY post_date DESC \
LIMIT 0001;");
   $API::queries++;
   $sth->execute($ARGV[0]);

   my $out = "<table class='table table-striped table-hover table-bordered table-condensed sortable'>\n";

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

      $out .= "<tr>";
      ##///$out .= "<td>$ref->{Date}</td>";

      my @Title = split("-", $ref->{post_title}, 2);
      $out .= "<tr><th>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 .= "<td><a href='$ref->{guid}' target='_blank'>$ref->{post_title}</a></td>";

      my $author = "Created by $ref->{display_name}";
      $out .= "<td title='$author'>$from_meta</td>";

      ##///$out .= &get_race_info($ref->{RID}, 'race_type');

      $out .= "<td>$ref->{post_content}</td>";

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

   &header("Race Report: $ARGV[0]");
   print $out;
   $API::bytes += length($out);
   &footer("Created: $API::today");
}

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

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

   my $div_title = "<div class='container-fluid'><h4><a href='https://www.midmdtriclub.org/'>MMTC</a> $title</h4></div>";

   my $out = "<html>
<head>
  <title>$title</title>
  <meta charset='utf-8'>
  <meta name='viewport' content='width=device-width, initial-scale=1'>
  <link rel='stylesheet' 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'>
  <script src='https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js'></script>
  <script src='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js'></script>
</head>
<body>

<div class='container'>
<center>
$div_title
</center>
";
   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;
}

sub usage {
   my $here = 'usage';

   print STDOUT "
	Usage: $0 [options] 'ID'

   -d {integer} = set Diagnostic level.
   -q           = be quite, suppressing graph created messages.
\n";
}


#*****************************************************************************
#
# Routine:	main
#
#*****************************************************************************
$API::today = &get_date('Today');

our($opt_c, $opt_d, $opt_f, $opt_i, $opt_m, $opt_q, $opt_s);

if (("@ARGV" =~ /\?/) ||
    ("@ARGV" =~ /-h/) ||
    "@ARGV" =~ /--help/) {
  &usage;
  exit 0;
}

# Define possible args.
getopts('d:cimsqv');

# check for diagnostic level
if ($opt_d) {
  $my::diag = $opt_d;
  print STDERR "$here: Diagnostic level set to $my::diag\n";
} else {
  $my::diag = 0;
}

$my::quite = 0;
if ($opt_q) {
  $my::quite = 1;
}

$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();
