BEGIN;

-- get next free extension for conference box within acceptable range
-- takes department.id
CREATE or REPLACE FUNCTION next_conf(integer) RETURNS integer AS '
   DECLARE
       company_id integer;
       from_nr    integer;
       to_nr      integer;
       next_ext   integer;
       ext_len    integer;
   BEGIN
       SELECT INTO company_id company FROM department WHERE id = $1;
       SELECT INTO ext_len extlen FROM company WHERE id = company_id;
       SELECT INTO from_nr from_extension FROM extensionrange WHERE function=''conference'' AND company=company_id;
       SELECT INTO to_nr to_extension FROM extensionrange WHERE function=''conference'' AND company=company_id;

       IF NOT from_nr IS NULL AND NOT to_nr IS NULL THEN
         SELECT INTO next_ext min(p1.extension::text::integer) + 1
           FROM phoneline AS p1 left outer join phoneline AS p2 ON (p1.extension::text::integer + 1 = CAST (p2.extension AS int)
                                                                    AND p1.company=p2.company)
                                left outer join phoneline AS p3 ON (p1.extension::text::integer + 1 = CAST (p3.extension AS int)
                                                                    AND p1.company=p3.company
                                                                    AND p2.id != p3.id AND p3.status = ''active'')
          WHERE p1.function=''conference'' AND p1.company=company_id AND p1.status != ''deleted''
            AND (p2.id IS NULL or p2.status = ''deleted'') AND p3.id IS NULL;

         IF next_ext IS NULL THEN
           next_ext = from_nr;
         ELSE
           IF next_ext > to_nr THEN
             next_ext = from_nr;
           END IF;
         END IF;
       END IF;
       IF next_ext IS NULL THEN
         IF ext_len = 3 THEN
           next_ext = 980;
         END IF;
         IF ext_len = 4 THEN
           next_ext = 9800;
         END IF;
         IF ext_len = 5 THEN
           next_ext = 98000;
         END IF;
       END IF;
       RETURN next_ext;
   END;
' LANGUAGE plpgsql;

-- get next free extension for phoneline with specified function.
-- takes company.id, phoneline.function
CREATE or REPLACE FUNCTION next_ext(integer, varchar) RETURNS integer AS '
   DECLARE
       from_nr    integer;
       to_nr      integer;
       next_ext   integer;
   BEGIN
       SELECT INTO from_nr from_extension FROM extensionrange WHERE function=$2 AND company = $1;
       SELECT INTO to_nr to_extension FROM extensionrange WHERE function=$2 AND company = $1;

       SELECT INTO next_ext min(p1.extension::text::integer) + 1
         FROM phoneline AS p1 left outer join phoneline AS p2 ON (p1.extension::text::integer + 1 = CAST(p2.extension AS int)
                                                                  AND p1.company=p2.company)
                              left outer join phoneline AS p3 ON (p1.extension::text::integer + 1 = CAST(p3.extension AS int)
                                                                  AND p1.company=p3.company
                                                                  AND p2.id != p3.id AND p3.status = ''active'')
        WHERE p1.function=$2 AND p1.company = $1 AND p1.status != ''deleted''
          AND (p2.id IS NULL or p2.status = ''deleted'') AND p3.id IS NULL;

       IF next_ext IS NULL THEN
         next_ext = from_nr;
       ELSE
         IF next_ext > to_nr THEN
           next_ext = from_nr;
         END IF;
       END IF;
       RETURN next_ext;
   END;
' LANGUAGE plpgsql;

COMMIT;
