-- FrmDocs.QSel
--
-- Журнал складских документов
--

select
  bdok.id                              as id,
  iif (bdok.lck = 1, 'Б', '')          as lck,
  bdok.sf_d                            as sf_d,
  bdok.sf_n                            as sf_n,
  bdok.tn_d                            as tn_d,
  bdok.tn_n                            as tn_n,
  bdok.io                              as io,
  bdok.ndok                            as ndok,
  bdok.data                            as data,
  bdok.type_                           as type_,
  bdok.idpost                          as idpost,
  iif(oper < 10   , '', 'возврат'    ) as ret,
  iif(idpost >= 0 , '', 'перемещение') as mov,
  --
  case bdok.paymode
    when 0 then '               '
    when 1 then 'наличные (ККМ) '
    when 2 then 'терминал (VISA)'
    when 3 then 'безнал         '
    else bdok.paymode
  end                                  as pay_mode,
  --
  post.name || iif((post.gorod = ''), '', ' (' || post.gorod || ')' )
                                       as post_full_name,

  bdok.summ_w_nds                      as summ_w_nds,
  --
  iif( bdok.summ_mode in (1, 2),
    iif( bdok.disc_buh0 is null,  '-', bdok.disc_buh0 || '%'),
    '')                                as disc_buh0,
  --
  iif(bdok.im = 1, 'ИМ', '')           as im,
  bdok.imr_nord                        as imr_nord,
  bdok.msg                             as msg,
  --
  (select count(*) from logprd where (logprd.id_bdok = bdok.id)) as prn_count, -- сколько раз распечатывался документ
  --
  iif (bdok.io in ('t', 'f'), 'S', '') as ROW_FONTSTYLE, -- перечеркнем удаленные доки
  iif(idpost >= 0 , '', 'BLUE'       ) as ROW_COLOR      -- выделим перемещения синим шрифтом

from bdok
  left join spost post on (post.id = bdok.idpost)

where (1 = 1)
  -- по дате будем фильтровать только закрытые документы. А открытые и резервы - целиком,
  -- иначе там набирается куча по жизни незакрытых документов
--  and ((bdok.data  >= dateAdd(0 - :last_days day to current_date))
  and ((bdok.data  >= dateAdd(0 - 60 day to current_date))
       or (bdok.type_   = 1)
       or (bdok.reserve = 1)
      )
  and (bdok.nsklad = -1     )  -- and (bdok.nsklad = :nsklad)
  and (bdok.io     = 'F'    )  -- and (bdok.io     = :io    )
  and (bdok.type_  = 0      )  -- and (bdok.type_  = :type_ )

--  and ((post.idagent  = :idagent) or (:idagent = -1))
--  and ((bdok.reserve  = :reserve) or (:reserve = -1))

order by
  data, idpost, ndok

Plan
------------------------------------------------
PLAN (LOGPRD INDEX (LOGPRD_ID_BDOK))
PLAN SORT (JOIN (BDOK INDEX (BDOK_FK_NSKLAD, BDOK_PK), POST INDEX (SPOST_PK)))

Query Time
------------------------------------------------
Prepare       : 16,00 ms
Execute       : 218,00 ms
Avg fetch time: 6,81 ms

Memory
------------------------------------------------
Current: 101 390 848
Max    : 155 215 232
Buffers: 2 048

Operations
------------------------------------------------
Read   : 8 153
Writes : 2
Fetches: 0
Marks  : 2


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|BDOK                           |         0 |     93174 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|LOGPRD                         |         0 |       601 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|SPOST                          |         0 |      2639 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+