宁卫免费PBX中的排队机空闲分机搜询策略

drop function get_group_idle_ext_first;--get_group_idle_ext_first用于查找第一个空闲的座席分机--p_use_multi_domain  是不是使用多租户--p_callin_number  呼入号码--p_group_number   组名或组短号，但可以带domain--p_transfer_times  呼转次数，第一次会考虑以前是谁接的，那么本次优先他--p_use_agent_status 是否要使用座席状态，如置忙、置闲，迁入迁出（上线下线）等，1为需要，0为不需要，放在这里，也可以允许使用者自己手工更改CREATE FUNCTION get_group_idle_ext_first( IN p_use_multi_domain BOOLEAN, IN p_callin_number TEXT, IN p_group_number TEXT, IN p_transfer_times int, IN p_use_agent_status int)RETURNS TABLE( myext text, mytimeout INT, mypassed BOOLEAN) LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFEAS $BODY$DECLARE f_mode int;f_timeout int;f_domain TEXT;f_group_number TEXT;f_position int;f_sql TEXT;f_c_split TEXT;f_use_agent_status int;--------r_passed BOOLEAN;r_ext Text;r_timeout int;----v_record  RECORD;BEGIN r_passed := false; f_c_split := '@'; if (p_use_multi_domain =true) then f_group_number := p_group_number; f_position := strpos(f_group_number,'@'); --postition(f_c_split in f_group_number);     if (f_position = 0) then f_group_number := p_group_number; f_domain := 'default'; else f_group_number := substring(p_group_number from 1 for f_position-1); f_domain := substring(p_group_number from f_position+1 for char_length(p_group_number)-f_position ); end if;  else f_group_number := p_group_number; f_domain := 'default'; end if; f_mode := -1;   if (p_use_agent_status =1) then f_use_agent_status :=1;  else f_use_agent_status :=0; end if;  raise notice 'group_number is %',f_group_number; raise notice 'domain is %',f_domain; ---------------------------------  if (p_use_multi_domain = true) then f_sql := 'select a.queue_mode,a.group_callout_timeout from nway_ext_group a, nway_fs_domains b where a.group_number=''' || f_group_number || ''' and a.domain_id=b.id and b.domain_name=''' || f_domain || ''';';  else f_sql := 'select queue_mode  ,group_callout_timeout  from nway_ext_group  where  group_number=''' || f_group_number || ''';'; end if;   raise notice 'group sql:%',f_sql;  EXECUTE f_sql INTO f_mode,f_timeout;  raise notice 'mode:%, timeout:%',f_mode,f_timeout; if (f_mode is NULL) then raise notice 'data is null'; r_timeout := -1; r_ext := 'error:not found group'; else if (f_mode < 0) then --return r_passed; r_timeout := -1; r_ext := 'mode not support'; else --执行后续 if (f_timeout is NULL) then r_timeout = 15; else r_timeout = f_timeout; end if;  if (f_mode >2 and p_transfer_times = 0) then if (p_use_multi_domain = true) then if (f_use_agent_status =1) then f_sql := 'SELECT extension_number  FROM nway_extension  where reg_state=''reged'' and agent_status=''idle'' and ' || ' call_state=''ready'' and extension_number  = ' ||  ' (select agent_number from nway_call_remember where call_number=''' || p_callin_number ||  ''' and  group_number=''' || f_group_number || ''' and domain_name=''' || f_domain || ''' order by insert_time desc limit 1 );'; else f_sql := 'SELECT extension_number  FROM nway_extension  where reg_state=''reged''    ' || ' and call_state=''ready'' and extension_number  = ' ||  ' (select agent_number from nway_call_remember where call_number=''' || p_callin_number ||  ''' and  group_number=''' || f_group_number || ''' and domain_name=''' || f_domain || ''' order by insert_time desc limit 1 );'; end if;  else if (f_use_agent_status =1) then f_sql := 'SELECT extension_number  FROM nway_extension  where reg_state=''reged'' and agent_status=''idle'' and call_state=''ready''' ||  ' and extension_number  = (select agent_number from nway_call_remember where call_number=''' ||p_callin_number ||  ''' and group_number=''' || f_group_number || ''' order by insert_time desc limit 1 );'; else f_sql := 'SELECT extension_number  FROM nway_extension  where reg_state=''reged'' and   call_state=''ready''' ||  ' and extension_number  = (select agent_number from nway_call_remember where call_number=''' ||p_callin_number ||  ''' and group_number=''' || f_group_number || ''' order by insert_time desc limit 1 );'; end if;  end if; raise notice 'last agent sql:%',f_sql;  EXECUTE f_sql INTO r_ext; end if; ------  if (r_ext is NULL OR char_length(r_ext) <2) then  RAISE NOTICE 'search idle agent from talbe:%',p_callin_number; if (p_use_multi_domain = true) then if (f_use_agent_status =1) then if (f_mode=3 OR f_mode = 0) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'')' || ' and (c.call_state=''ready'' OR c.call_state=''READY'') and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'')' || ' and s.id=e.domain_id and s.domain_name=''' || f_domain || '''  and (e.group_number=''' ||f_group_number || ''') and '|| ' (c.extension_number in ( select m.ext from nway_ext_group_map m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''' )) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1;'; end if; if (f_mode=1 OR f_mode = 4) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'')'|| ' and (c.call_state=''ready'' OR c.call_state=''READY'') and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'')'|| ' and s.id=e.domain_id and s.domain_name=''' || f_domain ||'''  and (e.group_number=''' ||f_group_number || ''') and (c.extension_number in ' || ' ( select m.ext from nway_ext_group_map  m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by random() limit 1;'; end if; if (f_mode = 2 OR f_mode =5) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and' || ' (c.call_state=''ready'' OR c.call_state=''READY'') and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'')'|| ' and s.id=e.domain_id and s.domain_name=''' || f_domain ||'''  and (c.extension_number >e.current_ext_number) and (e.group_number=''' || f_group_number || ''') and (c.extension_number in ( select m.ext from nway_ext_group_map  m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''' )) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1;';  end if;  else if (f_mode=3 OR f_mode = 0) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'')' || ' and (c.call_state=''ready'' OR c.call_state=''READY'')' || ' and s.id=e.domain_id and s.domain_name=''' || f_domain || '''  and (e.group_number=''' ||f_group_number || ''') and '|| ' (c.extension_number in ( select m.ext from nway_ext_group_map m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''' )) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1;'; end if; if (f_mode=1 OR f_mode = 4) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'')'|| ' and (c.call_state=''ready'' OR c.call_state=''READY'') '|| ' and s.id=e.domain_id and s.domain_name=''' || f_domain ||'''  and (e.group_number=''' ||f_group_number || ''') and (c.extension_number in ' || ' ( select m.ext from nway_ext_group_map  m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by random() limit 1;'; end if; if (f_mode = 2 OR f_mode =5) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and' || ' (c.call_state=''ready'' OR c.call_state=''READY'') '|| ' and s.id=e.domain_id and s.domain_name=''' || f_domain ||'''  and (c.extension_number >e.current_ext_number) and (e.group_number=''' || f_group_number || ''') and (c.extension_number in ( select m.ext from nway_ext_group_map  m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number ||  ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''' )) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1;';  end if;  end if; else if (f_use_agent_status =1) then if (f_mode=3 OR f_mode = 0) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'')'|| ' and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'')  and (e.group_number=''' ||f_group_number ||  ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number ||  ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1; '; end if; if (f_mode=1 OR f_mode = 4) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'') '|| ' and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'') and (e.group_number=''' ||f_group_number ||  ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number ||  ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by random() limit 1; '; end if; if (f_mode = 2 OR f_mode =5) then --from e.current_ext_number f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'')' || ' and (c.agent_state=''up'' OR c.agent_state=''UP'') and (c.agent_status=''ready'' OR c.agent_status=''idle'') and (c.extension_number >e.current_ext_number)'|| ' and (e.group_number=''' ||f_group_number ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number || ''')) and '|| '(extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1; ';  end if;  else if (f_mode=3 OR f_mode = 0) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'')'|| '    and (e.group_number=''' ||f_group_number ||  ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number ||  ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1; '; end if; if (f_mode=1 OR f_mode = 4) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'') '|| '   and (e.group_number=''' ||f_group_number ||  ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number ||  ''')) and (extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by random() limit 1; '; end if; if (f_mode = 2 OR f_mode =5) then --from e.current_ext_number f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and (c.call_state=''ready'' OR c.call_state=''READY'')' || '  and (c.extension_number >e.current_ext_number)'|| ' and (e.group_number=''' ||f_group_number ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number || ''')) and '|| '(extract(epoch FROM (now()-c.last_state_change_time)) >1 ) order by c.extension_number limit 1; ';  end if;  end if; end if; raise notice 'agent sql:%',f_sql; EXECUTE f_sql INTO r_ext;  if (r_ext is NULL) then r_passed := false; r_ext := ''; raise notice 'not found idle extension'; r_timeout=0;          if (f_mode = 2 OR f_mode =5) then if (p_use_multi_domain = true) then f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e,nway_fs_domains s where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and ' ||  ' (c.call_state=''ready'' OR c.call_state=''READY'') and (c.seat_state=''up'' OR c.seat_state=''UP'') and (c.seat_status=''ready'' OR c.seat_status=''idle'') ' || ' and s.id=e.domain_id and s.domain_name=''' || f_domain || '''  and (e.group_number=''' || f_group_number || ''') and (c.extension_number in (select m.ext from nway_ext_group_map  m, nway_ext_group n,nway_fs_domains p  where n.group_number=''' || f_group_number || ''' and n.id =m.ext_group_id and n.domain_id=p.id and p.domain_name=''' || f_domain || ''' )) order by c.extension_number limit 1;'; else f_sql := 'select c.extension_number from nway_extension c,nway_ext_group e  where (c.reg_state=''reged'' OR c.reg_state=''REGED'') and '|| ' (c.call_state=''ready'' OR c.call_state=''READY'') and (c.seat_state=''up'' OR c.seat_state=''UP'') and (c.seat_status=''ready'' OR c.seat_status=''idle'') '|| ' and (e.group_number=''' || f_group_number || ''') and (c.extension_number in ( select ext from nway_ext_group_map where ext_group_number=''' || f_group_number || ''')) order by c.extension_number limit 1;'; end if;  raise notice 'mode 2or5 repeat sql:%',f_sql; EXECUTE f_sql INTO r_ext; if (r_ext is NULL ) then r_passed = false; else r_passed = true; end if; end if; else raise notice 'found idle extension'; r_passed = true; end if;  else  r_passed := true; end if; end if;    end if; if (r_passed = true) then   if (p_use_multi_domain = true) then f_sql := 'update nway_extension set call_state=''callout'',last_state_change_time=now() where extension_number ='''|| r_ext ||''' and domain_id=(select id from nway_fs_domwains where domain_name='''|| f_domain || ''');'; else f_sql :='update nway_extension set call_state=''callout'',last_state_change_time=now() where extension_number ='''|| r_ext ||''';'; end if; raise notice 'found ext:% !!! and set it callout sql:%',r_ext,f_sql; EXECUTE f_sql;   if (p_use_multi_domain = true) then f_sql := 'update nway_ext_group set current_ext_number=''' || r_ext|| ''' where group_number =''' || f_group_number || ''' and domain_id in (select id from nway_fs_domains where domain_name='''|| f_domain || ''');'; else f_sql :='update nway_ext_group set current_ext_number=''' || r_ext|| ''' where group_number =''' || f_group_number || ''';'; end if; raise notice 'set ext:% is current using extension for group sql:%',r_ext,f_sql; EXECUTE f_sql;  if (p_use_multi_domain = true ) then myext := r_ext || '@' || f_domain; else myext := r_ext; end if; else myext := ''; end if; mypassed := r_passed; mytimeout := r_timeout; return query select myext,mytimeout,mypassed; END;$BODY$;    select public.get_group_idle_ext_first(true,'18621575908','110@nway.com',0,1);