1package App::Netdisco::Web::Plugin::Device::Ports;
2
3use Dancer ':syntax';
4use Dancer::Plugin::DBIC;
5use Dancer::Plugin::Auth::Extensible;
6
7use App::Netdisco::Util::Web (); # for sort_port
8use App::Netdisco::Web::Plugin;
9
10register_device_tab({ tag => 'ports', label => 'Ports', provides_csv => 1 });
11
12# device ports with a description (er, name) matching
13get '/ajax/content/device/ports' => require_login sub {
14    my $q = param('q');
15    my $prefer = param('prefer');
16    $prefer = ''
17      unless defined $prefer and $prefer =~ m/^(?:port|name|vlan)$/;
18
19    my $device = schema('netdisco')->resultset('Device')
20      ->search_for_device($q) or send_error('Bad device', 400);
21    my $set = $device->ports->with_properties;
22
23    # refine by ports if requested
24    my $f = param('f');
25    if ($f) {
26        if (($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) {
27            return unless $f =~ m/^\d+$/;
28        }
29        else {
30            if (param('partial')) {
31                # change wildcard chars to SQL
32                $f =~ s/\*/%/g;
33                $f =~ s/\?/_/g;
34                # set wildcards at param boundaries
35                if ($f !~ m/[%_]/) {
36                    $f =~ s/^\%*/%/;
37                    $f =~ s/\%*$/%/;
38                }
39                # enable ILIKE op
40                $f = { (param('invert') ? '-not_ilike' : '-ilike') => $f };
41            }
42            elsif (param('invert')) {
43                $f = { '!=' => $f };
44            }
45
46            if (($prefer eq 'port') or not $prefer and
47                $set->search({-or => ['me.port' => $f, 'me.descr' => $f]})->count) {
48
49                $set = $set->search({
50                  -or => [
51                    'me.port' => $f,
52                    'me.descr' => $f,
53                    'me.slave_of' => $f,
54                  ],
55                });
56            }
57            else {
58                $set = $set->search({'me.name' => $f});
59                return unless $set->count;
60            }
61        }
62    }
63
64    # filter for port status if asked
65    my %port_state = map {$_ => 1}
66      (ref [] eq ref param('port_state') ? @{param('port_state')}
67        : param('port_state') ? param('port_state') : ());
68
69    return unless scalar keys %port_state;
70
71    if (exists $port_state{free}) {
72        if (scalar keys %port_state == 1) {
73            $set = $set->only_free_ports({
74              age_num => (param('age_num') || 3),
75              age_unit => (param('age_unit') || 'months')
76            });
77        }
78        else {
79            $set = $set->with_is_free({
80              age_num => (param('age_num') || 3),
81              age_unit => (param('age_unit') || 'months')
82            });
83        }
84        delete $port_state{free};
85        # showing free ports requires showing down ports
86        ++$port_state{down};
87    }
88
89    if (scalar keys %port_state < 3) {
90        my @combi = ();
91
92        push @combi, {'me.up' => 'up'}
93          if exists $port_state{up};
94        push @combi, {'me.up_admin' => 'up', 'me.up' => { '!=' => 'up'}}
95          if exists $port_state{down};
96        push @combi, {'me.up_admin' => { '!=' => 'up'}}
97          if exists $port_state{shut};
98
99        $set = $set->search({-or => \@combi});
100    }
101
102    # so far only the basic device_port data
103    # now begin to join tables depending on the selected columns/options
104
105    # get vlans on the port
106    # leave this query dormant (lazy) unless c_vmember is set or vlan filtering
107    my $vlans = $set->search({}, {
108      select => [
109        'port',
110        { array_agg => 'port_vlans.vlan', -as => 'vlan_set'   },
111        { count     => 'port_vlans.vlan', -as => 'vlan_count' },
112      ],
113      join => 'port_vlans',
114      group_by => 'me.port',
115    });
116
117    if (param('c_vmember') or ($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) {
118        $vlans = { map {(
119          $_->port => {
120            # DBIC smart enough to work out this should be an arrayref :)
121            vlan_set   => $_->get_column('vlan_set'),
122            vlan_count => $_->get_column('vlan_count'),
123          },
124        )} $vlans->all };
125    }
126
127    # get aggregate master status (self join)
128    $set = $set->search({}, {
129      'join' => 'agg_master',
130      '+select' => [qw/agg_master.up_admin agg_master.up/],
131      '+as'     => [qw/agg_master_up_admin agg_master_up/],
132    });
133
134    # make sure query asks for formatted timestamps when needed
135    $set = $set->with_times if param('c_lastchange');
136
137    # what kind of nodes are we interested in?
138    my $nodes_name = (param('n_archived') ? 'nodes' : 'active_nodes');
139    $nodes_name .= '_with_age' if param('n_age');
140
141    my $ips_name = ((param('n_ip4') and param('n_ip6')) ? 'ips'
142                   : param('n_ip4') ? 'ip4s'
143                   : 'ip6s');
144
145    if (param('c_nodes')) {
146        # retrieve active/all connected nodes, if asked for
147        $set = $set->search({}, { prefetch => [{$nodes_name => $ips_name}] });
148        $set = $set->search({}, { order_by => ["${nodes_name}.vlan", "${nodes_name}.mac", "${ips_name}.ip"] });
149
150        # retrieve wireless SSIDs, if asked for
151        $set = $set->search({}, { prefetch => [{$nodes_name => 'wireless'}] })
152          if param('n_ssid');
153
154        # retrieve NetBIOS, if asked for
155        $set = $set->search({}, { prefetch => [{$nodes_name => 'netbios'}] })
156          if param('n_netbios');
157
158        # retrieve vendor, if asked for
159        $set = $set->search({}, { prefetch => [{$nodes_name => 'oui'}] })
160          if param('n_vendor');
161    }
162
163    # retrieve SSID, if asked for
164    $set = $set->search({}, { prefetch => 'ssid' })
165      if param('c_ssid');
166
167    # retrieve neighbor devices, if asked for
168    #$set = $set->search({}, { prefetch => [{neighbor_alias => 'device'}] })
169    #  if param('c_neighbors');
170    # retrieve neighbor devices, if asked for
171    $set = $set->search({}, {
172      join => 'neighbor_alias',
173      '+select' => ['neighbor_alias.ip', 'neighbor_alias.dns'],
174      '+as'     => ['neighbor_ip', 'neighbor_dns'],
175    }) if param('c_neighbors');
176
177    # also get remote LLDP inventory if asked for
178    $set = $set->with_remote_inventory if param('n_inventory');
179
180    # run query
181    my @results = $set->all;
182
183    # filter for tagged vlan using existing agg query,
184    # which is better than join inflation
185    if (($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) {
186      if (param('invert')) {
187        @results = grep {
188            (!defined $_->vlan or $_->vlan ne $f)
189              and
190            (0 == scalar grep {defined and $_ ne $f} @{ $vlans->{$_->port}->{vlan_set} })
191        } @results;
192      }
193      else {
194        @results = grep {
195            (defined $_->vlan and $_->vlan eq $f)
196              or
197            (scalar grep {defined and $_ eq $f} @{ $vlans->{$_->port}->{vlan_set} })
198        } @results;
199      }
200    }
201
202    # sort ports
203    @results = sort { &App::Netdisco::Util::Web::sort_port($a->port, $b->port) } @results;
204
205    # empty set would be a 'no records' msg
206    return unless scalar @results;
207
208    if (request->is_ajax) {
209        template 'ajax/device/ports.tt', {
210          results => \@results,
211          nodes => $nodes_name,
212          ips   => $ips_name,
213          device => $device,
214          vlans  => $vlans,
215        }, { layout => undef };
216    }
217    else {
218        header( 'Content-Type' => 'text/comma-separated-values' );
219        template 'ajax/device/ports_csv.tt', {
220          results => \@results,
221          nodes => $nodes_name,
222          ips   => $ips_name,
223          device => $device,
224          vlans  => $vlans,
225        }, { layout => undef };
226    }
227};
228
229true;
230