Advertisement
Guest User

Untitled

a guest
Mar 16th, 2022
41
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.76 KB | None | 0 0
  1. SELECT  
  2.  "ahopl".capture_time,
  3.  "ahopl".current_copy,
  4.  "ahopl".email_notify,
  5.  "ahopl".expire_time,
  6.  "ahopl".fulfillment_lib,
  7.  "ahopl".fulfillment_staff,
  8.  "ahopl".fulfillment_time,
  9.  "ahopl".hold_type,
  10.  "ahopl".holdable_formats,
  11.  "ahopl".id,
  12.  "ahopl".phone_notify,
  13.  "ahopl".sms_notify,
  14.  "ahopl".sms_carrier,
  15.  "ahopl".pickup_lib,
  16.  "ahopl".prev_check_time,
  17.  "ahopl".request_lib,
  18.  "ahopl".request_time,
  19.  "ahopl".requestor,
  20.  "ahopl".selection_depth,
  21.  "ahopl".selection_ou,
  22.  "ahopl".target,
  23.  "ahopl".usr,
  24.  "ahopl".cancel_time,
  25.  "ahopl".frozen,
  26.  "ahopl".thaw_date,
  27.  "ahopl".shelf_time,
  28.  "ahopl".cancel_cause,
  29.  "ahopl".cancel_note,
  30.  "ahopl".cut_in_line,
  31.  "ahopl".mint_condition,
  32.  "ahopl".shelf_expire_time,
  33.  "ahopl".current_shelf_lib,
  34.  "ahopl".acq_request,
  35.  "ahopl".copy_location_order_position,
  36.  "ahopl".usr_first_given_name,
  37.  "ahopl".usr_second_given_name,
  38.  "ahopl".usr_family_name,
  39.  "ahopl".usr_prefix,
  40.  "ahopl".usr_suffix,
  41.  "ahopl".usr_alias_or_first_given_name,
  42.  "ahopl".usr_display_name,
  43.  "ahopl".usr_alias_or_display_name,
  44.  "ahopl".usr_alias,
  45.  "ahopl".call_number_label,
  46.  "ahopl".issuance_label,
  47.  "ahopl".is_staff_hold,
  48.  "ahopl".potential_copies,
  49.  "ahopl".behind_desk,
  50.  "ahopl".hopeless_date
  51.  FROM (
  52.                         SELECT DISTINCT
  53.                                 ahr.*,
  54.                                 COALESCE(acplo.POSITION, acpl_ordered.fallback_position) AS
  55.                                         copy_location_order_position,
  56.                                 CASE WHEN au.alias IS NOT NULL THEN
  57.                                         au.alias
  58.                                 ELSE
  59.                                         au.first_given_name
  60.                                 END AS usr_alias_or_first_given_name,
  61.                                 au.first_given_name AS usr_first_given_name,
  62.                                 au.second_given_name AS usr_second_given_name,
  63.                                 au.family_name AS usr_family_name,
  64.                                 au.prefix AS usr_prefix,
  65.                                 au.suffix AS usr_suffix,
  66.                                 au.alias AS usr_alias,
  67.                                 CASE WHEN au.alias IS NOT NULL THEN
  68.                                         au.alias
  69.                                 ELSE
  70.                                         REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
  71.                                                 COALESCE(au.family_name, ''),
  72.                                                 COALESCE(au.suffix, ''),
  73.                                                 ', ',
  74.                                                 COALESCE(au.prefix, ''),
  75.                                                 COALESCE(au.first_given_name, ''),
  76.                                                 COALESCE(au.second_given_name, '')
  77.                                         ], ' '), E'\\s+,', ',')
  78.                                 END AS usr_alias_or_display_name,
  79.                                 REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
  80.                                         COALESCE(au.family_name, ''),
  81.                                         COALESCE(au.suffix, ''),
  82.                                         ', ',
  83.                                         COALESCE(au.prefix, ''),
  84.                                         COALESCE(au.first_given_name, ''),
  85.                                         COALESCE(au.second_given_name, '')
  86.                                 ], ' '), E'\\s+,', ',') AS usr_display_name,
  87.                                 TRIM(acnp.label || ' ' || acn.label || ' ' || acns.label)
  88.                                         AS call_number_label,
  89.                                 siss.label AS issuance_label,
  90.                                 (ahr.usr <> ahr.requestor) AS is_staff_hold,
  91.                                 ahcm_1.copy_count AS potential_copies
  92.                         FROM action.hold_request ahr
  93.                         JOIN asset.copy acp ON (acp.id = ahr.current_copy)
  94.                         JOIN asset.call_number acn ON (acp.call_number = acn.id)
  95.                         JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
  96.                         JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
  97.                         JOIN actor.usr au ON (au.id = ahr.usr)
  98.                         JOIN (
  99.                                 SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
  100.                                 FROM asset.copy_location
  101.                         ) acpl_ordered ON (acpl_ordered.id = acp.location)
  102.                         LEFT JOIN actor.usr_standing_penalty ausp
  103.                                 ON (ahr.usr = ausp.usr AND (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
  104.                         LEFT JOIN config.standing_penalty csp
  105.                                 ON (
  106.                                         csp.id = ausp.standing_penalty AND
  107.                                         csp.block_list LIKE '%CAPTURE%' AND (
  108.                                                 (csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
  109.                                                 (csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
  110.                                                         SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
  111.                                                 )
  112.                                         )
  113.                                 )
  114.                         JOIN (
  115.                                 SELECT COUNT(target_copy) AS copy_count, hold
  116.                                 FROM action.hold_copy_map
  117.                                 GROUP BY 2
  118.                         ) ahcm_1 ON (ahcm_1.hold = ahr.id)
  119.                         LEFT JOIN serial.issuance siss
  120.                                 ON (ahr.hold_type = 'I' AND siss.id = ahr.target)
  121.                         LEFT JOIN asset.copy_location_order acplo
  122.                                 ON (acp.location = acplo.location AND
  123.                                         acp.circ_lib = acplo.org)
  124.                         WHERE
  125.                                 ahr.capture_time IS NULL AND
  126.                                 ahr.cancel_time IS NULL AND
  127.                                 csp.id IS NULL AND
  128.                                 (ahr.expire_time IS NULL OR ahr.expire_time > NOW()) AND
  129.                                 acp.STATUS IN (0,7)
  130.                         ) AS "ahopl"  
  131. LEFT JOIN action.hold_request_note AS "__ahrn_1" ON ( "__ahrn_1".hold = "ahopl".id )  
  132. LEFT JOIN actor.org_unit AS "__aou_1" ON ( "__aou_1".id = "ahopl".request_lib )  
  133. LEFT JOIN actor.org_unit AS "__aou_2" ON ( "__aou_2".id = "ahopl".pickup_lib )  
  134. LEFT JOIN actor.usr AS "__au_1" ON ( "__au_1".id = "ahopl".usr )  
  135. LEFT JOIN config.sms_carrier AS "__csc_1" ON ( "__csc_1".id = "ahopl".sms_carrier )  
  136. LEFT JOIN actor.org_unit AS "__aou_3" ON ( "__aou_3".id = "ahopl".selection_ou )  
  137. LEFT JOIN asset.copy AS "__acp_1" ON ( "__acp_1".id = "ahopl".current_copy )  
  138. LEFT JOIN asset.call_number AS "__acn_1" ON ( "__acn_1".id = "__acp_1".call_number )  
  139. LEFT JOIN asset.call_number_prefix AS "__acnp_1" ON ( "__acnp_1".id = "__acn_1".prefix )  
  140. LEFT JOIN asset.call_number_suffix AS "__acns_1" ON ( "__acns_1".id = "__acn_1".suffix )  
  141. LEFT JOIN biblio.record_entry AS "__bre_1" ON ( "__bre_1".id = "__acn_1".record )  
  142. LEFT JOIN reporter.materialized_simple_record AS "__rmsr_1" ON ( "__rmsr_1".id = "__bre_1".id )  
  143. LEFT JOIN config.copy_status AS "__ccs_1" ON ( "__ccs_1".id = "__acp_1".STATUS )  
  144. LEFT JOIN asset.copy_location AS "__acpl_1" ON ( "__acpl_1".id = "__acp_1".location )  
  145. LEFT JOIN actor.org_unit AS "__aou_4" ON ( "__aou_4".id = "__acp_1".circ_lib )
  146. WHERE ( "__aou_4".id = '298' )
  147. ORDER BY "__acpl_1".name ASC, "__acn_1".label_sortkey ASC LIMIT 25 OFFSET 0;
  148.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement