-- Call Forward Unconditional
CREATE or REPLACE FUNCTION  cfwd_unconditional_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_unconditional varchar;
    _when_unconditional_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_unconditional, _when_unconditional_number, _default_contacttype
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_unconditional,
           phoneline_forwarding.when_unconditional_number, contacttype.id
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
                     left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_unconditional_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_unconditional = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
             ''when_unconditional from '' ||  quote_literal(_when_unconditional) || ''to '' || quote_literal($3));
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_unconditional_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int; 
    _when_unconditional_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_unconditional_number, _default_contacttype 
                phoneline.id, phoneline.profile, phoneline.contact, phoneline.when_unconditional_number, contacttype.id
      from phoneline left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    update phoneline_forwarding set when_unconditional_number = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
    insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
           ''when_unconditional_number from '' ||  quote_literal(_when_unconditional_number) || ''to '' || quote_literal($3));
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_unconditional_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_unconditional
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_unconditional_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_unconditional_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

-- Call Forward Busy
CREATE or REPLACE FUNCTION  cfwd_busy_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_busy varchar;
    _when_busy_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_busy, _when_busy_number, _default_contacttype
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_busy,
           phoneline_forwarding.when_busy_number, contacttype.id
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
                     left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_busy_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_busy = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
             ''when_busy from '' ||  quote_literal(_when_busy) || ''to '' || quote_literal($3));
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_busy_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int; 
    _when_busy_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_busy_number, _default_contacttype 
                phoneline.id, phoneline.profile, phoneline.contact, phoneline.when_busy_number, contacttype.id
      from phoneline left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    update phoneline_forwarding set when_busy_number = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
    insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
           ''when_busy_number from '' ||  quote_literal(_when_busy_number) || ''to '' || quote_literal($3));
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_busy_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_busy
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_busy_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_busy_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

-- Call Forward NoAnswer
CREATE or REPLACE FUNCTION  cfwd_noanswer_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_noanswer varchar;
    _when_noanswer_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_noanswer, _when_noanswer_number, _default_contacttype
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_noanswer,
           phoneline_forwarding.when_noanswer_number, contacttype.id
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
                     left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_noanswer_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_noanswer = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
             ''when_noanswer from '' ||  quote_literal(_when_noanswer) || ''to '' || quote_literal($3));
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_noanswer_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int; 
    _when_noanswer_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_noanswer_number, _default_contacttype 
                phoneline.id, phoneline.profile, phoneline.contact, phoneline.when_noanswer_number, contacttype.id
      from phoneline left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    update phoneline_forwarding set when_noanswer_number = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
    insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
           ''when_noanswer_number from '' ||  quote_literal(_when_noanswer_number) || ''to '' || quote_literal($3));
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_noanswer_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_noanswer
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_noanswer_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_noanswer_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

-- Call Forward Loggedoff
CREATE or REPLACE FUNCTION  cfwd_loggedoff_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_loggedoff varchar;
    _when_loggedoff_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_loggedoff, _when_loggedoff_number, _default_contacttype
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_loggedoff,
           phoneline_forwarding.when_loggedoff_number, contacttype.id
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
                     left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_loggedoff_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_loggedoff = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
             ''when_loggedoff from '' ||  quote_literal(_when_loggedoff) || ''to '' || quote_literal($3));
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int; 
    _when_loggedoff_number varchar;
    _default_contacttype int;
  BEGIN
    select into _id, _profile, _contact, _when_loggedoff_number, _default_contacttype 
                phoneline.id, phoneline.profile, phoneline.contact, phoneline.when_loggedoff_number, contacttype.id
      from phoneline left join contacttype on contacttype.company = phoneline.company 
                                          and contacttype.name = ''default''
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    update phoneline_forwarding set when_loggedoff_number = $3 where profile = _profile and phoneline = _id and contacttype = _default_contacttype;
    insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
           ''when_loggedoff_number from '' ||  quote_literal(_when_loggedoff_number) || ''to '' || quote_literal($3));
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_loggedoff
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_loggedoff_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;


