create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null, /* Брокер */
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @stock_id_s varchar(30) = null,
  @is_inv int = null,
  @investor_id_s varchar(30) = null,
  @is_oper int,
  @purp_code int
-- execute dbo.ap_naufor32_other_op_list;1 @broker_id_s = '21535000001051575', @date_b_d = '10/10/05', @date_e_d = '25/12/05', @stock_id_s=NULL, @is_inv = 1, @investor_id_s='21535000002034982', @is_oper=0, @purp_code=0
as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @id numeric
begin

set nocount on

select @action_id = %ACTION_ID%
------------------------------конвертация входящих переменных--------------
declare @broker_id numeric(18,0)
if @broker_id_s is NULL or @broker_id_s='' begin
   select @broker_id = null
end
else begin
   select @broker_id = convert(numeric,@broker_id_s)
end

declare @date_b datetime
if @date_b_d is NULL or @date_b_d='' begin
   select @date_b = null
end
else begin
   select @date_b = convert(datetime,@date_b_d,3)
end

declare @date_e datetime
if @date_e_d is NULL or @date_e_d='' begin
   select @date_e = null
end
else begin
   select @date_e = convert(datetime,@date_e_d,3)
end

declare @investor_id_nn numeric(18,0)
declare @investor_id_bb numeric(18,0)
declare @investor_id_ee numeric(18,0)
if @investor_id_s is NULL or @investor_id_s='' begin
   select @investor_id_nn = null
   select @investor_id_bb = -999999999999999998
   select @investor_id_ee = 999999999999999999
end
else begin
   select @investor_id_nn = convert(numeric,@investor_id_s)
   select @investor_id_bb = @investor_id_nn
   select @investor_id_ee = @investor_id_nn
end


declare @stock_id_nn numeric(18,0)
declare @stock_id_bb numeric(18,0)
declare @stock_id_ee numeric(18,0)
if @stock_id_s is NULL or @stock_id_s='' begin
   select @stock_id_nn = null
   select @stock_id_bb = -999999999999999998
   select @stock_id_ee = 999999999999999999
end
else begin
   select @stock_id_nn = convert(numeric,@stock_id_s)
   select @stock_id_bb = @stock_id_nn
   select @stock_id_ee = @stock_id_nn
end
-----------------------------------------------------------------------
declare @investor_id numeric(18,0)
 select @investor_id = convert(numeric,@investor_id_s)
declare @stock_id numeric
select @stock_id = convert(numeric,@stock_id_s)

declare @cur_plan numeric
declare @cur_partner numeric
declare @subc_str_bal varchar(255)
declare @mess varchar(255)
declare @accs_id numeric
declare @accs_num_eff varchar(255)
declare @accshort_id numeric
declare @accshort_num_eff varchar(255)

declare
@m1_id numeric,
@subc_eff_num varchar(255),
@investor_name varchar(255)

select @date_b = convert(datetime, @date_b_d, 3)
select @date_e = .DATE_NORM((convert(datetime, @date_e_d, 3)),E)
select @broker_id = convert(numeric, @broker_id_s)

if @stock_id_s = '' select @stock_id_s = null

-----------------------------находим план и партнера---------------
select @cur_partner = partner_id,
       @cur_plan = back_securs_id
  from tb_broker_change
  where id = @broker_id

.ID_VAR(@partner2plan_id)
select @partner2plan_id = .PLAN2PARTNER_ID(@cur_partner,@cur_plan)

if @partner2plan_id is null
.EXIT_MESSAGE('План счетов ценных бумаг для организации не найден')
----------------------------создаем временные таблицы--------------
create table #result(
  id numeric IDENTITY,
  item_id numeric null,
  op_name varchar(255) null,
  subc_eff_num varchar(255) null,
  op_date datetime null,
  sysdate datetime null,
  in_no varchar(255) null,
  op_sum money null,
  doc_sum money null,
price money null,
  type_name varchar(255) null,
  type_code varchar(255) null,
  first_doc varchar(255) null,
  purpose_name varchar(255) null,
  purpose_code int null,
  investor_name varchar(255) null,
--  character_id int null,
  com money null,
  stock_id numeric null,
  investor_id numeric(18,0) null,
  is_zalog int null,
  stock_name varchar(255) null,
  bank_account_id numeric null
)

--------------находим id и пр. счетов---------------------------------------------------------
.ACCID_FROM_CONST2PLAN(@accs_id,'BACK_ACC_STOCK_SV',@cur_plan,1,1,'Счет по ЦБ',@mess)  --S.A
.ACCID_FROM_CONST2PLAN(@accshort_id,'BACK_ACC_SECUR_MP_SHORT_CB',@cur_plan,1,1,'Счет КП по ЦБ',@mess)--S.C.4
select @accs_num_eff=acc_num_eff_rep from t_accounts where id=@accs_id
select @accshort_num_eff=acc_num_eff_rep from t_accounts where id=@accshort_id

-----------------------находим id типа аналитики---------------------------------------------
declare @subc_stock_type_id numeric, @subc_inv_type_id numeric
select @subc_stock_type_id = .SUBCTYPE_FROM_CODE('STOCK_EMIS')
select @subc_inv_type_id = .SUBCTYPE_FROM_CODE('INVESTORS')
---------------------------------------------------------------------------------------------

------------------------вставляем данные по счетам S.A и S.C.4-------------------------------
insert #result(item_id,op_name,subc_eff_num,op_date,sysdate,in_no,op_sum,doc_sum,type_name,
               type_code,first_doc,purpose_name, purpose_code,investor_name,
               com,stock_id,investor_id,is_zalog)
select
  ops.item_id,
  ops.op_name,
  o.subc_eff_num,
  ops.op_date,
  o.sysdate,
  tdd.in_no,
  isnull((o.sum_bal * o.deb_or_cred),0) as op_sum,
  isnull(tsm.stock_price,0) as doc_sum,
  t.type_name,
  t.type_code,
  null,
  null,
  null,
  (select item_name from t_items where id = subc2.item_id),
  null,
  subc1.item_id,
  subc2.item_id,
  null

  from t_oper_subconto subc1,  t_oper_list o ,t_oper_subconto subc2,t_accounts,t_items ai,
    t_states ass, td_depo_docs tdd, t_items i, t_types t, /* t_purposes purp, */ td_stock_moves tsm,
    t_operations ops
  where (subc1.item_id = @stock_id or @stock_id is null)
    and subc1.subconto_type_id = @subc_stock_type_id
    and subc1.id = o.id

    and (subc2.item_id = @investor_id or @investor_id is null)
    and subc2.subconto_type_id = @subc_inv_type_id
    and subc2.id = o.id

    and o.partner2plan_id = @partner2plan_id
    and o.op_date >= @date_b and o.op_date <= @date_e

    and o.acc_id = t_accounts.id
    and t_accounts.id = ai.id
    and ai.state_id = ass.id
    and ass.class_id !=2
    and ((rtrim(ltrim(UPPER(t_accounts.acc_num_eff_rep))) LIKE isnull(rtrim(ltrim(UPPER((@accs_num_eff)
        ))),'%')+'%')   or
      (rtrim(ltrim(UPPER(t_accounts.acc_num_eff_rep))) LIKE isnull (rtrim(ltrim(UPPER((@accshort_num_eff)
    ))),'%')+'%'))

  and tdd.id = ops.item_id
  and i.id = ops.item_id
  and t.id = i.type_id

--  and tdd.id not in (select tbd.id from tb_baysale_docitems tbd where tbd.id = tdd.id)

--  and upper(t.type_code) in ('STOCK_TRANSFER', 'STOCK_TRANSFER_OTHER_DEPO' ,'D_STOCK_OUT','D_STOCK_OUT_MOVE','D_STOCK_OUT_OTHER_DEPO','TAKING_CHARGE_ZALOG')
--    and upper(t.type_code) not in ('STOCK_TRANSFER','STOCK_TRANSFER_OTHER_DEPO','D_STOCK_OUT','D_STOCK_OUT_MOVE','D_STOCK_OUT_OTHER_DEPO','TAKING_CHARGE_ZALOG')
--  and tsm.is_dogovor_id = purp.id
--  and purp.code in (65,66,50,51,98,99,100)
  and tsm.id =* ops.item_id

  and ops.id = o.head_id
-------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
-------******************-данные по ДС -*********************----------------
-----------------------------------------------------------------------------

----------находим плана и партнера----------
select @cur_partner = partner_id,
       @cur_plan = back_money_id
  from tb_broker_change
  where id = @broker_id

declare @plan2partner_id numeric
select @plan2partner_id=.PLAN2PARTNER_ID(@cur_partner,@cur_plan)

---------------------находим счет 2--------
declare @acc2_id numeric
declare @acc2_num_eff varchar(255)
.ACCID_FROM_CONST2PLAN(@acc2_id,'BACK_ACC_MONEY_PLACE',@cur_plan,1,1,'Счет по ДС',@mess)
--.ACCID_FROM_CONST2PLAN(@acc309_id,'BACK_ACC_FUND_MONEY',@cur_plan,1,3,'Счет фонда клиента по ДС',@mess)
select @acc2_num_eff  = acc_num_eff from t_accounts where id = @acc2_id
--select @acc309_num_eff= acc_num_eff from t_accounts where id = @acc309_id

---------формирование строки субконто в зависимости от выбранной группировки
select @subc_str_bal=''
.SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@investor_id,Y)--строка субконто

---------------- собираются вручную проводки по счету 2----------
insert #result
select o.item_id,
       o.op_name,
       ol.subc_eff_num,
       convert(datetime,convert(varchar,ol.op_date,3),3),
       min(o.sysdate),
       null,--это in_no
       sum(ol.deb_or_cred * ol.sum_bal) as op_sum,
       sum(ol.deb_or_cred * ol.sum_bal) as doc_sum, null,
       '', '', '', '', null,
       (select item_name from t_items where id = subc.item_id)
       ,null,null,subc.item_id,null, null, null

from  t_operations o INNER JOIN
                      t_oper_list ol ON o.id = ol.head_id INNER JOIN
                      t_accounts a ON ol.acc_id = a.id INNER JOIN
                      t_items i ON o.item_id = i.id INNER JOIN
                      t_types t ON i.type_id = t.id inner join
                      t_oper_subconto subc on subc.id = ol.id
WHERE     (NOT EXISTS
                          (SELECT     1
                            FROM          t_simples
                            WHERE      type_id = 5500 AND upper(simple_name) = upper(o.op_name)))
  AND ol.partner2plan_id = @plan2partner_id
  and o.op_date >= @date_b
  and o.op_date < dateadd(dd, 1, @date_e)
  and a.acc_num_eff like @acc2_num_eff+'.%'
  and ol.subc_eff_num like '%'+@subc_str_bal+'%'
  and .ITEMS_EXISTS(o.item_id)

  and (subc.item_id = @investor_id or @investor_id is null)
  and subc.subconto_type_id = @subc_inv_type_id

group by ol.op_date,o.item_id,a.acc_num_eff,o.op_name,ol.subc_eff_num,subc.item_id--a.acc_type_id,, o.sysdate--

delete #result where ISNULL(op_sum,0) =0

delete #result from tb_baysale_docitems b
where b.id = item_id

------------------------------отсеить договора с классификатором VISIBLE_IN_NAUFOR_REPORTS----------
delete #result
from t_item2class t_i2c, t_classes t_c, t_classvalues t_cv
where upper(ltrim(rtrim(t_c.code)))=upper(ltrim(rtrim('VISIBLE_IN_NAUFOR_REPORTS')))
  and t_cv.class_id=t_c.id
  and t_i2c.value_id=t_cv.id
  and t_cv.code = 'INVISIBLE_IN_REPORT'
  and t_i2c.item_id = #result.investor_id
----------------------------------------------------------------------------------------------------
-------------------обновление документа основания и целевого назначения ( нужен для выдачи)-------------------
update #result set
  type_name=t.type_name,
  type_code=t.type_code,
  in_no=d.in_no,
  first_doc=(case
      when upper(t.type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE') then 'Отчет брокера от '+convert(varchar,d.in_date,103)
      when upper(t.type_code) in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 'Тикет '+isnull(d.out_no,' ')+' / '+isnull(convert(varchar,d.out_date,103),' ')
      when upper(t.type_code) in ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT','MONEY_MOVE_COMMISSION_2','MONEY_MOVE2INVESTORS') then 'Выписка по расчетному счету от '+convert(varchar,d.in_date,103)
      end),
  purpose_name=(case
      when upper(t.type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then t.type_name
      when upper(t.type_code) in ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT','MONEY_MOVE_COMMISSION_2','MONEY_MOVE2INVESTORS') then (select isnull(p.perpose_str,purp.name) from
                                  t_purposes purp,t_paydocs p where p.id=d.id and purp.id=p.purpose_id)
      end)
from t_types t,t_items i,td_depo_docs d
where i.id=#result.item_id
  and t.id=i.type_id
  and d.id=#result.item_id
----------------------платежи фонда--------------------------
update #result set
in_no = p.doc_number,
purpose_name = pur.name
from t_paydocs p, t_items i, t_types t, t_purposes pur
where i.id = p.id
  and #result.item_id = i.id
  and i.type_id = t.id
  and t.type_code in ('SD_PAYBANK_OUT','SD_PAYBANK_IN')
  and p.purpose_id = pur.id
---------------------ручная проводка-----------------------------------
update #result set
  type_name = t.type_name, type_code = t.type_code, in_no = d.jour_no,
  first_doc = i.item_name + ' ' + convert(varchar, d.doc_date,103),
  purpose_name = 'Ручная проводка: ' + isnull(d.doc_comment, '')
from t_types t, t_items i, t_man_jours d
where i.id=#result.item_id
  and t.id=i.type_id
  and d.id=#result.item_id
  and upper(t.type_code) in ('MANOPER')  --  типа Журнал ручных проводок

---------------для групповых операций с ЦБ-----------------
/* Добавление флага залога */
update #result set is_zalog =
  (select case when (isnull((select tdst.depo_subacc_type_code
               from td_depo_subacc_types tdst, td_depo_subacc tds
               where tdst.id = tds.depo_subacc_type_id
                 and tds.id = tsm.t_subacc_id),'') = 'ZS' and
          isnull((select tdst.depo_subacc_type_code
                 from td_depo_subacc_types tdst, td_depo_subacc tds
                 where tdst.id = tds.depo_subacc_type_id
                   and tds.id = tsm.s_subacc_id),'') <> 'ZS') then 1

            when (isnull((select tdst.depo_subacc_type_code
               from td_depo_subacc_types tdst, td_depo_subacc tds
               where tdst.id = tds.depo_subacc_type_id
                 and tds.id = tsm.t_subacc_id),'') <> 'ZS' and
          isnull((select tdst.depo_subacc_type_code
                 from td_depo_subacc_types tdst, td_depo_subacc tds
                 where tdst.id = tds.depo_subacc_type_id
                   and tds.id = tsm.s_subacc_id),'') = 'ZS') then 2 end
from td_stock_moves tsm
where tsm.id = #result.item_id)

update #result set is_zalog =
  (select case when (isnull((select tdst.depo_subacc_type_code
               from td_depo_subacc_types tdst, td_depo_subacc tds
               where tdst.id = tds.depo_subacc_type_id
                 and tds.id = tsm.t_subacc_id),'') = 'ZS' and
          isnull((select tdst.depo_subacc_type_code
                 from td_depo_subacc_types tdst, td_depo_subacc tds
                 where tdst.id = tds.depo_subacc_type_id
                   and tds.id = tsm.s_subacc_id),'') = 'ZS') then 3

            when (isnull((select tdst.depo_subacc_type_code
               from td_depo_subacc_types tdst, td_depo_subacc tds
               where tdst.id = tds.depo_subacc_type_id
                 and tds.id = tsm.t_subacc_id),'') <> 'ZS' and
          isnull((select tdst.depo_subacc_type_code
                 from td_depo_subacc_types tdst, td_depo_subacc tds
                 where tdst.id = tds.depo_subacc_type_id
                   and tds.id = tsm.s_subacc_id),'') <> 'ZS') then 0 end
from td_stock_moves tsm
where tsm.id = #result.item_id)


update #result set
first_doc=(case
      when upper(st.stock_type_code) in ('VEX', 'BIL', 'VORGRSB')
                                  then (select t_purposes.name from t_purposes, td_stock_moves sm
                                  where sm.is_dogovor_id=t_purposes.id and i.id=sm.id) + ' от ' +convert(varchar,d.in_date,103)
      when upper(t.type_code) in ('STOCK_TRANSFER','STOCK_TRANSFER_OTHER_DEPO','D_STOCK_OUT','D_STOCK_OUT_MOVE','D_STOCK_OUT_OTHER_DEPO','TAKING_CHARGE_ZALOG')
                                  then 'Уведомление депозитария '+(select p.partner_short_name from t_partners p,td_depo_accounts da,td_depo_subacc ds,td_stock_moves sm
                                  where i.id=sm.id and ds.id=sm.s_subacc_id and da.id=ds.depo_acc_id and p.id=da.organisation_id)+' от '+convert(varchar,d.in_date,103)
      when upper(t.type_code) in ('D_STOCK_IN','D_STOCK_IN_MOVE','D_STOCK_IN_OTHER_DEPO','D_STOCK_ZALOG')
                                  then 'Уведомление депозитария '+(select p.partner_short_name from t_partners p,td_depo_accounts da,td_depo_subacc ds,td_stock_moves sm
                                  where i.id=sm.id and ds.id=sm.t_subacc_id and da.id=ds.depo_acc_id and p.id=da.organisation_id)+' от '+convert(varchar,d.in_date,103)
end)
from t_types t, t_items i, td_depo_docs d, t_stock_types st, td_stock_emis te
where i.id = #result.item_id
  and te.id = #result.stock_id
  and te.stock_type_id = st.id
  and t.id = i.type_id
  and d.id = #result.item_id
  and #result.first_doc is null

------------------------для групповых операций с ЦБ---------------------------
/* "Отчет брокера от ..." или "Выписка по расчетному счету от ..." в зависимости
от того, на брокерский или на расчетный счет поступают ден. средства*/

.ID_VAR(@bank_account) -- id типа аналитики
select @bank_account=.SUBCTYPE_FROM_CODE('BANK_ACCOUNT')
declare @bank_account_id numeric

declare cur cursor for
select id, subc_eff_num from #result
for update
open cur
fetch cur into @id, @subc_eff_num

while .CURSOR_STATE=0 begin
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,@bank_account,@bank_account_id)

update #result set
bank_account_id = @bank_account_id
where id = @id
  fetch cur into @id, @subc_eff_num
end
close cur
.DEALLOCATE cur
---конец курсора

update #result set
purpose_name=t_p.name,  in_no=d.in_no,
 first_doc= (case when b.acc_type_id = 4 then 'Отчет брокера от '+convert(varchar,d.in_date,103)
                  when b.acc_type_id =1 then 'Выписка по расчетному счету от '+convert(varchar,d.in_date,103)
            end)
from t_items i, td_depo_docs d,
     ts_sd_stock_moves ts_m, t_purposes t_p,
     tb_list_stock_redemption tb_l,
     t_bank_accounts b
where i.id=#result.item_id
   and ts_m.id=d.id
   and d.perpose_id = t_p.id
   and i.id=tb_l.id
   and tb_l.item_id=ts_m.id
   and b.id=#result.bank_account_id
---------------------------------------
/* проверка залога */
update #result set
  purpose_name = (case is_zalog when 1 then 'Приём ЦБ в залог'
                                when 2 then 'Вывод ЦБ из залога'
                                when 3 then 'Перевод залога' end),
  purpose_code = (case is_zalog when 1 then 98
                                when 2 then 99
                                when 3 then 100 end)
where is_zalog <> 0

update #result set
  com=(select sum(td_comis2oper.comis) from td_comis2oper
         where td_comis2oper.item_id = #result.item_id
           and .ITEMS_EXISTS(td_comis2oper.id))

delete #result
where first_doc is null or purpose_name is null


select
  #result.op_date,
  #result.in_no,
  abs(isnull(#result.op_sum,0)) as op_sum,
  abs(isnull(#result.doc_sum,0)) as doc_sum, 
  abs(isnull(#result.price,round((isnull(#result.doc_sum,0))/(isnull(abs(#result.op_sum),0)),8))) as price,
  #result.first_doc,
  #result.purpose_name,
  #result.investor_name,
  #result.stock_name,
  isnull(#result.com,0) as com,
  #result.purpose_code
from #result
order by op_date,in_no,investor_name

end