select
    (select
    '/local00/files/' || gi2.company_id || '/' || if2.id || '/filename.mp3.wav' filename
    from
        groups_ivr_files gi2
        ,ivr_files if2
        ,dids_group dg2
    where
        gi2.company_id = if2.company_id
        and gi2.filename = if2.filename
        and if2.recoded is not null
        and dg2.company_id = gi2.company_id
        and dg2.call_group = gi2.call_group
        and dg2.did = d.did
    ) ivr_filename									--
    ,(select
        fwd_to
    from
        cfwd_line cl2
        ,dids_group dg2
    where
        cl2.company_id = dg2.company_id
        and cl2.call_group = dg2.call_group
        and dg2.did = d.did
    ) dest_fwd_to									--
    ,(select
        g2.recording
    from
        groups g2
        ,dids_group dg2
    where
        dg2.did = d.did
        and dg2.company_id = g2.company_id
        and dg2.call_group = g2.call_group
    ) dest_recording									--
    ,(select
        g2.rule_id
    from
        groups g2
        ,dids_group dg2
    where
        dg2.did = d.did
        and dg2.company_id = g2.company_id
        and dg2.call_group = g2.call_group
    ) dial_rule										--
    ,(select
        call_group
    from
        dids_group dg2
    where
        dg2.did = d.did
    ) dest_call_group									--
    ,(select
        gf2.email
    from
        dids_group dg2
        ,groups_fax gf2
    where 
        dg2.did = d.did
        and dg2.company_id = gf2.company_id
        and dg2.call_group = gf2.call_group
    limit 1
    ) fax2mail										--
    ,c.name l_company									--
    ,(select
        case (
            select
                (select
                    c2.in_lines
                from
                    companies c2
                where
                    c2.id = d1.company_id
                ) - (
                select
                    count(*)
                from
                    channels
                where
                    callstate <> 'DOWN'
                    and dest in (
                        select
                            d2.did
                        from
                            dids d2
                        where
                            company_id = (
                                select
                                    distinct company_id
                                from
                                    dids
                                where
                                    did = d1.did
                                )
                            )
                ) cur_channels
            from
                dids d1
            where
                d1.did = d.did) > 0
        when
            true then 1
            else 0
        end
    ) n_lines										--
    ,c.id company_id									--
    ,(select
        min(gl2.delay)
    from
        groups_line gl2
        ,dids_group dg2
        ,groups g2
    where
        dg2.did = d.did
        and gl2.company_id = dg2.company_id
        and gl2.call_group = dg2.call_group
        and gl2.company_id = g2.company_id 
        and gl2.call_group = g2.call_group
    ) delay										--
    ,(select
      array_to_string(
        array(
          select
            gl2.line
            || ',' ||
            coalesce(case
              when
                (((extract(isodow from now()) - 1) * 10000 + extract(hour from now()) * 100 + extract(minute from now()))::int <@ any(c2.day_z)
                and c2.fwd_enable = 'true')
                or
                (not (((extract(isodow from now()) - 1) * 10000 + extract(hour from now()) * 100 + extract(minute from now()))::int <@ any(c2.day_z))
                and c2.fwd_enable = 'false')
              then
                c2.fwd_to
            end, '')
            || ':' ||
            coalesce(gl2.delay, 0)
            || ':' ||
            coalesce(c2.timeout, 0)
            || ':' ||
            case when
                g2.rule_id in (2, 3) then 
                    row_number() over(order by random())
                else
                    coalesce(gl2.priority, 0)
                end 
          from
            groups_line gl2
          join
            dids_group dg2
          on
            gl2.company_id = dg2.company_id
            and gl2.call_group = dg2.call_group
          join
            groups g2
          on
            gl2.company_id = g2.company_id
            and gl2.call_group = g2.call_group
          left outer join
            cfwd c2
          on
            gl2.company_id = c2.company_id
            and gl2.line = c2.line
          where
            dg2.did = d.did
          order by
            case
              when
                g2.rule_id in (2, 3)
              then
                random()
              else
                gl2.priority
            end
            ,gl2.line
        )
      ,';'
    )) dest_lines2									--
    ,g_o.keypress min_keypress
from
    dids d
join
    companies c
on
    d.company_id = c.id
join
    dids_group dg
on
    d.company_id = dg.company_id
    and d.did = dg.did
join
    groups g
on
    dg.company_id = g.company_id
    and g.call_group = g.call_group
left outer join
    groups g_o
on
    g.company_id = g_o.company_id
    and g.call_group = g_o.parent_group
where
    d.did = '%s'
order by
    case when
        g_o.keypress = 0
    then
        9.5
    when
        g_o.keypress = 10
    then
        -1
    else
        g_o.keypress
    end
limit 1;