58 lines
2.5 KiB
SQL
58 lines
2.5 KiB
SQL
-- Заказ из локации, где звонки выключены (Настройки -> Коммуникации -> Автодозвон -> Локации),
|
||
-- помечается статусом city_disabled = «Не звоним (город)». На SMS это не влияет.
|
||
-- Порядок: ручной запрет -> локация выключена -> исключение по номеру -> журнал звонков.
|
||
|
||
CREATE OR REPLACE FUNCTION public.autocall_status(og public.order_groups)
|
||
RETURNS text
|
||
LANGUAGE sql
|
||
STABLE SECURITY DEFINER
|
||
SET search_path TO 'public'
|
||
AS $function$
|
||
select case
|
||
when og.autocall_opt_out then 'opt_out'
|
||
when exists (
|
||
select 1 from public.autocall_city_rules c
|
||
where btrim(c.city) = btrim(coalesce(og.town, ''))
|
||
and c.calls_enabled = false
|
||
) then 'city_disabled'
|
||
when exists (
|
||
select 1 from public.autocall_exclusions e
|
||
where e.active
|
||
and (
|
||
(e.rule_type = 'phone' and coalesce(og.customer_phone_normalized, regexp_replace(coalesce(og.customer_phone,''),'\D','','g')) = e.value)
|
||
or
|
||
(e.rule_type = 'prefix' and coalesce(og.customer_phone_normalized, regexp_replace(coalesce(og.customer_phone,''),'\D','','g')) like e.value || '%')
|
||
)
|
||
) then 'excluded'
|
||
when not exists (select 1 from public.autocall_log l where l.order_group_id = og.id) then 'not_called'
|
||
when exists (
|
||
select 1 from public.autocall_log l
|
||
where l.order_group_id = og.id
|
||
and l.outcome in ('confirmed','confirmed_silent','confirmed_kept_previous')
|
||
) then 'confirmed'
|
||
else (
|
||
select case
|
||
when l.outcome = 'no_answer' then 'no_answer'
|
||
when l.outcome in ('no_input_day','no_input_half') then
|
||
case
|
||
when exists (select 1 from public.autocall_log l2
|
||
where l2.order_group_id = og.id and l2.outcome = 'no_input_day')
|
||
and exists (select 1 from public.autocall_log l3
|
||
where l3.order_group_id = og.id and l3.outcome = 'no_input_half')
|
||
then 'no_input_both'
|
||
when l.outcome = 'no_input_half' then 'no_input_half'
|
||
else 'no_input_day'
|
||
end
|
||
when l.outcome in ('operator','error','invalid_number') then 'manual'
|
||
else 'not_called'
|
||
end
|
||
from public.autocall_log l
|
||
where l.order_group_id = og.id
|
||
order by l.created_at desc
|
||
limit 1
|
||
)
|
||
end;
|
||
$function$;
|
||
|
||
NOTIFY pgrst, 'reload schema';
|