BEGIN;
DROP VIEW v_my_colleagues;
alter table phoneline drop column when_unconditional;
alter table phoneline drop column when_unconditional_number;
alter table phoneline drop column when_busy;
alter table phoneline drop column when_busy_number;
alter table phoneline drop column when_noanswer;
alter table phoneline drop column when_noanswer_number;
alter table phoneline drop column when_noanswer_after;
alter table phoneline drop column when_loggedoff;
alter table phoneline drop column when_loggedoff_number;

CREATE VIEW v_my_colleagues AS
select contact.id,contact_department.department,contact.visible,contact.firstname, contact.lastname,
       phoneline.extension, phoneline.company, channel.protocol, channel.username,
       'DAHDI/' || dahdi_channel.channel as dahdi_channel
  from contact left join phoneline on phoneline.lookupid = contact.id and phoneline.status <> 'deleted'
                                  and phoneline.function = 'contact'
               left join phone on phone.id = contact.phone and phone.status <> 'deleted'
               left join contact_department on contact.id = contact_department.contact
               left join channel on channel.phone = contact.phone and channel.inuse = 'yes'
                                and channel.line = phoneline.line
               left join dahdi_channel on dahdi_channel.id = phone.dahdi_channel and dahdi_channel.channel <> '1'
                                    and phone.dahdi_channel <> '1'
 where contact.id > 1 and contact.status <> 'deleted' and contact.visible = 'yes'
       and phoneline.company = mycompanyid(CAST(USER as varchar));
GRANT SELECT ON v_my_colleagues TO GROUP astium_users;

COMMIT;

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;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact, _default_contacttype
                phoneline.id, phoneline.profile, phoneline.contact, 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'';
    select into _when_unconditional_number when_unconditional_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and contacttype = _default_contacttype limit 1;
    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() );
    IF _when_unconditional_number IS NULL THEN
        _when_unconditional_number := '''';
    END IF;
    _value := ''when_unconditional_number from '' ||  quote_literal(_when_unconditional_number) || ''to '' || quote_literal($3);
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
    return ''0'';
  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;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact, _default_contacttype
                phoneline.id, phoneline.profile, phoneline.contact, 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'';
    select into _when_busy_number when_busy_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and contacttype = _default_contacttype limit 1;
    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() );
    IF _when_busy_number IS NULL THEN
        _when_busy_number := '''';
    END IF;
    _value := ''when_busy_number from '' ||  quote_literal(_when_busy_number) || ''to '' || quote_literal($3);
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_noanswer(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_noanswer_number varchar;
    _default_contacttype int;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact, _default_contacttype
                phoneline.id, phoneline.profile, phoneline.contact, 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'';
    select into _when_noanswer_number when_noanswer_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and contacttype = _default_contacttype limit 1;
    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() );
    IF _when_noanswer_number IS NULL THEN
        _when_noanswer_number := '''';
    END IF;
    _value := ''when_noanswer_number from '' ||  quote_literal(_when_noanswer_number) || ''to '' || quote_literal($3);
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
    return ''0'';
  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;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact, _default_contacttype
                phoneline.id, phoneline.profile, phoneline.contact, 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'';
    select into _when_loggedoff_number when_loggedoff_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and contacttype = _default_contacttype limit 1;
    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() );
    IF _when_loggedoff_number IS NULL THEN
        _when_loggedoff_number := '''';
    END IF;
    _value := ''when_loggedoff_number from '' ||  quote_literal(_when_loggedoff_number) || ''to '' || quote_literal($3);
    insert into history (created, creator, contact, type, lookup, lookupid, value) values
           (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
    return ''0'';
  END;
' LANGUAGE plpgsql;

