
BEGIN;

-- Get extension to redirect to, if redirect is active for inbound
-- the second arg is new, and is the current time expressed in the timezone for the schedule.
-- We let the caller figure what that is, which seems easier than trying to do it here.
CREATE or REPLACE FUNCTION mustredirect(integer,time without time zone) RETURNS varchar AS
'select soundfile.name || ''#'' || pl1.extension || ''#'' || pl2.extension || ''#'' || pl3.extension
   from inbound left join inbound_schedule on inbound_schedule.inbound = inbound.id
                left join schedule on schedule.id = inbound_schedule.schedule
                left join phoneline as pl1 on pl1.id = schedule.phoneline1
                left join phoneline as pl2 on pl2.id = schedule.phoneline2
                left join phoneline as pl3 on pl3.id = schedule.phoneline3
                left join soundfile on soundfile.id = schedule.greeting

-- first, define basic relationship between the tables we are selecting from.

  where (inbound.redirect_on = \'yes\' and inbound.id = $1)

-- now every following AND statement handles one set of fields from the schedule table, each.
-- * starttime & endtime
--   If starttime or endtime is null, then they dont limit the schedule timewise.
--   There is a difference between the case where starttime is before endtime, compared with the other way around.
--   If starttime is before endtime, the current time should be in between both.
--   If endtime is before starttime, the current time should be either after starttime or before endtime.

    and ((starttime is not null and endtime is not null and
          starttime < endtime and $2 > starttime and $2 < endtime) or
         (starttime is not null and endtime is not null and
          starttime > endtime and ($2 > starttime or $2 < endtime)) or
         (starttime is null and endtime is not null and $2 < endtime) or
         (starttime is not null and endtime is null and $2 > starttime))

-- * atdate & enddate

    and (atdate is null or current_date >= atdate)
    and (enddate is null or current_date <= enddate)

-- * interval
--   is one of the following:
--   * none

    and ((interval = \'none\') or

--   * day
--     when the schedule is repeated daily, the every field defines that it is for example every 3 days.
--     it is calculated as follows: atdate is substracted from currentdate. That gives the number of days
--     between then and now. For some reason, this result needs to be cast to the integer type.
--     Then using the % (modulo) operator, we check wether this number of days is divisible by every.
--     In case every is 0, this leads to division by zero. To prevent that, we say that every needs to be not 0
--     before doing the actual calculation.

         (interval = \'day\'  and
          ((every = 0) or
           (every != 0 and (cast((current_date - atdate) as integer) % every) = 0))) or

--   * week
--     same idea as with interval = day.
--     Except, this time we also need to check if the database field value for the current day is set to yes.
--     For example: if today is friday, then date_part(dow, current_date) needs to be 5, and friday set to yes.

         (interval = \'week\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'week\', current_date) - date_part(\'week\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(\'dow\', current_date) = 0 and sunday    = \'yes\') or
           (date_part(\'dow\', current_date) = 1 and monday    = \'yes\') or
           (date_part(\'dow\', current_date) = 2 and tuesday   = \'yes\') or
           (date_part(\'dow\', current_date) = 3 and wednesday = \'yes\') or
           (date_part(\'dow\', current_date) = 4 and thursday  = \'yes\') or
           (date_part(\'dow\', current_date) = 5 and friday    = \'yes\') or
           (date_part(\'dow\', current_date) = 6 and saturday  = \'yes\')
          )
         ) or

--   * month
--     Only for interval=month, the repeat_by field is used. This field is either date or day.
--     If the value is date, then the schedule repeats monthly by date - for example, every 18th of the month.
--     If the value is day, then the schedule repeats by weekday of the month - for example, every second tuesday.

         (interval = \'month\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'month\', current_date) - date_part(\'month\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (repeat_by = \'date\' and (date_part(\'day\', current_date)) = (date_part(\'day\', atdate))) or
           (
            repeat_by = \'day\'  and (date_part(\'dow\', current_date)) = (date_part(\'dow\', atdate)) and

--     Say it is monday 14. This is the second monday of the month - the 7th is the first monday.
--     One day later it is tuesday 15. This is the third tuesday of the month - the 1st is the first tuesday.
--     In one day, the number /weekday of the month/ goes from 2 to 3.
--     Because weekdays such as tuesday repeat every 7 days, we divide the day of the month by 7.
--     14 / 7 = 2.
--     15 / 7 = 2.14
--     13 / 7 = 1.86 - appearently, the jump from 1 to 2 does not happen from the 14th to the 15th, but
--                     from the 13th to the 14th.
--     So, before dividing by 7, we need to substract 1. Then the floor() function rounds it down to an integer.
--     Note that this doesnt give the actual weekday of the month; in order to get that, we need to add 1 to
--     the result. But for comparing 2 dates, that isnt neccessary.

            floor((date_part(\'day\', atdate) -1) / 7) = floor((date_part(\'day\', current_date) -1) / 7)
           )
          )
         ) or

--   * year

         (interval = \'year\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'year\', current_date) - date_part(\'year\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(\'day\', current_date) = date_part(\'day\', atdate)) and
           (date_part(\'month\', current_date) = date_part(\'month\', atdate))
          )
         )

-- Take priorities into account, only select the one with highest priority.

        ) order by priority limit 1;
' LANGUAGE SQL;


-- Get extension to redirect to, if redirect is active for phoneline
-- the second arg is new, and is the current time expressed in the timezone for the schedule.
-- We let the caller figure what that is, which seems easier than trying to do it here.
CREATE or REPLACE FUNCTION activeschedule(integer, time without time zone) RETURNS varchar AS
'select soundfile.name || ''#'' || pl1.extension || ''#'' || pl2.extension || ''#'' || pl3.extension
   from phoneline left join phoneline_schedule on phoneline_schedule.phoneline = phoneline.id
                  left join schedule on schedule.id = phoneline_schedule.schedule
                  left join phoneline as pl1 on pl1.id = schedule.phoneline1
                  left join phoneline as pl2 on pl2.id = schedule.phoneline2
                  left join phoneline as pl3 on pl3.id = schedule.phoneline3
                  left join soundfile on soundfile.id = schedule.greeting

-- first, define basic relationship between the tables we are selecting from.

  where (phoneline.redirect_on = \'yes\' and phoneline.id = $1)

-- now every following AND statement handles one set of fields from the schedule table, each.
-- * starttime & endtime
--   If starttime or endtime is null, then they dont limit the schedule timewise.
--   There is a difference between the case where starttime is before endtime, compared with the other way around.
--   If starttime is before endtime, the current time should be in between both.
--   If endtime is before starttime, the current time should be either after starttime or before endtime.

    and ((starttime is not null and endtime is not null and
          starttime < endtime and $2 > starttime and $2 < endtime) or
         (starttime is not null and endtime is not null and
          starttime > endtime and ($2 > starttime or $2 < endtime)) or
         (starttime is null and endtime is not null and $2 < endtime) or
         (starttime is not null and endtime is null and $2 > starttime))

-- * atdate & enddate

    and (atdate is null or current_date >= atdate)
    and (enddate is null or current_date <= enddate)

-- * interval
--   is one of the following:
--   * none

    and ((interval = \'none\') or

--   * day
--     when the schedule is repeated daily, the every field defines that it is for example every 3 days.
--     it is calculated as follows: atdate is substracted from currentdate. That gives the number of days
--     between then and now. For some reason, this result needs to be cast to the integer type.
--     Then using the % (modulo) operator, we check wether this number of days is divisible by every.
--     In case every is 0, this leads to division by zero. To prevent that, we say that every needs to be not 0
--     before doing the actual calculation.

         (interval = \'day\'  and
          ((every = 0) or
           (every != 0 and (cast((current_date - atdate) as integer) % every) = 0))) or

--   * week
--     same idea as with interval = day.
--     Except, this time we also need to check if the database field value for the current day is set to yes.
--     For example: if today is friday, then date_part(dow, current_date) needs to be 5, and friday set to yes.

         (interval = \'week\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'week\', current_date) - date_part(\'week\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(\'dow\', current_date) = 0 and sunday    = \'yes\') or
           (date_part(\'dow\', current_date) = 1 and monday    = \'yes\') or
           (date_part(\'dow\', current_date) = 2 and tuesday   = \'yes\') or
           (date_part(\'dow\', current_date) = 3 and wednesday = \'yes\') or
           (date_part(\'dow\', current_date) = 4 and thursday  = \'yes\') or
           (date_part(\'dow\', current_date) = 5 and friday    = \'yes\') or
           (date_part(\'dow\', current_date) = 6 and saturday  = \'yes\')
          )
         ) or

--   * month
--     Only for interval=month, the repeat_by field is used. This field is either date or day.
--     If the value is date, then the schedule repeats monthly by date - for example, every 18th of the month.
--     If the value is day, then the schedule repeats by weekday of the month - for example, every second tuesday.

         (interval = \'month\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'month\', current_date) - date_part(\'month\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (repeat_by = \'date\' and (date_part(\'day\', current_date)) = (date_part(\'day\', atdate))) or
           (
            repeat_by = \'day\'  and (date_part(\'dow\', current_date)) = (date_part(\'dow\', atdate)) and

--     Say it is monday 14. This is the second monday of the month - the 7th is the first monday.
--     One day later it is tuesday 15. This is the third tuesday of the month - the 1st is the first tuesday.
--     In one day, the number /weekday of the month/ goes from 2 to 3.
--     Because weekdays such as tuesday repeat every 7 days, we divide the day of the month by 7.
--     14 / 7 = 2.
--     15 / 7 = 2.14
--     13 / 7 = 1.86 - appearently, the jump from 1 to 2 does not happen from the 14th to the 15th, but
--                     from the 13th to the 14th.
--     So, before dividing by 7, we need to substract 1. Then the floor() function rounds it down to an integer.
--     Note that this doesnt give the actual weekday of the month; in order to get that, we need to add 1 to
--     the result. But for comparing 2 dates, that isnt neccessary.

            floor((date_part(\'day\', atdate) -1) / 7) = floor((date_part(\'day\', current_date) -1) / 7)
           )
          )
         ) or

--   * year

         (interval = \'year\' and
          ((every = 0) or
           (every != 0 and
            (cast((date_part(\'year\', current_date) - date_part(\'year\', atdate)) as integer) % every = 0)
           )
          ) and
          (
           (date_part(\'day\', current_date) = date_part(\'day\', atdate)) and
           (date_part(\'month\', current_date) = date_part(\'month\', atdate))
          )
         )

-- Take priorities into account, only select the one with highest priority.

        ) order by priority limit 1;
' LANGUAGE SQL;

COMMIT;
