• Files
• Commits
• Log
• Graph
• Stats

package CommsLog;

# switch on query output to console using "SQL_TRACE=1 dance"

use Dancer2;

use DateTime;
use Local::DB;
use Local::Utils;
use Data::Printer;
use SQL::Abstract::More;

# warn setting('environment'); # dispatch.cgi requires manual set in BEGIN block

# turn off datetime objects:
# $Local::DBIx::Simple::Result::NO_AUTO_DATE_INFLATION = 1;

my $dbix = Local::DB->dbix({ dbname => 'hilis4' });

# maximum number of records to retrieve ----------------------------------------
my $max_msg_num = setting('max_results');
#-------------------------------------------------------------------------------

our $VERSION = '0.01';

hook before => sub {
    $ENV{SQL_TRACE} ||= setting('enable_sql_trace'); # don't override if already set

    return if request->path_info =~ m!^/index!; # don't need a session for this

    my $vars = request->parameters->as_hashref; _debug($vars);
    my $session_id = $vars->{_sid} or halt 'Not logged into HILIS.';
    check_session($session_id)
        or halt 'Session expired. Please return to HILIS and try again.';
};

hook before_template_render => sub {
    my $tokens = shift;
    my %uri = map { $_ => uri_for('/' . $_) } qw(search new_record);
    $tokens->{uri_for} = \%uri;

    $tokens->{session_id} =
        body_parameters->get('_sid') || query_parameters->get('_sid');
};

# original route/tt:
get '/index' => sub { template index => {}, { layout => 'default' } };

get '/' => sub { template 'main' };

post '/search' => sub {
    my $vars = request->parameters->as_hashref; _debug($vars);

    my $results = do_search($vars); # p $results;
    template 'main', { results => $results };
};

post '/new_record' => sub {
    # get user.id corresponding to session id:
    my $user_id = get_user_id( body_parameters->get('_sid') ); # p $user_id;
    my $details = body_parameters->get('details');

    my %h = (
        user_id  => $user_id,
        details  => $details,
    );
    my $res = $dbix->insert('general_comms_log', \%h);

    if ( $res->rows ) {
        var msg => 'input success';
    }
    else {
        var msg => 'input failed'; # may not get this far, probably 500 error
    }
    forward '/search', { details => $details };
};

#===============================================================================
sub do_search {
    my $vars = shift; # username, details, start_date, end_date

    my @cols = ( 'l.details', 'u.first_name', 'u.last_name', 'l.time' );
    my @rels = ( 'general_comms_log|l' => 'l.user_id=u.id' => 'users|u' );

    my %params = (
        -columns => \@cols,
        -where   => get_search_params($vars),
        -from    => [ -join => @rels ],
    ); # p \%params;
    my ($sql, @bind) = SQL::Abstract::More->new->select(%params); # p [$sql, \@bind];

    { # check count, return if excessive (> $max_msg_num):
        local $params{'-columns'} = 'COUNT(*)';
        my ($sql, @bind) = SQL::Abstract::More->new->select(%params); # p [$sql, \@bind];
        my $n = $dbix->query($sql, @bind)->value;
        if ( $n > $max_msg_num ) {
            var msg => "too many records found ($n), please limit search"
                . " and retry ...";
            var total => $n;
            return undef;
        }
    }

    my $res = $dbix->query($sql, @bind)->hashes;
    return $res;
}

sub get_search_params {
    my $vars = shift;

    my %h;
    if ( my $username = $vars->{username} ) { # search username & last_name
        push @{ $h{'-or'} }, [
            'u.username'  => { -like => $username . '%' },
            'u.last_name' => { -like => $username . '%' },
        ];
    }
    if ( my $details = $vars->{details} ) {
        $h{'l.details'} = { -rlike => $details };
    }
    { # dates:
        my ($start_date, $end_date) = map reformat_date($vars->{$_}),
            qw/start_date end_date/; # p [$start_date, $end_date];
        my $date_key = 'DATE(l.time)';

        if ( $start_date && $end_date ) {
            $h{$date_key} = { -between => [$start_date, $end_date] };
        }
        else {
            if ( $start_date ) {
                $h{$date_key} = { '>=' => $start_date };
            }
            elsif ( $end_date ) {
                $h{$date_key} = { '<=' => $end_date };
            }
        } # p \%h;
    }
    return \%h;
}

sub get_user_id {
    my $session_id = shift; # p $session_id;

    my @rels = ( 'sessions|s' => 's.userid=u.username' => 'users|u' );
    my %params = (
        -columns => 'u.id',
        -where   => { 's.id' => $session_id },
        -from    => [ -join => @rels ],
    );
    my ($sql, @bind) = SQL::Abstract::More->new->select(%params); # p [$sql, \@bind];
    my $id = $dbix->query($sql, @bind)->value;
    return $id;
}

sub check_session {
    my $session_id = shift || return 0; # p $session_id;

    my $now = Local::Utils::time_now; # p $now;

    my %h = (
        id   => $session_id,
        time => { '>' => $now->subtract( minutes => 30 ) },
    );
    return $dbix->count(sessions => \%h);
}

sub reformat_date { # returns date in yyyy-mm-dd format
    my $str = shift || return; # p $str;
    # $date =~ s/(^\d{1,2})\/(\d{1,2})\/(\d{4}$)/$3-$2-$1/;
    my $dt = Local::Utils::date_string_to_datetime($str)
        or die "cannot parse date string: $str"; # p $dt->ymd;
    return $dt->ymd;
}

sub _debug { p @_ if setting('environment') =~ /^dev/ }

true;