SELECT S.server_id                         AS server_id,
       S.server_machine_id                 AS server_machine_id,
       S.server_port                       AS server_port,
       S.server_autostart                  AS server_autostart,
       PNAME.value                         AS virtualserver_name,
       PUID.value                          AS virtualserver_unique_identifier,
       WLE.value                           AS weblist_enabled,
       Coalesce(PMAXHB.value, '64')        AS max_homebases,
       Coalesce(HBC.hbcount, 0)            AS homebase_count,
       Coalesce(ccg.guest_chan, 0)         AS guest_can_create_channels,
       Coalesce(cchb.guest_hb, 0)          AS guest_can_create_homebase,
       (SELECT Count(*)
        FROM   clients
        WHERE  homebase_since IS NOT NULL) AS total_hbslots_used
FROM   servers S
       left join server_properties PNAME
              ON S.server_id = PNAME.server_id
                 AND PNAME.ident = 'virtualserver_name'
       left join server_properties PUID
              ON S.server_id = PUID.server_id
                 AND PUID.ident = 'virtualserver_unique_identifier'
       left join server_properties WLE
              ON S.server_id = WLE.server_id
                 AND WLE.ident = 'virtualserver_weblist_enabled'
       left join server_properties PMAXHB
              ON S.server_id = PMAXHB.server_id
                 AND PMAXHB.ident = 'virtualserver_max_homebases'
       left join (SELECT server_id,
                         Count(*) AS hbcount
                  FROM   clients c
                  WHERE  homebase_since IS NOT NULL
                  GROUP  BY server_id) HBC
              ON S.server_id = HBC.server_id
       left join (SELECT DISTINCT server_properties.server_id,
                                  1 AS guest_chan
                  FROM   server_properties
                         left join perm_server_group
                                ON perm_server_group.server_id = server_properties.server_id
                                   AND perm_server_group.id1 = Cast(server_properties.value AS INT)
                  WHERE  server_properties.ident = 'virtualserver_default_server_group'
                         AND perm_server_group.perm_id IN (
                                 'b_channel_create_permanent',
                                 'b_channel_create_semi_permanent',
                                 'b_channel_create_temporary' )
                         AND perm_server_group.perm_negated = 0
                         AND perm_server_group.perm_value > 0) AS ccg
              ON ccg.server_id = S.server_id
       left join (SELECT DISTINCT server_properties.server_id,
                                  1 AS guest_hb
                  FROM   server_properties
                         left join perm_server_group
                                ON perm_server_group.server_id = server_properties.server_id
                                   AND perm_server_group.id1 = Cast(server_properties.value AS INT)
                  WHERE  server_properties.ident = 'virtualserver_default_server_group'
                         AND perm_server_group.perm_id = 'b_virtualserver_homebase_set'
                         AND perm_server_group.perm_negated = 0
                         AND perm_server_group.perm_value > 0) AS cchb
              ON cchb.server_id = S.server_id
WHERE  NOT ( S.server_id = 0 )
ORDER  BY S.server_id; 