1package App::Netdisco::DB::ResultSet::DevicePower; 2use base 'App::Netdisco::DB::ResultSet'; 3 4use strict; 5use warnings; 6 7=head1 ADDITIONAL METHODS 8 9 10=head2 with_poestats 11 12This is a modifier for any C<search()> which will add the following 13additional synthesized columns to the result set: 14 15=over 4 16 17=item poe_capable_ports 18 19Count of ports which have the ability to supply PoE. 20 21=item poe_powered_ports 22 23Count of ports with PoE administratively disabled. 24 25=item poe_disabled_ports 26 27Count of ports which are delivering power. 28 29=item poe_errored_ports 30 31Count of ports either reporting a fault or in test mode. 32 33=item poe_power_committed 34 35Total power that has been negotiated and therefore committed on ports 36actively supplying power. 37 38=item poe_power_delivering 39 40Total power as measured on ports actively supplying power. 41 42=back 43 44=cut 45 46sub with_poestats { 47 my ($rs, $cond, $attrs) = @_; 48 49 return $rs 50 ->search_rs($cond, $attrs) 51 ->search({}, 52 { 53 'columns' => { 54 ip => \"DISTINCT ON (me.ip, me.module) me.ip", 55 module => 'module', 56 power => 'power::bigint', 57 status => 'status', 58 poe_capable_ports => \"COUNT(ports.port) OVER (PARTITION BY me.ip, me.module)", 59 poe_powered_ports => \"SUM(CASE WHEN ports.status = 'deliveringPower' THEN 1 ELSE 0 END) OVER (PARTITION BY me.ip, me.module)", 60 poe_disabled_ports => \"SUM(CASE WHEN ports.admin = 'false' THEN 1 ELSE 0 END) OVER (PARTITION BY me.ip, me.module)", 61 poe_errored_ports => \"SUM(CASE WHEN ports.status ILIKE '%fault' THEN 1 ELSE 0 END) OVER (PARTITION BY me.ip, me.module)", 62 poe_power_committed => \("SUM(CASE " 63 . "WHEN ports.status = 'deliveringPower' AND ports.class = 'class0' THEN 15.4 " 64 . "WHEN ports.status = 'deliveringPower' AND ports.class = 'class1' THEN 4.0 " 65 . "WHEN ports.status = 'deliveringPower' AND ports.class = 'class2' THEN 7.0 " 66 . "WHEN ports.status = 'deliveringPower' AND ports.class = 'class3' THEN 15.4 " 67 . "WHEN ports.status = 'deliveringPower' AND ports.class = 'class4' THEN 30.0 " 68 . "WHEN ports.status = 'deliveringPower' AND ports.class IS NULL THEN 15.4 " 69 . "ELSE 0 END) OVER (PARTITION BY me.ip, me.module)"), 70 poe_power_delivering => \("SUM(CASE WHEN (ports.power IS NULL OR ports.power = '0') " 71 . "THEN 0 ELSE round(ports.power/1000.0, 1) END) " 72 . "OVER (PARTITION BY me.ip, me.module)") 73 }, 74 join => 'ports' 75 }); 76} 77 781; 79