BEGIN;

-- return 'yes' if I am allowed to enter the conference box as administrator
-- get user id, conference extension
CREATE or REPLACE FUNCTION confadmin(integer,varchar) RETURNS varchar AS '
   DECLARE
       uid ALIAS for $1;
       ext ALIAS for $2;
       allow varchar;
       comp integer;
   BEGIN

       select into comp contact.company
       from   contact where id = uid;

       select into allow ''yes''
       from   conference, phoneline, contact_department
       where  phoneline.extension = ext and phoneline.company = comp and
              conference.phoneline = phoneline.id and
              conference.department = contact_department.department and
              contact_department.contact = uid;

       IF  allow = ''yes'' THEN
           return ''yes'';
       END IF;

       return ''no'';
   END;
' LANGUAGE plpgsql;

-- return 'yes' if I am allowed to spy on the extension of another person
CREATE or REPLACE FUNCTION canspy(integer,varchar) RETURNS varchar AS '
   DECLARE
       roleid integer;
       allow varchar;
       spyable varchar;
       comp integer;
       uid ALIAS for $1;
       ext ALIAS for $2;
   BEGIN

       select into roleid, comp contact.role, contact.company
       from contact where contact.id = uid;

       select into allow role.allow_spy
       from role where role.id = roleid;

       IF  allow = ''no'' THEN
       --  RAISE NOTICE ''not allowed to spy on other people'';
           return ''no'';
       END IF;

       select into roleid contact.role
       from   phoneline left join contact on contact.id = phoneline.lookupid
       where  phoneline.extension = ext and phoneline.company = comp and contact.company = comp and phoneline.status <> ''deleted'';

       select into spyable role.spyable
       from role where role.id = roleid;

       return spyable;
   END;
' LANGUAGE plpgsql;

-- return 'yes' if I am allowed to barge on the extension of another person
CREATE or REPLACE FUNCTION canbarge(integer,varchar) RETURNS varchar AS '
   DECLARE
       roleid integer;
       allow varchar;
       bargeable varchar;
       comp integer;
       uid ALIAS for $1;
       ext ALIAS for $2;
   BEGIN

       select into roleid, comp contact.role, contact.company
       from contact where contact.id = uid;

       select into allow role.allow_barge
       from role where role.id = roleid;

       IF  allow = ''no'' THEN
       --  RAISE NOTICE ''not allowed to barge on other people'';
           return ''no'';
       END IF;

       select into roleid contact.role
       from   phoneline left join contact on contact.id = phoneline.lookupid
       where  phoneline.extension = ext and phoneline.company = comp and contact.company = comp and phoneline.status <> ''deleted'';

       select into bargeable role.bargeable
       from role where role.id = roleid;

       return bargeable;
   END;
' LANGUAGE plpgsql;

-- return 'yes' if I am allowed to page another person
CREATE or REPLACE FUNCTION canpage(integer,varchar) RETURNS varchar AS '
   DECLARE
       roleid integer;
       allow varchar;
       pageable varchar;
       comp integer;
       uid ALIAS for $1;
       ext ALIAS for $2;
   BEGIN

       select into roleid, comp contact.role, contact.company
       from   contact where contact.id = uid;

       select into allow role.allow_page
       from role where role.id = roleid;

       IF  allow = ''no'' THEN
       --  RAISE NOTICE ''not allowed to page other people'';
           return ''no'';
       END IF;

       select into roleid contact.role
       from   phoneline left join contact on contact.id = phoneline.lookupid
       where  phoneline.extension = ext and contact.company = comp and phoneline.company = comp and phoneline.status <> ''deleted'';

       -- isreceptionist ???
       select into pageable role.isreceptionist
       from role where role.id = roleid;

       return pageable;
   END;
' LANGUAGE plpgsql;

-- return 'yes' if I am allowed to dial *60 to bypass callforward settings
CREATE or REPLACE FUNCTION allow_bypass_forward(integer,varchar) RETURNS varchar AS '
   DECLARE
       roleid integer;
       allow varchar;
       bypassable varchar;
       comp integer;
       uid ALIAS for $1;
       ext ALIAS for $2;
   BEGIN

       select into roleid, comp contact.role, contact.company
       from contact where contact.id = uid;

       select into allow role.allow_bypass_forward
       from role where role.id = roleid;

       IF  allow = ''no'' THEN
       --  RAISE NOTICE ''not allowed to bypass other peoples callforward settings'';
           return ''no'';
       END IF;

       select into bypassable phoneline.bypassable_forward
       from   phoneline left join contact on contact.id = phoneline.lookupid
       where  phoneline.extension = ext and contact.company = comp and phoneline.company = comp and phoneline.status <> ''deleted'';

       return bypassable;
   END;
' LANGUAGE plpgsql;

COMMIT;
