Open-ILS/src/sql/Pg/upgrade/0656.LP893315_schema.function.filter_deleted_acns_from_unapi.holdings_xml.sql

   1 -- Evergreen DB patch XXXX.LP893315_schema.function.filter_deleted_acns_from_unapi.holdings_xml.sql
   2 --
   3 -- Prevent deleted call numbers from hiding active call numbers / copies / URIs
   4 --
   5 BEGIN;
   6
   7 -- check whether patch can be applied
   8 SELECT evergreen.upgrade_deps_block_check('0656', :eg_version);
   9
  10 CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$
  11      SELECT  XMLELEMENT(
  12                  name holdings,
  13                  XMLATTRIBUTES(
  14                     CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
  15                     CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
  16                  ),
  17                  XMLELEMENT(
  18                      name counts,
  19                      (SELECT  XMLAGG(XMLELEMENT::XML) FROM (
  20                          SELECT  XMLELEMENT(
  21                                      name count,
  22                                      XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
  23                                  )::text
  24                            FROM  asset.opac_ou_record_copy_count($2,  $1)
  25                                      UNION
  26                          SELECT  XMLELEMENT(
  27                                      name count,
  28                                      XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
  29                                  )::text
  30                            FROM  asset.staff_ou_record_copy_count($2, $1)
  31                                      ORDER BY 1
  32                      )x)
  33                  ),
  34                  CASE
  35                      WHEN ('bmp' = ANY ($5)) THEN
  36                         XMLELEMENT(
  37                             name monograph_parts,
  38                             (SELECT XMLAGG(bmp) FROM (
  39                                 SELECT  unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
  40                                   FROM  biblio.monograph_part
  41                                   WHERE record = $1
  42                             )x)
  43                         )
  44                      ELSE NULL
  45                  END,
  46                  XMLELEMENT(
  47                      name volumes,
  48                      (SELECT XMLAGG(acn) FROM (
  49                         SELECT  unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE)
  50                           FROM  asset.call_number acn
  51                           WHERE acn.record = $1
  52                                 AND acn.deleted IS FALSE
  53                                 AND EXISTS (
  54                                     SELECT  1
  55                                       FROM  asset.copy acp
  56                                             JOIN actor.org_unit_descendants(
  57                                                 $2,
  58                                                 (COALESCE(
  59                                                     $4,
  60                                                     (SELECT aout.depth
  61                                                       FROM  actor.org_unit_type aout
  62                                                             JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2)
  63                                                     )
  64                                                 ))
  65                                             ) aoud ON (acp.circ_lib = aoud.id)
  66                                       LIMIT 1
  67                                )
  68                           ORDER BY label_sortkey
  69                           LIMIT $6
  70                           OFFSET $7
  71                      )x)
  72                  ),
  73                  CASE WHEN ('ssub' = ANY ($5)) THEN
  74                      XMLELEMENT(
  75                          name subscriptions,
  76                          (SELECT XMLAGG(ssub) FROM (
  77                             SELECT  unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
  78                               FROM  serial.subscription
  79                               WHERE record_entry = $1
  80                         )x)
  81                      )
  82                  ELSE NULL END,
  83                  CASE WHEN ('acp' = ANY ($5)) THEN
  84                      XMLELEMENT(
  85                          name foreign_copies,
  86                          (SELECT XMLAGG(acp) FROM (
  87                             SELECT  unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE)
  88                               FROM  biblio.peer_bib_copy_map p
  89                                     JOIN asset.copy c ON (p.target_copy = c.id)
  90                               WHERE NOT c.deleted AND peer_record = $1
  91                         )x)
  92                      )
  93                  ELSE NULL END
  94              );
  95 $F$ LANGUAGE SQL;
  96
  97 COMMIT;