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