1package App::Netdisco::DB::Result::Virtual::DevicePoeStatus; 2 3use strict; 4use warnings; 5 6use base 'DBIx::Class::Core'; 7 8__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 9 10__PACKAGE__->table('device_poe_status'); 11__PACKAGE__->result_source_instance->is_virtual(1); 12__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL'); 13SELECT DISTINCT ON (dp.ip,dp.module) 14 dp.ip, 15 dp.module, 16 dp.power::bigint, 17 dp.status, 18 d.dns, 19 d.name, 20 d.model, 21 d.location, 22 COUNT(dpp.port) OVER (PARTITION BY dp.ip, dp.module) AS poe_capable_ports, 23 SUM(CASE WHEN dpp.status = 'deliveringPower' THEN 1 ELSE 0 END) OVER (PARTITION BY dp.ip, dp.module) AS poe_powered_ports, 24 SUM(CASE WHEN dpp.admin = 'false' THEN 1 ELSE 0 END) OVER (PARTITION BY dp.ip, dp.module) AS poe_disabled_ports, 25 SUM(CASE WHEN dpp.status ILIKE '%fault' THEN 1 26 ELSE 0 END) OVER (PARTITION BY dp.ip, dp.module) AS poe_errored_ports, 27 SUM(CASE WHEN dpp.status = 'deliveringPower' AND dpp.class = 'class4' THEN 30.0 28 WHEN dpp.status = 'deliveringPower' AND dpp.class = 'class2' THEN 7.0 29 WHEN dpp.status = 'deliveringPower' AND dpp.class = 'class1' THEN 4.0 30 WHEN dpp.status = 'deliveringPower' AND dpp.class = 'class3' THEN 15.4 31 WHEN dpp.status = 'deliveringPower' AND dpp.class = 'class0' THEN 15.4 32 WHEN dpp.status = 'deliveringPower' AND dpp.class IS NULL THEN 15.4 33 ELSE 0 END) OVER (PARTITION BY dp.ip, dp.module) AS poe_power_committed, 34 SUM(CASE WHEN (dpp.power IS NULL OR dpp.power = '0') THEN 0 35 ELSE round(dpp.power/1000.0, 1) END) OVER (PARTITION BY dp.ip, dp.module) AS poe_power_delivering 36FROM device_power dp 37JOIN device_port_power dpp ON dpp.ip = dp.ip 38AND dpp.module = dp.module 39JOIN device d ON dp.ip = d.ip 40ENDSQL 41 42__PACKAGE__->add_columns( 43 'ip' => { 44 data_type => 'inet', 45 }, 46 'module' => { 47 data_type => 'integer', 48 }, 49 'power' => { 50 data_type => 'integer', 51 }, 52 'status' => { 53 data_type => 'text', 54 }, 55 'dns' => { 56 data_type => 'text', 57 }, 58 'name' => { 59 data_type => 'text', 60 }, 61 'model' => { 62 data_type => 'text', 63 }, 64 'location' => { 65 data_type => 'text', 66 }, 67 'poe_capable_ports' => { 68 data_type => 'bigint', 69 }, 70 'poe_powered_ports' => { 71 data_type => 'bigint', 72 }, 73 'poe_disabled_ports' => { 74 data_type => 'bigint', 75 }, 76 'poe_errored_ports' => { 77 data_type => 'bigint', 78 }, 79 'poe_power_committed' => { 80 data_type => 'numeric', 81 }, 82 'poe_power_delivering' => { 83 data_type => 'numeric', 84 }, 85); 86 871; 88