[RM-32250]: <MU DSpace> ตรวจสอบและแก้ไขให้ จำนวน view and download ในระบบ dspace แสดงผลให้ถูกต้อง (ตรงกันกับระบบรายงาน)

Solution / Workaround

SELECT
  CONCAT('<a href="https://repository.li.mahidol.ac.th/items/',i.uuid,'" target="_blank">',h.handle,'</a>') AS handle,
  (
    SELECT
      STRING_AGG(text_value, ', ')
    FROM
      metadatavalue
    WHERE
      metadata_field_id = '64'
      AND dspace_object_id = i.uuid
  ) AS title,
  (
    SELECT
      STRING_AGG(text_value, ', ')
    FROM
      metadatavalue
    WHERE
      metadata_field_id = '64'
      AND dspace_object_id = i.owning_collection
  ) AS colname,
  (
    SELECT
      STRING_AGG(text_value, ', ')
    FROM
      metadatavalue
    WHERE
      metadata_field_id = '64'
      AND dspace_object_id = com2col.community_id
  ) AS comname,
  count(*) as Download
FROM
  statistics s
  LEFT JOIN bundle2bitstream b2b ON CAST(b2b.bitstream_id AS TEXT) = s.id
  LEFT JOIN item2bundle i2b ON i2b.bundle_id = b2b.bundle_id
  LEFT JOIN item i ON i.uuid = i2b.item_id
  LEFT JOIN community2collection com2col ON com2col.collection_id = i.owning_collection
  LEFT JOIN handle h ON h.resource_id = i.uuid
  LEFT JOIN (
    select
      dspace_object_id
    from
      metadatavalue
      left join metadatafieldregistry USING(metadata_field_id)
      left join metadataschemaregistry using(metadata_schema_id)
    where
      metadata_field_id = 64
      AND text_value = 'ORIGINAL'
  ) bo ON bo.dspace_object_id = b2b.bundle_id
WHERE
  s.type = '0'
  AND s.data ->> 'isBot' = 'false'
--   AND i.uuid = '13c117c5-ead7-47ee-bec5-8cd3245cbde8'
  AND bo.dspace_object_id IS NOT NULL
  AND i.discoverable IS true
  AND s.time BETWEEN '{{ date.start }}' AND '{{ date.end }}'
GROUP BY
  1,
  2,
  3,
  4
ORDER BY COUNT(*) DESC

Verification Steps

รายการข้อมูลที่มีการดาวน์โหลดสูงสุด (Top 30 downloads) ตามช่วงเวลาที่กำหนด (v2)