1<?php 2/* Icinga Web 2 | (c) 2013 Icinga Development Team | GPLv2+ */ 3 4namespace Icinga\Module\Monitoring\Backend\Ido\Query; 5 6/** 7 * Query for contact groups 8 */ 9class ContactgroupQuery extends IdoQuery 10{ 11 /** 12 * {@inheritdoc} 13 */ 14 protected $allowCustomVars = true; 15 16 /** 17 * {@inheritdoc} 18 */ 19 protected $groupBase = array('contactgroups' => array('cg.contactgroup_id', 'cgo.object_id')); 20 21 /** 22 * {@inheritdoc} 23 */ 24 protected $groupOrigin = array('hosts', 'members', 'services'); 25 26 protected $subQueryTargets = array( 27 'hostgroups' => 'hostgroup', 28 'servicegroups' => 'servicegroup' 29 ); 30 31 /** 32 * {@inheritdoc} 33 */ 34 protected $columnMap = array( 35 'contactgroups' => array( 36 'contactgroup' => 'cgo.name1 COLLATE latin1_general_ci', 37 'contactgroup_name' => 'cgo.name1', 38 'contactgroup_alias' => 'cg.alias COLLATE latin1_general_ci' 39 ), 40 'members' => array( 41 'contact_count' => 'SUM(CASE WHEN cgmo.object_id IS NOT NULL THEN 1 ELSE 0 END)' 42 ), 43 'hostgroups' => array( 44 'hostgroup' => 'hgo.name1 COLLATE latin1_general_ci', 45 'hostgroup_alias' => 'hg.alias COLLATE latin1_general_ci', 46 'hostgroup_name' => 'hgo.name1' 47 ), 48 'hosts' => array( 49 'host' => 'ho.name1 COLLATE latin1_general_ci', 50 'host_name' => 'ho.name1', 51 'host_alias' => 'h.alias', 52 'host_display_name' => 'h.display_name COLLATE latin1_general_ci' 53 ), 54 'instances' => array( 55 'instance_name' => 'i.instance_name' 56 ), 57 'servicegroups' => array( 58 'servicegroup' => 'sgo.name1 COLLATE latin1_general_ci', 59 'servicegroup_name' => 'sgo.name1', 60 'servicegroup_alias' => 'sg.alias COLLATE latin1_general_ci' 61 ), 62 'services' => array( 63 'service' => 'so.name2 COLLATE latin1_general_ci', 64 'service_description' => 'so.name2', 65 'service_display_name' => 's.display_name COLLATE latin1_general_ci', 66 'service_host_name' => 'so.name1' 67 ) 68 ); 69 70 /** 71 * {@inheritdoc} 72 */ 73 protected function joinBaseTables() 74 { 75 $this->select->from( 76 array('cg' => $this->prefix . 'contactgroups'), 77 array() 78 )->join( 79 array('cgo' => $this->prefix . 'objects'), 80 'cgo.object_id = cg.contactgroup_object_id AND cgo.is_active = 1 AND cgo.objecttype_id = 11', 81 array() 82 ); 83 $this->joinedVirtualTables['contactgroups'] = true; 84 } 85 86 /** 87 * Join contact group members 88 */ 89 protected function joinMembers() 90 { 91 $this->select->joinLeft( 92 array('cgm' => $this->prefix . 'contactgroup_members'), 93 'cgm.contactgroup_id = cg.contactgroup_id', 94 array() 95 )->joinLeft( 96 array('cgmo' => $this->prefix . 'objects'), 97 'cgmo.object_id = cgm.contact_object_id AND cgmo.is_active = 1 AND cgmo.objecttype_id = 10', 98 array() 99 ); 100 } 101 102 /** 103 * Join host groups 104 */ 105 protected function joinHostgroups() 106 { 107 $this->requireVirtualTable('hosts'); 108 $this->select->joinLeft( 109 array('hgm' => $this->prefix . 'hostgroup_members'), 110 'hgm.host_object_id = ho.object_id', 111 array() 112 )->joinLeft( 113 array('hg' => $this->prefix . 'hostgroups'), 114 'hg.hostgroup_id = hgm.hostgroup_id', 115 array() 116 )->joinLeft( 117 array('hgo' => $this->prefix . 'objects'), 118 'hgo.object_id = hg.hostgroup_object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3', 119 array() 120 ); 121 } 122 123 /** 124 * Join hosts 125 */ 126 protected function joinHosts() 127 { 128 $this->select->joinLeft( 129 array('hcg' => $this->prefix . 'host_contactgroups'), 130 'hcg.contactgroup_object_id = cg.contactgroup_object_id', 131 array() 132 )->joinLeft( 133 array('h' => $this->prefix . 'hosts'), 134 'h.host_id = hcg.host_id', 135 array() 136 )->joinLeft( 137 array('ho' => $this->prefix . 'objects'), 138 'ho.object_id = h.host_object_id AND ho.is_active = 1 AND ho.objecttype_id = 1', 139 array() 140 ); 141 } 142 143 /** 144 * Join instances 145 */ 146 protected function joinInstances() 147 { 148 $this->select->join( 149 array('i' => $this->prefix . 'instances'), 150 'i.instance_id = cg.instance_id', 151 array() 152 ); 153 } 154 155 /** 156 * Join service groups 157 */ 158 protected function joinServicegroups() 159 { 160 $this->requireVirtualTable('services'); 161 $this->select->joinLeft( 162 array('sgm' => $this->prefix . 'servicegroup_members'), 163 'sgm.service_object_id = s.service_object_id', 164 array() 165 )->joinLeft( 166 array('sg' => $this->prefix . 'servicegroups'), 167 'sg.servicegroup_id = sgm.servicegroup_id', 168 array() 169 )->joinLeft( 170 array('sgo' => $this->prefix . 'objects'), 171 'sgo.object_id = sg.servicegroup_object_id AND sgo.is_active = 1 AND sgo.objecttype_id = 4', 172 array() 173 ); 174 } 175 176 /** 177 * Join services 178 */ 179 protected function joinServices() 180 { 181 $this->select->joinLeft( 182 array('scg' => $this->prefix . 'service_contactgroups'), 183 'scg.contactgroup_object_id = cg.contactgroup_object_id', 184 array() 185 )->joinLeft( 186 array('s' => $this->prefix . 'services'), 187 's.service_id = scg.service_id', 188 array() 189 )->joinLeft( 190 array('so' => $this->prefix . 'objects'), 191 'so.object_id = s.service_object_id AND so.is_active = 1 AND so.objecttype_id = 2', 192 array() 193 ); 194 } 195 196 protected function joinSubQuery(IdoQuery $query, $name, $filter, $and, $negate, &$additionalFilter) 197 { 198 if ($name === 'hostgroup') { 199 $this->requireVirtualTable('hosts'); 200 201 $query->joinVirtualTable('members'); 202 203 return ['hgm.host_object_id', 'ho.object_id']; 204 } elseif ($name === 'servicegroup') { 205 $this->requireVirtualTable('services'); 206 207 $query->joinVirtualTable('members'); 208 209 return ['sgm.service_object_id', 'so.object_id']; 210 } 211 212 return parent::joinSubQuery($query, $name, $filter, $and, $negate, $additionalFilter); 213 } 214} 215