logbook_pdf_report

#! /usr/bin/perl

use strict;
use warnings;

use DBI;
use DateTime::Format::MySQL;
use HTML::Restrict;

# html remover
my $hr = HTML::Restrict->new();

# database connection stuff
my $dbh = DBI->connect(
    "dbi:mysql:dbname=olin2",
    "user",
    "password",
    { RaiseError => 1 },
) or die $DBI::errstr;


# query to grab ALL entries from `olin2`.`lobby`, ordered by date
my $sth = $dbh->prepare("select l.id, l.date_added, l.username,
            l.entry
            from logbook l
            order by l.date_added desc");
$sth->execute();

# the LaTeX document preamble
print<<EOF;
\\documentclass{article}
\\usepackage{framed,graphicx,xcolor}
\\usepackage[top=.5in, bottom=.5in, left=.5in, right=.5in]{geometry}
\\usepackage{etoolbox}
\\BeforeBeginEnvironment{shaded}{\\begin{center} \\noindent\\begin{minipage}{.9\\linewidth}}
\\AfterEndEnvironment{shaded}{ \\end{minipage}\\end{center} }
\\raggedbottom
\\setlength{\\parskip}{.2em}
\\setlength{\\parindent}{0cm}
\\nonstopmode
\\setlength{\\topsep}{0pt}

\\begin{document}
\\definecolor{shadecolor}{gray}{.9}

EOF

my $row;

# set the `logbook` variables
while ($row = $sth->fetchrow_hashref()) {
    my  $id = $row->{id};
    my  $date_added = DateTime::Format::MySQL->parse_timestamp($row->{date_added});
    my  $username = $row->{username};
    my  $entry = $row->{entry};

    # filter some stuff out to make LaTeX behave
    $entry =~ s/<br \/>/\\\\/g;
    $entry =~ s/\\//g;
    $entry =~ s/\#/\\#/g;
    $entry =~ s/\&/\\&/g;
    $entry =~ s/\_/\\_/g;

    # filters out HTML tags
    $entry = $hr->process($entry);

    # get the first name of the `logbook`.`entry` author
    my $fname_query = 'select fname from users where username = ?';
    my @row1 = $dbh->selectrow_array($fname_query,undef,$username);
    my $fname = $row1[0];

    #print " \\fbox{";

    # each logbook entry is in a minipage (prevents line breaks in the middle
    # of an entry)
    print " \\begin{minipage}{\\linewidth}";

    # the date and author are in a table (easier to read)
    print " \\begin{tabular}{l r} \\\\ ";
    print $date_added->strftime("%a, %d %b %Y at %l:%M %p & ") .
        'By: ' . $fname .
        '\\end{tabular} \\\\ \\vspace{.2em} \\\\' . $entry . ' \\smallskip ';

    # query to grab the comments associated with this particular logbook entry
    my $comment_sth = $dbh->prepare("select c.date_added as cdate, c.username cuser, c.comment from comments as c
                        where c.logbook_id = ?
                    order by c.date_added asc");
    $comment_sth->execute($id);

    # set up the comments variables
    while (my $row = $comment_sth->fetchrow_hashref()) {
        if(defined($row->{comment}) && $row->{comment} ne '') {
            # comments are shaded to stand out
            print "\\begin{shaded}";
            my  $comment_date = DateTime::Format::MySQL->parse_timestamp($row->{cdate});
            my  $comment_username = $row->{cuser};
            my  $comment = $row->{comment};

            # filter some stuff out so LaTeX behaves
            $comment =~ s/<br \/>//g;
            $comment =~ s/\\//g;
            $comment=~ s/\#/\\#/g;
            $comment =~ s/\&/\\&/g;
            $comment =~ tr/\n//d;
            $comment =~ s/\_/\\_/g;

            # filters out HTML
            $comment = $hr->process($comment);

            # gets the comment author's first name (functionize this!)
            my $fname_query = 'select fname from users where username = ?';
            my @row2 = $dbh->selectrow_array($fname_query,undef,$comment_username);
            my $comment_fname = $row2[0];

            print "Date: " . $comment_date->strftime("%a, %d %b %Y at %l:%M %p") . " \\\\ ";
            print "By: $comment_fname \\\\";
            print "$comment \\\\ ";
            print "\\end{shaded} ";
        }
    }
print '\\vspace{.1em} \\\\ ';
print "\\end{minipage}";
print " \\hrule"; # seperates the logbook entries from each other

} # end of the main loop

print '\end{document}';

# clean up
$sth->finish();
$dbh->disconnect();