SHARE
TWEET

Org. Unit Open Date Functions

Dyrcona Apr 6th, 2020 (edited) 416 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION cwmars.org_is_open(org INTEGER, thedate DATE)
  2. RETURNS BOOLEAN AS
  3. $$
  4. DECLARE
  5.     dow INTEGER;
  6.     column_open TEXT;
  7.     column_close TEXT;
  8.     isOpen BOOLEAN;
  9. BEGIN
  10.     -- Shortcut to prevent infinite loop in calling functions.
  11.     IF NOT EXISTS(SELECT * FROM actor.hours_of_operation WHERE id = org) THEN
  12.         RETURN TRUE;
  13.     END IF;
  14.     SELECT DATE_PART('DOW', thedate) - 1 INTO dow;
  15.     IF dow = -1 THEN
  16.         dow := 6;
  17.     END IF;
  18.     SELECT 'dow_'||dow||'_open' INTO column_open;
  19.     SELECT 'dow_'||dow||'_close' INTO column_close;
  20.     EXECUTE FORMAT('SELECT TRUE FROM actor.hours_of_operation '
  21.                    'LEFT JOIN actor.org_unit_closed ON org_unit_closed.org_unit = hours_of_operation.id '
  22.                    'AND $1 BETWEEN close_start::DATE AND close_end::DATE '
  23.                    'WHERE %I <> ''00:00:00'' AND %I <> ''00:00:00'' '
  24.                    'AND hours_of_operation.id = $2 '
  25.                    'AND org_unit_closed IS NULL;', column_open, column_close)
  26.     INTO isOpen
  27.     USING thedate, org;
  28.     IF isOpen IS NULL THEN
  29.         isOpen := FALSE;
  30.     END IF;
  31.     RETURN isOpen;
  32. END;
  33. $$
  34. LANGUAGE plpgsql;
  35.  
  36. CREATE OR REPLACE FUNCTION cwmars.next_open_date(org INTEGER, thedate DATE)
  37. RETURNS DATE AS
  38. $$
  39. DECLARE
  40.     condVariable BOOLEAN;
  41. BEGIN
  42.     SELECT cwmars.org_is_open(org, thedate) INTO condVariable;
  43.     WHILE condVariable IS FALSE LOOP
  44.           thedate := thedate + '1 day'::INTERVAL;
  45.           SELECT cwmars.org_is_open(org, thedate) INTO condVariable;
  46.     END LOOP;
  47.     RETURN thedate;
  48. END;
  49. $$
  50. LANGUAGE plpgsql;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top