-- find a contact given some callerid
CREATE or REPLACE FUNCTION get_contact_by_callerid(integer, varchar, varchar) RETURNS INTEGER AS '
   DECLARE
     p_company ALIAS for $1;
     p_callerid ALIAS for $2;
     p_countryprefix ALIAS for $3;
     _contactid INTEGER;
     tmp VARCHAR;
   BEGIN
     IF p_callerid = '''' THEN
       return 1;
     END IF;
     -- check number against contact.mobile
     select into _contactid contact.id
       from contact left join contact_department on contact_department.contact = contact.id
                    left join department on department.id = contact_department.department
      where contact.status <> ''deleted'' and department.company = p_company and
            contact.mobile = p_callerid
      limit 1;
     IF FOUND THEN
       return _contactid;
     END IF;

     -- maybe the callerid is in ITU format? Remove the countrycode and prefix with 0
     select into _contactid contact.id
       from contact left join contact_department on contact_department.contact = contact.id
                    left join department on department.id = contact_department.department
      where contact.status <> ''deleted'' and department.company = p_company and
            contact.mobile = ''0'' || substring(p_callerid from length(p_countryprefix)+3)
      limit 1;
     IF FOUND THEN
       return _contactid;
     END IF;
     RAISE NOTICE ''% NOT FOUND IN contact.mobile'', p_callerid;

     -- check against contact_number.number
     select into _contactid  contact.id
       from contact left join contact_department on contact_department.contact = contact.id
                    left join department on department.id = contact_department.department
                    left join contact_number on contact_number.contact = contact.id
      where contact.status <> ''deleted'' and department.company = p_company and
            contact_number.number = p_callerid and
            contact_number.status != ''deleted'' and contact_number.approved = ''yes''
      limit 1;
     IF FOUND THEN
       return _contactid;
     END IF;

     -- maybe the callerid is in ITU format? Remove the countrycode and prefix with 0
     select into _contactid contact.id
       from contact left join contact_department on contact_department.contact = contact.id
                    left join department on department.id = contact_department.department
                    left join contact_number on contact_number.contact = contact.id
      where contact.status <> ''deleted'' and department.company = p_company and
            contact_number.number = ''0'' || substring(p_callerid from length(p_countryprefix)+1) and
            contact_number.status != ''deleted'' and contact_number.approved = ''yes''
      limit 1;
     IF FOUND THEN
       return _contactid;
     END IF;

     RAISE NOTICE ''% NOT FOUND in contact_number.number'', p_callerid;
     return ''1'';
   END;
' LANGUAGE plpgsql;


