-- #New: 21.05.2018/J.Fes
-- #LUp: 22.05.2018/J.Fes Update names punix_path_name and pvms_path_name
-- #LUp: 19.06.2024/J.Fes get TCUserID
-- #Doc: VolInf of Group
-- #Doc: 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.pmachine_type = 0 THEN vol.pwnt_path_name
       WHEN vol.pmachine_type = 2 THEN vol.punix_path_name
       ELSE 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.pmachine_type = 0 THEN vol.pwnt_path_name
       WHEN vol.pmachine_type = 2 THEN vol.punix_path_name
       ELSE 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,
  -- #LUP 19.06.2024/J.Fes get TC User ID
  ppu.puser_id AS Name,
  vol.pvolume_name AS Volume,
  CASE WHEN vol.pmachine_type = 0 THEN vol.pwnt_path_name
       WHEN vol.pmachine_type = 2 THEN vol.punix_path_name
       ELSE 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)
  -- #New: 19.06.2024/J.Fes
  INNER JOIN infodba.PPOM_USER ppu ON (pu.puid = ppu.puid)
  LEFT JOIN infodba.PIMANFILE f ON (pu_tp.pval_0 = f.psd_path_name)
GROUP BY
  ppu.puser_id,
  vol.pvolume_name,
  CASE WHEN vol.pmachine_type = 0 THEN vol.pwnt_path_name
       WHEN vol.pmachine_type = 2 THEN vol.punix_path_name
       ELSE pvms_path_name END,
  pu_tp.pval_0,
  CASE WHEN pu.rvolumeu = vol.puid THEN 1 ELSE 0 END
ORDER BY
  Type, Name, TopPath

