<h3>Port Reporting</h3>
<FORM ACTION="<%$r->uri%>" METHOD=GET>
<INPUT TYPE="hidden" NAME="cmd" VALUE="search">
<TABLE WIDTH="85%" CELLSPACING=0 CELLPADDING=2 BORDER=0 CLASS=box>
<TR>
    <TH COLSPAN=2>Select Your Report Type</TH>
</TR>
<TR><TD COLSPAN=2><hr noshade></TD></TR>
<TR CLASS="match-1">
    <TD>Reason:</TD>
    <TD>
        <SELECT NAME="reason" MULTIPLE>
            <OPTION VALUE=""<% (scalar @reason and grep(/\S+/,@reason)) ? '' : ' SELECTED'%>>All Reasons
% foreach my $reason (keys %$db_reasons) {
            <OPTION VALUE="<%$reason|h%>" TITLE="<%$Reasons{$reason}->[1]||'' |h%>"<%grep(/^$reason$/,@reason) ? ' SELECTED' : ''%>><% $Reasons{$reason}->[0] || $reason %> (<%$db_reasons->{$reason}%>)
% }
        </SELECT>
    </TD>
</TR>
<TR CLASS="match-0">
    <TD>Text Search:
        <BR><font size=-1>Popular searches include <i>blaster</i> and <i>nachi</i>.
    </TD>
    <TD><INPUT TYPE="text" NAME="text" VALUE="<%$text|h%>"></TD>
</TR>
<TR CLASS="match-1">
    <TD>Action Type:
    <BR><font size=-1>Limit search to the enabling or disabling of ports.</font>
    </TD>
    <TD>
    <INPUT TYPE="radio" NAME="dir" VALUE="enable"<%$dir eq 'enable' ? ' CHECKED' : ''%>>Port Enable
    <INPUT TYPE="radio" NAME="dir" VALUE="disable"<%$dir eq 'disable' ? ' CHECKED' : ''%>>Port Disable
    <INPUT TYPE="radio" NAME="dir" VALUE="vlan"<%$dir eq 'vlan' ? ' CHECKED' : ''%>>VLAN Switch
    <INPUT TYPE="radio" NAME="dir" VALUE=""<%$dir eq '' ? ' CHECKED' : ''%>>All
    </TD>
</TR>
<TR CLASS="match-0">
    <TD>
    Report Type:
    <BR><font size=-1>Choose to see a list of log entries, or to see currently disabled ports that
<i>also</i> have log entries.</font>
    </TD>
    <TD>
    <INPUT TYPE="radio" NAME="type" VALUE=""<%$type eq '' ? ' CHECKED' : ''%>>Historical Log Entries&nbsp;
    <INPUT TYPE="radio" NAME="type" VALUE="now"<%$type eq 'now' ? ' CHECKED' : ''%>>Current Only <font size=-2>(slow)</font>&nbsp;
    </TD>
</TR>
<TR CLASS="match-1">
    <TD>User:</TD>
    <TD>
        <SELECT NAME="user" MULTIPLE SIZE=4>
            <OPTION VALUE=""<%grep(/\S+/, @user) ? '' : ' SELECTED'%>>All Users
% foreach my $user (sort keys %$db_users) {
            <OPTION VALUE="<%$user|h%>"<%grep(/^$user$/,@user) ? ' SELECTED' : ''%>><%$user%> (<%$db_users->{$user}%>)
% }
        </SELECT>
    </TD>
</TR>
<TR CLASS="match-0">
    <TD>Sort By:</TD>
    <TD>
        <SELECT NAME="sort" SIZE=3>
% foreach (qw/Date User Reason Port/){
            <OPTION VALUE="<%$_|h%>"<%$sort eq $_ ? ' SELECTED' : ''%>><%$_|h%>
% }
        </SELECT>
    </TD>
</TR>
<TR CLASS="match-1">
    <TD>Time Frame:</TD>
    <TD>
    <INPUT TYPE="radio" NAME="timeframe" VALUE=""<%$timeframe eq '' ? ' CHECKED' : ''%>          >All&nbsp;
    <INPUT TYPE="radio" NAME="timeframe" VALUE="today"<%$timeframe eq 'today' ? ' CHECKED' : ''%>>Today
    <INPUT TYPE="radio" NAME="timeframe" VALUE="week"<%$timeframe eq 'week' ? ' CHECKED' : ''%>  >This Week
    <INPUT TYPE="radio" NAME="timeframe" VALUE="month"<%$timeframe eq 'month' ? ' CHECKED' : ''%>>This Month
    <BR>
    <INPUT TYPE="radio" NAME="timeframe" VALUE="interval"<%$timeframe eq 'interval' ? ' CHECKED' : ''%>>Within the last<font size=-2></font>&nbsp;
    <SELECT NAME="timenumber">
% for (1..31) {
        <OPTION VALUE="<%$_%>"<%$timelength eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <SELECT NAME="timelength">
% for (qw/days months years/) {
        <OPTION VALUE="<%$_%>"<%$timenumber eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    </TD>
</TR>
<TR CLASS="match-0"><TD COLSPAN=2><hr noshade></TD></TR>
<TR CLASS="match-0">
    <TD COLSPAN=2 ALIGN=CENTER><INPUT TYPE=SUBMIT VALUE="Generate Report"> [<a href="<%$r->uri%>">Reset</A>]</TD>
</TR>
</TABLE>
</FORM>
<& SELF:report &>
<%args>
$cmd        => ''
$text       => ''
$dir        => 'disable'
@reason     => ()
$type       => '' 
$timeframe  => 'month'
$timelength => 'days'
$timenumber => 1
@user       => ()
$sort       => 'Date'
</%args>
<%doc>
TODO:
    - Implement Time frames for reports.
</%doc>
<%shared>
my %Reasons = %netdisco::PORT_CONTROL_REASONS;
my $matches = undef;
my $argsort = undef;
my $argtype = undef;
</%shared>
<%init>
# Get rid of \n's in the long reasons
foreach my $reason (keys %Reasons){
    $Reasons{$reason}->[1] =~ s/\n//g;
}
$argsort = $sort;
$argtype = $type;

# Grab the reasons and people out of the port log
# todo: sql_column has no way of doing a 'group by', so this kludge
my $db_reasons = {};
my $db_temp = sql_rows('device_port_log',
                ['distinct(reason) as reason','count(reason) as count'],
                {'action' => "!vlan"},
                0,'group by reason');
foreach my $row (@$db_temp){
    $db_reasons->{$row->{reason}}=$row->{count};
}
my $db_users = {};
$db_temp = sql_rows('device_port_log',['distinct(username) as username','count(username) as count'],undef,0,'group by username');
foreach my $row (@$db_temp){
    $db_users->{$row->{username}}=$row->{count};
}

# Search for ports
if ($cmd eq 'search'){

    #Get current time
    my ($second, $minute, $hour, $day, $month, $year, $weekDay, $dayofyear, $isDST) = localtime;
    if($day < 10) {
       $day = "0" . $day;
    }
    $month++;
    if($month < 10) {
    	$month = "0" . $month;
    }
    $year += 1900;
    my $yearMonth = $year . "-" . $month;
    my $yearMonthDay = $yearMonth . "-" . $day;
    $timelength =~ s/[^a-z ]+//g;
    $timenumber = 1 unless $timenumber =~ m/^\d+$/;
    
    # Get ports from port device log
    my $tables = 'device_port_log l left join device d on d.ip = l.ip';
    my $cols = ['d.dns','l.reason','l.port','l.ip','l.log','l.username','l.userip','l.action','extract(epoch from l.creation) as creation'];
    my $where = {};
    $where->{'l.log'} = '%'.$text.'%'  if $text;
    $where->{'l.reason'} = [\@reason] if (scalar @reason and grep(/\S+/,@reason));
    $where->{'l.action'} = $dir if $dir;
    $where->{'l.username'} = [\@user] if (scalar @user and grep(/\S+/,@user));
    if ($type eq 'now'){
        $tables .= ' left join device_port p on l.ip = p.ip and l.port = p.port';
        $where->{'p.up_admin'} = 'down';
    }
    if ($timeframe eq 'interval'){
    	if ($timelength eq 'days'){
		$where->{'age(l.creation)'} = \\"<= interval \'$timenumber day\'";
	} elsif ($timelength eq 'months'){
		$where->{'age(l.creation)'} = \\"<= interval \'$timenumber mon\'";
	} else {
		$where->{'age(l.creation)'} = \\"<= interval \'$timenumber year\'";
	}
    } elsif ($timeframe eq 'today'){
    	$where->{'l.creation'} = '%'.$yearMonthDay.'%';
    } elsif ($timeframe eq 'week'){
    	$where->{'age(l.creation)'} = \\"<= interval \'$weekDay day\'";
    } elsif ($timeframe eq 'month'){
    	$where->{'l.creation'} = '%'.$yearMonth.'%';
    }
    #$netdisco::SQLCARP=1;
    $matches = sql_rows($tables,$cols,$where) || [];
    #$netdisco::SQLCARP=0;
}
</%init>
<%method report>
<%perl>
return unless defined $matches;

# Sort by routines
@$matches = sort { $b->{creation} <=> $a->{creation} } @$matches if $argsort eq 'Date';

if ($argsort eq 'User'){
    @$matches = sort {
        # Sort by username
        if ($a->{username} ne $b->{username}) {
            return ($a->{username} cmp $b->{username});
        }
        # Secondary sort by creation
        return $b->{creation} <=> $a->{creation};
                     } @$matches;
}

if ($argsort eq 'Reason'){
    @$matches = sort {
        # Sort by Reason
        if ($a->{reason} ne $b->{reason}){
            return $a->{reason} cmp $b->{reason};
        }
        # 2nd Sort by Date
        return $b->{creation} <=> $a->{creation}; 
                     } @$matches;
}

if ($argsort eq 'Port'){
    @$matches = sort {
        # Sort by IP
        if ($a->{ip} ne $b->{ip}) {
            # Anyone tell me why this will only work when I cast it to scalar???
            return scalar &sort_ip;
        }
        # 2nd - Sort by Port
        if ($a->{port} ne $b->{port}){
            return scalar &sort_port;
        }
        # 3rd - Sort by date
        return $b->{creation} <=> $a->{creation};
                    } @$matches;
}

</%perl>
<h3>Matching Ports</h3>
% unless (scalar @$matches) {
No Ports Matching.<BR>
%return;}
<%scalar @$matches%> Ports matching.<BR>
% if ($argtype eq 'now'){
<B>Showing log entries for ports that are currently disabled only.</b><bR>
% }
<P>
<TABLE CELLSPACING=0 CELLPADDING=0 BORDER=0 WIDTH="100%">
<TR>
    <TH>Port</TH>
    <TH>Who</TH>
    <TH>Reason/Action</TH>
    <TH>Log</TH>
    <TH>Date</TH>
</TR>
<TR><TD COLSPAN=6><HR NOSHADE></TD></TR>
% my $count = 0;
% foreach my $port (@$matches) {
%   my $dev = $port->{dns} || $port->{ip};
%   $dev =~ s/\Q$netdisco::CONFIG{domain}\E//;
<TR CLASS="match-<%++$count % 2 %>">
    <TD><font size=-1><A HREF="device.html?ip=<%$port->{ip}|u%>&port=<%$port->{port}|u%>"><%$dev%> [<%$port->{port}%>]</A></font></TD>
    <TD><%$port->{username}%><BR><font size=-1><a href="node.html?node=<%$port->{userip}%>"><%$port->{userip}%></a></font></TD>
    <TD><%$port->{reason}%>/<%$port->{action}%></TD>
    <TD><font size=-2><%$port->{log} || '&nbsp;'%></font></TD>
    <TD><NOBR><% scalar localtime($port->{creation})%></NOBR></TD>
</TR>
% }
</TABLE>
</%method>
<%method title>
- Port Reporting \
</%method>
%# $Id: port_report.html,v 1.8 2006/06/28 21:59:27 maxbaker Exp $
%# vim:syntax=mason
