#!/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_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};
   $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>$ref->{post_title}</td>";
}


#*****************************************************************************
#
# 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',
                  'Title',
                  'MMTC<br>Racers',
                  'Race<br>Type');

   my $sth = $API::dbh->prepare("SELECT \
wp_posts.ID AS RID, DATE_FORMAT(post_date,'%W, %b %D, %Y') AS Date,
DATEDIFF(DATE(post_date), CURDATE()) AS TTT, display_name, \
post_title AS Title, post_content AS Description, \
guid, post_type, post_name \
FROM wp_posts, wp_users \
WHERE post_author = wp_users.ID AND post_type = 'mmtc_race_date' AND post_status = 'publish' AND DATE(post_date) > NOW() \
GROUP BY post_title \
ORDER BY post_date \
LIMIT 99;");
   $API::queries++;
   $sth->execute();

   my $out = "<table class='table table-striped table-hover table-bordered table-condensed sortable'>
<caption>Updated: $API::today</caption>\n";

   $out .= '<tr>';
   foreach my $col (@Columns) {
      $out .= "<th>$col</th>";
   }
   $out .= '</tr>';

   while (my $ref = $sth->fetchrow_hashref()) {
      $out .= "<tr>";

      my $title;
      if ($ref->{TTT} == 1) {
          $title = 'tomorrow';
      } else {
          $title = "$ref->{TTT} days from today";
      }
      $out .= "<td sorttable_customkey='$ref->{TTT}' title='$title'>$ref->{Date}</td>";

      my @Title = split("-", $ref->{Title}, 2);
      my $href = "$ref->{guid}/$ref->{post_type}/$ref->{post_name}";
      my $author = "Created by $ref->{display_name}";
      $out .= "<td title='$author'><a href='$href' target='_blank'>$Title[0]</a></td>";

      my $racers = &get_racers($ref->{RID});
      $out .= "<td><a href='$href' target='_blank'>$racers</a></td>";

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

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

   &header("Upcoming Races", "");
   print $out;
   $API::bytes += length($out);
   &footer('');
}

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

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

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

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

<html>
<head>
  <title>$title</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_title
<cite>$msg<cite>
</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;
}

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

$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_date');

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