-- #New: 21.05.2018 18:34/J.Fes
-- #LUp: 22.05.2018 13:31/J.Fes Update names punix_path_name and pvms_path_name
-- #DOC: VolInf of Group
-- #DCO: select 'GROUP' 'GroupName' 'Volume', 'VolRoot', 'TopPath', IsDefVol, 'n Files in TopPath'
SELECT
  'GROUP' AS Type,
  pg.pname AS Name,
  vol.pvolume_name AS Volume,
  CASE WHEN vol.pwnt_path_name <> '' THEN vol.pwnt_path_name
       WHEN vol.punix_path_name <> '' THEN vol.punix_path_name
       ELSE vol.pvms_path_name END AS VolPath,
  pu_tp.pval_0 AS TopPath,
  CASE WHEN g.rvolumeu = vol.puid THEN 1 ELSE 0 END IsDefaultVol,
 -- f.pfile_name,
  count(*) AS NFiles
FROM
  infodba.PIMANVOLUME vol
  INNER JOIN infodba.PUSER_PATHS pu_tp ON (vol.puid = pu_tp.puid)
  INNER JOIN infodba.PUSERS pup ON ( (pu_tp.puid = pup.puid) AND (pup.pseq = pu_tp.pseq))
  INNER JOIN infodba.PGROUP g ON (g.puid = pup.pvalu_0)
  INNER JOIN infodba.PPOM_GROUP pg ON (pg.puid = g.puid)
  LEFT JOIN infodba.PIMANFILE f ON (pu_tp.pval_0 = f.psd_path_name)
GROUP BY
  pg.pname,
  vol.pvolume_name,
  CASE WHEN vol.pwnt_path_name <> '' THEN vol.pwnt_path_name
       WHEN vol.punix_path_name <> '' THEN vol.punix_path_name
       ELSE vol.pvms_path_name END,
  pu_tp.pval_0,
  CASE WHEN g.rvolumeu = vol.puid THEN 1 ELSE 0 END
UNION ALL

-- 21.05.2018 18:40/J.Fes
-- #DOC: VolInf of User
-- #DCO: select 'GROUP' 'UserName' 'Volume', 'VolRoot', 'TopPath', IsDefVol, 'n Files in TopPath'
SELECT
  'USER' AS Type,
  pu.pos_username AS Name,
  vol.pvolume_name AS Volume,
  CASE WHEN vol.pwnt_path_name <> '' THEN vol.pwnt_path_name
       WHEN vol.punix_path_name <> '' THEN vol.punix_path_name
       ELSE vol.pvms_path_name END AS VolPath,
  pu_tp.pval_0 AS TopPath,
  CASE WHEN pu.rvolumeu = vol.puid THEN 1 ELSE 0 END IsDefaultVol,
 --  f.pfile_name
 count(*) AS NFiles
FROM
  infodba.PIMANVOLUME vol
  INNER JOIN infodba.PUSER_PATHS pu_tp ON (vol.puid = pu_tp.puid)
  INNER JOIN infodba.PUSERS pup ON ( (pu_tp.puid = pup.puid) AND (pup.pseq = pu_tp.pseq))
  INNER JOIN infodba.PUSER pu ON (pu.puid = pup.pvalu_0)
  LEFT JOIN infodba.PIMANFILE f ON (pu_tp.pval_0 = f.psd_path_name)
Group By
  pu.pos_username,
  vol.pvolume_name,
  CASE WHEN vol.pwnt_path_name <> '' THEN vol.pwnt_path_name
       WHEN vol.punix_path_name <> '' THEN vol.punix_path_name
       ELSE vol.pvms_path_name END,
  pu_tp.pval_0,
  CASE WHEN pu.rvolumeu = vol.puid THEN 1 ELSE 0 END
order by
  Type, Name, TopPath
