UPDATE phoneline set callerid = 'DISA - Authorized' where extension = '*9001';

-- find important info on a contact
-- return name, dialplan, contacttypeid, contacttype, recording
CREATE or REPLACE FUNCTION getcontactinfo(integer) RETURNS VARCHAR AS '
   DECLARE
     _name VARCHAR;
     _dialplan VARCHAR;
     _contacttypeid INTEGER;
     _contacttype VARCHAR;
     _recording VARCHAR;
     _department VARCHAR;
     _pin VARCHAR;
   BEGIN
     select into _name, _dialplan, _contacttypeid, _contacttype, _recording, _department, _pin
                 contact.firstname || '' '' || contact.lastname as name,
                 substring(contact.allow_dial_tollfree, 1, 1) || substring(contact.allow_dial_local, 1, 1) ||
                 substring(contact.allow_dial_longdistance, 1, 1) || substring(contact.allow_dial_mobile, 1, 1) ||
                 substring(contact.allow_dial_international, 1, 1) || substring(contact.allow_dial_entertain, 1, 1) ||
                 substring(contact.allow_dial_premium, 1, 1) || substring(contact.allow_dial_adult, 1, 1) as cperm,
                 contact.contacttype, contacttype.name, phoneline.recording, department.name,
                 contact.login || contact.pin as pin
       from contact left join phoneline on (phoneline.contact = contact.id
                                        or (phoneline.function = ''contact'' and phoneline.lookupid = contact.id))
                    left join contacttype on contacttype.id = contact.contacttype
                    left join contact_department on  contact.id = contact_department.contact
                    left join department on contact_department.department = department.id
       where contact.id = $1 and phoneline.contact IS NOT NULL limit 1;

     return _name || ''#'' || _dialplan || ''#'' || _contacttypeid || ''#'' || _contacttype || ''#'' || _recording || ''#'' || _department || ''#'' || _pin;
   END;
' LANGUAGE plpgsql;


