create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null,
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
--  @broker_code varchar(100) = null,
--  @broker_name varchar(255) = null,
  @is_inv int = null,
  @is_bank int = null,
  @is_portf int = null,
  @is_portf_sub int = null,
  @is_asset int = null,
  @is_acc int = null,                    -- комиссии отдельной строкой
  @investor_id_s varchar(30) = null,
  @bank_account_id_s varchar(30) = null,
  @portfolio_id_s varchar(30) = null,
  @investor2portfolio_id_s varchar(30) = null,
  @place_id_s varchar(30) = null,
  @is_pass int = null

as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @id numeric
begin
--.EXIT_MESSAGE_PARM('Дата начальная',@date_b_d)
set nocount on

select @action_id = %ACTION_ID%

declare @broker_id numeric
declare @date_b datetime
declare @date_e datetime
declare @cur_plan numeric
declare @cur_partner numeric
declare @subc_str_bal varchar(255)
declare @mess varchar(255)
declare @acc2_id numeric
declare @acc2_num_eff varchar(255)
declare @acc41_id numeric
declare @acc41_num_eff varchar(255)
declare @acc309_id numeric
declare @acc309_num_eff varchar(255), @acc_num_eff varchar(255), @acc_last_num_eff varchar(255)

declare
  @m1_id numeric,
  @m2_id numeric,
  @m3_id numeric,
  @m4_id numeric,
  @m5_id numeric,
  @subc_eff_num varchar(255),
  @investor_name varchar(255),
  @bank_account_name varchar(255),
  @portfolio_name varchar(255),
  @section_name varchar(255),
  @asset_place_name varchar(255)

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



declare
  @investor_id numeric(18,0),
  @bank_account_id numeric(18,0),
  @portfolio_id numeric(18,0),
  @investor2portfolio_id numeric(18,0),
  @place_id numeric(18,0),
  @emi_id numeric(18,0)

-- проверка существования входных данных

if isnull(@investor_id_s,'-1') = '-1'
  select @investor_id = null
else
  select @investor_id = convert(numeric, @investor_id_s)

if isnull(@bank_account_id_s,'-1') = '-1'
  select @bank_account_id = null
else
  select @bank_account_id = convert(numeric, @bank_account_id_s)

if isnull(@portfolio_id_s,'-1') = '-1'
  select @portfolio_id = null
else
  select @portfolio_id = convert(numeric, @portfolio_id_s)

if isnull(@investor2portfolio_id_s,'-1') = '-1'
  select @investor2portfolio_id = null
else
  select @investor2portfolio_id = convert(numeric, @investor2portfolio_id_s)

if isnull(@place_id_s,'-1') = '-1'
  select @place_id = null
else
  select @place_id = convert(numeric, @place_id_s)

-- определение плана счетов и владельца
select @cur_partner = partner_id,
       @cur_plan = back_money_id
  from tb_broker_change
  where id = @broker_id

declare @plan2partner_id numeric

--Возвращает plan2partner по id плана и владельца
select @plan2partner_id=.PLAN2PARTNER_ID(@cur_partner,@cur_plan)

create table #result(
id numeric IDENTITY,
item_id numeric,
op_name varchar(100) null,
acc_id numeric,
acc_num_eff varchar(50),
acc_last_id numeric,
acc_last_num_eff varchar(50),
acc_last_type_id int,
subc_eff_num varchar(255),
op_date datetime,
sysdate datetime null,
in_no varchar(50) null,
op_sum_in money,
op_sum_out money,
type_name varchar(100) null,
type_code varchar(100) null,
first_doc varchar(255) null,
purpose_name varchar(150) null,
com_br money null,
com_other money null,
investor_name varchar(255) null,
investor_id numeric null,
bank_account_name varchar(150) null,
portfolio_name varchar(150) null,
section_name varchar(150) null,
asset_place_name varchar(255) null,
portf_sub_id numeric null,
bank_account_id numeric null
)

create table #rep(
id numeric,
item_id numeric,
op_name varchar(50) null,
acc_id numeric,
acc_num_eff varchar(50),
acc_last_id numeric,
acc_last_num_eff varchar(50),
acc_last_type_id int,
subc_eff_num varchar(255),
op_date datetime,
sysdate datetime null,
in_no varchar(50) null,
saldo_b money,
saldo_e money,
op_sum_in money,
op_sum_out money,
type_name varchar(150) null,
type_code varchar(100) null,
first_doc varchar(150) null,
purpose_name varchar(100) null,
com_br money null,
com_other money null,
investor_name varchar(255) null,
investor_id numeric null,
bank_account_name varchar(150) null,
portfolio_name varchar(150) null,
section_name varchar(150) null,
asset_place_name varchar(255) null,
subconto varchar(255) null,
saldo_sub_b money null,
saldo_acc_b money null,
saldo_sub_e money null,
saldo_acc_e money null,
curs_diff money null,
portf_sub_id numeric null
)

select @subc_str_bal=''

--достаем id счета
.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)
.ACCID_FROM_CONST2PLAN(@acc41_id,'BACK_ACC_COMISSIONS',@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 @acc41_num_eff= acc_num_eff from t_accounts where id = @acc41_id

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

if @is_bank=1
begin
  .SUBC_STR_ADD(@subc_str_bal,'BANK_ACCOUNT',@bank_account_id,Y)
end
else
begin
.SUBC_STR_ADD(@subc_str_bal,'BANK_ACCOUNT',null,Y) --добавили
end

if @is_portf=1
begin
  .SUBC_STR_ADD(@subc_str_bal,'PORTFOLIO',@portfolio_id,Y)
end

if @is_asset=1
begin
  .SUBC_STR_ADD(@subc_str_bal, 'ASSET_PLACES', @place_id,Y)
end

declare @subc_str_bal2 varchar(255)
select @subc_str_bal2 = @subc_str_bal



/*if @is_portf_sub=1
begin*/
 -- .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@investor2portfolio_id,Y)
---end

if @is_portf_sub=1
begin
  .SUBC_STR_ADD(@subc_str_bal2,'INVESTOR2PORTFOLIO',@investor2portfolio_id,Y)
end

--select @subc_str_bal2 , @subc_str_bal--test

-- собираются вручную проводки по данному @plan2partner_id и за данный промежуток времени
insert #result
select o.item_id,
null, @acc2_id, @acc2_num_eff,
a.id, a.acc_num_eff,a.acc_type_id,
ol.subc_eff_num, convert(datetime,convert(varchar,ol.op_date,3),3),
 min(o.sysdate), null,

  sum(case when t.type_code in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 0
           else (case ol.deb_or_cred
                      when 1 then ol.sum_bal
                      else 0
                 end)
      end),

  sum(case when t.type_code in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 0
           else (case ol.deb_or_cred
                      when 1 then 0
                      else ol.sum_bal
                 end)
      end),
null, null, null, null, null,null,'',null,'','','','',null,null--1


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_oper_list op ON o.id = op.head_id INNER JOIN
                      t_accounts a2 ON op.acc_id = a2.id INNER JOIN
                      t_items i ON o.item_id = i.id INNER JOIN
                      t_types t ON i.type_id = t.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 a2.acc_num_eff not like @acc41_num_eff
  and ol.id <> op.id
  and ol.subc_eff_num like '%'+@subc_str_bal+'%'
group by convert(datetime,convert(varchar,ol.op_date,3),3),o.item_id, a.id,a.acc_num_eff,a.acc_type_id,ol.subc_eff_num--, o.sysdate--
--order by o.sysdate
--====отладка
--select t_items.item_name,#result.* from #result inner join t_items on t_items.id = #result.item_id

/*select * from #result
where item_id='21535000002051303'
select * from #result
where item_id='21535000002038544'--test
*/

/* отладка
select t_items.item_name,#result.* from #result inner join t_items on t_items.id = #result.item_id
where item_name like '%0301010190%'
*/
select @subc_str_bal=''

if @is_inv=1
begin
  .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@investor_id,Y)
end

declare @subc_inv_type_id numeric

select @subc_inv_type_id=.SUBCTYPE_FROM_CODE('INVESTORS')--Возвращает id типа субконто по шифру

--при условии, что группировка по пассиву, но непонятен момент с @subc_inv_type_id

if @is_pass=1
begin

insert #result
select o.item_id, null, @acc309_id, @acc309_num_eff, @acc309_id,
  @acc309_num_eff, 2, '{' + convert(varchar, (isnull(@subc_inv_type_id,0))) + ':' + convert(varchar,tos.item_id)+'}',
  convert(datetime,convert(varchar,ol.op_date,3),3),
  Min(o.sysdate), null,
  sum(case when t.type_code in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 0
           else (case ol.deb_or_cred
                      when 1 then ol.sum_bal
                      else 0
                 end)
      end),
  sum(case when t.type_code in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 0
           else (case ol.deb_or_cred
                      when 1 then 0
                      else ol.sum_bal
                 end)
      end),
  null,null,null,null,null,null,'',null,'','','','',null,null--2


from t_operations o,t_oper_list ol,t_accounts a,t_types t,t_items i,t_oper_subconto tos
where ol.partner2plan_id = @plan2partner_id
  and ol.acc_id = a.id
  and a.acc_num_eff like @acc309_num_eff + '.%'
  and ol.head_id = o.id
  and ol.subc_eff_num like '%'+@subc_str_bal+'%'
  and o.op_date >= @date_b
  and o.op_date < dateadd(dd, 1, @date_e)
  and tos.id=ol.id
  and tos.subconto_type_id=@subc_inv_type_id
  and i.id=o.item_id
  and t.id=i.type_id
  and upper(t.type_code) not in ('MONEY_MOVE_COMMISSION_2') --перевод ДС внутри инвестора
  and (case
       when t.type_code in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') then 'null'
       else o.op_name
       end) not in (select simple_name from t_simples where type_id = 5500)
group by convert(datetime,convert(varchar,ol.op_date,3),3),o.item_id, tos.item_id

end

--новое!!!
.ID_VAR(@bank_account) -- счет
select @bank_account=.SUBCTYPE_FROM_CODE('BANK_ACCOUNT')
declare @subc_eff_num2 varchar(255)
declare @bank_account_id2 numeric
--declare @id numeric

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

while .CURSOR_STATE=0 begin
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num2,@bank_account,@bank_account_id2)

update #result set
bank_account_id = @bank_account_id2
where id = @id

  fetch cur1 into @id, @subc_eff_num2
end
close cur1
.DEALLOCATE cur1
---конец курсора

--обновление документа основания и целевого назначения (нужен для выдачи)
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)
      else 'Не задан'
      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)
      else t.type_name
      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
  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
  type_name=t.type_name, purpose_name=t.type_name, type_code=t.type_code
from t_types t,t_items i
where i.id=#result.item_id
  and t.id=i.type_id
  and #result.item_id not in (select d.id from td_depo_docs d)

--select * from #result --test

--для групповых операций с ЦБ
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
bank_account_name=(case when b.acc_type_id = 2 then  'Валютный счет  №'+isnull(@bank_account_name,' ')+ ' в ' + i.item_name
                       when b.acc_type_id = 3 then  'Депозитный счет  №'+isnull(@bank_account_name,' ')+ ' в ' + i.item_name
                       when b.acc_type_id = 4 then  'Брокерский счет  №'+isnull(@bank_account_name,' ')+ ' в ' + i.item_name
else 'Расчетный счет №'+isnull(@bank_account_name,' ') + ' в ' + i.item_name end),
acc_type_id = isnull(b.acc_type_id, 1),
bank_acc_id = isnull(@m1_id, #result.bank_acc_id)
from t_bank_accounts b, t_items i
where #result.id=@id
  and b.id = @m1_id
  and i.id = b.bank_id
*/

--------------------------------------------------------------------
-----                   Расчет комиссий                     --------
--------------------------------------------------------------------
if isnull(@is_acc,0)=0
begin
 update #result set
  com_br= (select sum(td_comis2oper.comis) from td_comis2oper
            where td_comis2oper.item_id = #result.item_id
           and td_comis2oper.service_id = 3
           and .ITEMS_EXISTS(td_comis2oper.id)),

  com_other=(select sum(td_comis2oper.comis) from td_comis2oper
               where td_comis2oper.item_id = #result.item_id
                 and td_comis2oper.service_id <> 3
                 and .ITEMS_EXISTS(td_comis2oper.id))


end
else
 begin
       update #result
         set com_br= (select sum(td_comis2oper.comis)
                        from td_comis2oper
                       where td_comis2oper.item_id = #result.item_id
                         and td_comis2oper.service_id = 3
                         and td_comis2oper.item_id <> td_comis2oper.comis_foundation_id
                         and .ITEMS_EXISTS(td_comis2oper.id)
                     ),
             com_other=(select sum(td_comis2oper.comis)
                          from td_comis2oper
                         where td_comis2oper.item_id = #result.item_id
                           and td_comis2oper.service_id <>3
                           and td_comis2oper.item_id<>td_comis2oper.comis_foundation_id
                           and .ITEMS_EXISTS(td_comis2oper.id)
                        )
    insert into #result
    select distinct o.item_id,o.op_name, a.id,a.acc_num_eff,
       #result.acc_last_id, #result.acc_last_num_eff,
       #result.acc_last_type_id, ol.subc_eff_num,
       ol.op_date,ol.sysdate,#result.in_no,null,null,
       t.type_name,t.type_code, i.item_name, null,--(цел.наз)
       sum(td_comis2oper.comis),
       null,null,null,null,null,null,null,null,null--3
   from   t_operations o,
          #result,
          t_oper_list fol,
          t_oper_list ol,
          td_comis2oper ,
          dbo.t_subconto_types,
          t_oper_subconto,
          t_accounts a,
          t_items i,
          t_types t
   where o.item_id in (select td_comis2oper.comis_foundation_id
                                              from td_comis2oper,#result
                                             where td_comis2oper.item_id = #result.item_id
                                               and td_comis2oper.service_id = 3
                                               and .ITEMS_EXISTS(td_comis2oper.id)
                        )


     and td_comis2oper.item_id = #result.item_id
    --? and o.item_id = td_comis2oper.comis_foundation_id
     and o.id = fol.head_id
     and fol.deb_or_cred = 1
     and fol.id = dbo.t_oper_subconto.id
     and dbo.t_oper_subconto.item_id = #result.item_id
     and t_subconto_types.subc_code = 'DOC_SEC'
     and dbo.t_oper_subconto.subconto_type_id=t_subconto_types.id
     and fol.head_id = ol.head_id
     and ol.deb_or_cred = -1
     and ol.acc_id = a.id
     and a.acc_num_eff like '%2.0.%'
     and i.id = #result.item_id
     and t.id = i.type_id

    group by o.item_id,o.op_name, a.id,a.acc_num_eff,
             #result.acc_last_id, #result.acc_last_num_eff, #result.acc_last_type_id,
             ol.subc_eff_num,ol.op_date,ol.sysdate,#result.in_no,
             t.type_name,t.type_code, i.item_name

    insert into #result
    select distinct o.item_id,o.op_name, a.id,a.acc_num_eff,
       #result.acc_last_id, #result.acc_last_num_eff,
       #result.acc_last_type_id,ol.subc_eff_num,
       ol.op_date,ol.sysdate,#result.in_no,null,null,
       t.type_name,t.type_code, i.item_name, null,--(цел.наз)
       null,sum(td_comis2oper.comis),
       null,null,null,null,null,null, null,null--4
    from   t_operations o,
          #result,
          t_oper_list fol,
          t_oper_list ol,
          td_comis2oper ,
          dbo.t_subconto_types,
          t_oper_subconto,
          t_accounts a,
          t_items i,
          t_types t
   where o.item_id in (select td_comis2oper.comis_foundation_id
                                              from td_comis2oper,#result
                                             where td_comis2oper.item_id = #result.item_id
                                               and td_comis2oper.service_id <> 3
                                               and .ITEMS_EXISTS(td_comis2oper.id)
                        )


     and td_comis2oper.item_id = #result.item_id
     and o.item_id = td_comis2oper.comis_foundation_id
     and o.id = fol.head_id
     and fol.deb_or_cred = 1
     and fol.id = dbo.t_oper_subconto.id
     and dbo.t_oper_subconto.item_id = #result.item_id
     and t_subconto_types.subc_code = 'DOC_SEC'
     and dbo.t_oper_subconto.subconto_type_id=t_subconto_types.id
     and fol.head_id = ol.head_id
     and ol.deb_or_cred = -1
     and ol.acc_id = a.id
     and a.acc_num_eff like '%2.0.%'
     and i.id = #result.item_id
     and t.id = i.type_id

    group by o.item_id,o.op_name, a.id,a.acc_num_eff,#result.acc_last_id,
             #result.acc_last_num_eff, #result.acc_last_type_id,
             ol.subc_eff_num,ol.op_date,ol.sysdate,#result.in_no,
             t.type_name,t.type_code, i.item_name

end

   /*update #result set
    com_br=isnull(#result.com_br, 0) + (select tb_bill2dealings.comis                        --t_schfact.doc_date
         from t_schfact,td_depo_doc2bill,tb_bill2dealings, #result r, td_prices p
        where td_depo_doc2bill.bill_id = t_schfact.id
        and tb_bill2dealings.pos2bill_id=td_depo_doc2bill.id
        and tb_bill2dealings.item_id = #result.item_id
        and p.id = tb_bill2yjdjtdealings.price_position_id
        and p.service_id = 3),
    com_other=isnull(#result.com_other, 0) + (select sum(tb_bill2dealings.comis)                        --t_schfact.doc_date
         from t_schfact,td_depo_doc2bill,tb_bill2dealings, #result r, td_prices p
        where td_depo_doc2bill.bill_id = t_schfact.id
        and tb_bill2dealings.pos2bill_id=td_depo_doc2bill.id
        and tb_bill2dealings.item_id = #result.item_id
        and p.id = tb_bill2dealings.price_position_id
        and p.service_id <> 3)
    */
      ----- Обновляем комиссиями, основанием которых является та же сделка

  ----- Добавляем проводки из счетов по комиссиям, основанием которых является та же сделка
    /* where ----------------------

         and t_operations.item_id in (select td_comis2oper.comis_foundation_id
                                              from td_comis2oper,#result
                                             where td_comis2oper.item_id = #result.item_id
                                               and td_comis2oper.service_id =3
                                               and .ITEMS_EXISTS(td_comis2oper.id)
                                            )
 */
--====отладка
-- select t_items.item_name,#result.* from #result inner join t_items on t_items.id = #result.item_id

.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc2_id,@subc_str_bal2,L,N,Y,D,P)

--delete #tmp_osv where acc_num_eff not like @acc309_num_eff+'%' and acc_num_eff not like @acc2_num_eff+'.%'

declare @op_sum_in money,
@op_sum_out money,
@acc_type_id int,
@acc_id numeric,
@saldo_b money,
@saldo_e money,
@saldo money,
@subc_eff_num_old varchar(255),
@acc_num_eff_old varchar(255),
@acc_last_num_eff_old varchar(255),
@com_br money,
@com_other money

declare @saldo_sub_b money
declare @saldo_sub_e money
declare @saldo_acc_b money
declare @saldo_acc_e money
declare @old_id numeric
declare @gen_id numeric

select @subc_str_bal=''



declare curs cursor for
select id from #result
for update

open curs

fetch curs into @id

while .CURSOR_STATE=0
begin

--вставить кусочек про комиссии



select @subc_eff_num = subc_eff_num from #result where id = @id

select @subc_str_bal = ''

if @subc_str_bal like ':'+'%'           -- вероятнее всего отладка
.EXIT_MESSAGE_PARM('@subc_str_bal = ', @subc_str_bal)

--Достает экземпляры аналитики по соответствующему типу

.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'INVESTORS',@m1_id)
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'BANK_ACCOUNT',@m2_id)
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'PORTFOLIO',@m3_id)
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'INVESTOR2PORTFOLIO',@m4_id)
.GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'ASSET_PLACES',@m5_id)


/*if @is_inv <> 1
   select @m1_id = null*/

if @m1_id is not null
begin
   select @investor_name=(select item_name from t_items where id = @m1_id)
   .SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @m1_id)
end

if @is_bank<>1
select @m2_id = null

if @m2_id is not null
begin
   select @bank_account_name=(select bank_account_no from t_bank_accounts where id=@m2_id)
   .SUBC_STR_ADD(@subc_str_bal,'BANK_ACCOUNT',@m2_id)
end


if @is_portf <> 1
  select @m3_id = null

if @m3_id is not null
begin
   select @portfolio_name=(select item_name from t_items where id=@m3_id)
   .SUBC_STR_ADD(@subc_str_bal, 'PORTFOLIO', @m3_id)
end


update #result set
  portf_sub_id=@m4_id
where id = @id

-- условие непонятное
/*if exists(select 1 from tb_portfolio_section,tb_broker_change
           where tb_portfolio_section.id=@m4_id
           and tb_broker_change.id=tb_portfolio_section.investor_id)
   and exists(select 1 from tb_baysale_docitems,#result
           where #result.id=@id and tb_baysale_docitems.id=#result.item_id
           and tb_baysale_docitems.character_id=2)
begin

update #result set
op_sum_in=isnull(com_br,0)+isnull(com_other,0),
com_br=0,
com_other=0
where id=@id

end
*/
if @is_portf_sub <> 1
 select @m4_id = null

if @m4_id is not null
begin
   select @section_name=(select item_name from t_items where id=@m4_id)
  .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@m4_id)
end

if @is_asset <> 1
  select @m5_id = null

if @m5_id is not null
begin
   select @asset_place_name=(select item_name from t_items where id=@m5_id)
   .SUBC_STR_ADD(@subc_str_bal,'ASSET_PLACES',@m5_id)
end

--обновляем табл.( это основная причина курсора)
update #result set
  investor_name = isnull(@investor_name, ' '),
  investor_id = @m1_id,
  bank_account_name = isnull(@bank_account_name, ' '),
  portfolio_name = isnull(@portfolio_name, ' '),
  section_name = isnull(@section_name, ' '),
  asset_place_name = isnull(@asset_place_name, ' '),
  subc_eff_num = @subc_str_bal
where id = @id

select
@investor_name = null,
@bank_account_name = null,
@portfolio_name = null,
@section_name = null,
@asset_place_name = null,
@m1_id = null,
@m2_id = null,
@m3_id = null,
@m4_id = null,
@m5_id = null,
@subc_str_bal = ''

fetch curs into @id

end

close curs

.DEALLOCATE curs



------------------------------отсеить договора с классификатором 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
----------------------------------------------------------------------------------------------------

select @saldo = 0,
       @subc_eff_num_old = null,
       @acc_num_eff_old = null,
       @acc_last_num_eff_old = null,
       @gen_id = 0

update #rep set
sysdate=(select max(h.action_date) from t_actions_history h where h.doc_id=#rep.item_id)


declare curs2 cursor for  --обработка табл. от TOTALS_QUERY #tmp_osv
select id from #result where acc_num_eff like @acc2_num_eff + '%'
order by acc_num_eff, acc_last_num_eff, subc_eff_num,op_date,sysdate --ура!

open curs2
fetch curs2 into @id

while .CURSOR_STATE=0
begin

select
  @subc_eff_num = isnull(subc_eff_num,''),
  @op_sum_in  = op_sum_in,
  @op_sum_out = op_sum_out,
  @acc_type_id= acc_last_type_id,
  @acc_id     = acc_last_id,
  @com_br     = com_br,
  @com_other  = com_other,
  @acc_num_eff= acc_num_eff,
  @acc_last_num_eff = acc_last_num_eff
from #result where id = @id

select @gen_id=@gen_id+1

--удаляются секции из строки субконто
if @is_inv=1 and @investor_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num,'INVESTORS',@investor_id)
end

if @is_bank=1 and @bank_account_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num,'BANK_ACCOUNT',@bank_account_id)
end

if @is_portf=1 and @portfolio_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num,'PORTFOLIO',@portfolio_id)
end

if @is_portf_sub=1 and @investor2portfolio_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num,'INVESTOR2PORTFOLIO',@investor2portfolio_id)
end

if @is_asset=1 and @place_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num, 'ASSET_PLACES', @place_id)
end

-- расчет
if @acc_num_eff_old = @acc_num_eff
  begin
    if @acc_last_num_eff_old = @acc_last_num_eff
      begin
        if @subc_eff_num_old = @subc_eff_num
          select @saldo_b = @saldo,
                 @saldo_sub_b=0,
                 @saldo_acc_b=0
        else
          begin
            select @saldo = 0
            select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                                      from #tmp_osv t
                                      where t.acc_id = @acc_id
                                        and isnull(t.subc_eff_num,'') like '%' + @subc_eff_num + '%'), 0)
            select @saldo_sub_b=@saldo_b
            select @saldo_acc_b=@saldo_b
          end
      end
    else
      begin
        select @saldo = 0
        select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                                  from #tmp_osv t
                                  where t.acc_id = @acc_id
                                    and ((isnull(t.subc_eff_num,'') like '%'+@subc_eff_num+'%')
                                    or (@subc_eff_num like '%'+isnull(t.subc_eff_num,'')+'%'))), 0)
    select @saldo_acc_b=@saldo_b
    select @saldo_sub_b=@saldo_b
      end
  end
else
  begin
    select @saldo = 0
    select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                              from #tmp_osv t
                              where t.acc_id = @acc_id
                                and ((isnull(t.subc_eff_num,'') like '%' + @subc_eff_num + '%')
                                or (@subc_eff_num like '%' + isnull(t.subc_eff_num,'') + '%'))), 0)
    select @saldo_acc_b=@saldo_b
    select @saldo_sub_b=@saldo_b

  end

if @acc_last_num_eff_old is not null
begin

if (isnull(@subc_eff_num_old,'') <> isnull(@subc_eff_num,'')) or (@acc_last_num_eff_old<>@acc_last_num_eff)
begin

   update #rep set
   saldo_sub_e=@saldo_e,
   saldo_acc_e=@saldo_e
  where id=@old_id

end

end

select @saldo_e = @saldo_b + (@op_sum_in - @op_sum_out) - isnull(@com_br,0) - isnull(@com_other,0)
select @saldo = @saldo_e
select
  @subc_eff_num_old = @subc_eff_num,
  @acc_num_eff_old  = @acc_num_eff,
  @acc_last_num_eff_old = @acc_last_num_eff,
  @old_id=@gen_id


--Вставляем в результирующую таблицу
insert #rep
select @gen_id,r.item_id, r.op_name, r.acc_id, r.acc_num_eff, r.acc_last_id,
  r.acc_last_num_eff, r.acc_last_type_id, r.subc_eff_num, r.op_date,
  r.sysdate, r.in_no, @saldo_b, @saldo_e, r.op_sum_in, r.op_sum_out,
  r.type_name, r.type_code, r.first_doc, r.purpose_name, r.com_br,
  r.com_other, r.investor_name, r.investor_id, r.bank_account_name, r.portfolio_name,
  r.section_name, r.asset_place_name, r.subc_eff_num,@saldo_sub_b,@saldo_acc_b,0,0,null,r.portf_sub_id
from #result r where r.id = @id
order by acc_num_eff, acc_last_num_eff, subc_eff_num, op_date, r.sysdate, isnull(in_no,'')

fetch curs2 into @id

end

close curs2

.DEALLOCATE curs2

   update #rep set
   saldo_sub_e=@saldo_e,
   saldo_acc_e=@saldo_e
  where id=@old_id

--====отладка
--select t_items.item_name,#result.* from #result inner join t_items on t_items.id = #result.item_id

--====отладка
--select t_items.item_name,#rep.* from #rep inner join t_items on t_items.id = #rep.item_id

select @subc_str_bal = ''
if @is_inv=1
begin
  .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@investor_id,Y)
end

truncate table #tmp_osv

if @is_pass=1
begin

--.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc309_id,@subc_str_bal,L,N,Y,D,P)
.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc2_id,@subc_str_bal,U,N,Y,D,P)

update #tmp_osv set
acc_id=@acc309_id



select @saldo=0, @subc_eff_num_old=null, @acc_num_eff_old = null, @acc_last_num_eff_old = null

declare curs3 cursor for
select id from #result where acc_num_eff like @acc309_num_eff + '%'
order by acc_num_eff, acc_last_num_eff, subc_eff_num, op_date,sysdate,isnull(in_no,'')
-- расчет по табл. #tmp_osv. Выполняется все, в случае группировки по пассиву
open curs3
fetch curs3 into @id

while .CURSOR_STATE=0
begin

select
  @subc_eff_num = isnull(subc_eff_num,''),
  @op_sum_in  = op_sum_in,
  @op_sum_out = op_sum_out,
  @acc_type_id= acc_last_type_id,
  @acc_id     = acc_last_id,
  @com_br     = com_br,
  @com_other  = com_other,
  @acc_num_eff= acc_num_eff,
  @acc_last_num_eff = acc_last_num_eff
from #result where id = @id

select @gen_id=@gen_id+1

if @is_inv=1 and @investor_id is not null
begin
  .SUBC_STR_DEL(@subc_eff_num,'INVESTORS',@investor_id)
end

if @acc_num_eff_old = @acc_num_eff
  begin
    if @acc_last_num_eff_old = @acc_last_num_eff
      begin
        if @subc_eff_num_old = @subc_eff_num
          select @saldo_b = @saldo,
                 @saldo_sub_b=0,
                 @saldo_acc_b=0
        else
          begin
            select @saldo = 0
            select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                                      from #tmp_osv t
                                      where t.acc_id = @acc_id
                                        and isnull(t.subc_eff_num,'') like '%' + @subc_eff_num + '%'), 0)
            select @saldo_sub_b=@saldo_b
            select @saldo_acc_b=@saldo_b
          end
      end
    else
      begin
        select @saldo = 0
        select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                                  from #tmp_osv t
                                  where t.acc_id = @acc_id
                                    and ((isnull(t.subc_eff_num,'') like '%'+@subc_eff_num+'%')
                                    or (@subc_eff_num like '%'+isnull(t.subc_eff_num,'')+'%'))), 0)
            select @saldo_sub_b=@saldo_b
            select @saldo_acc_b=@saldo_b
      end
  end
else
  begin
    select @saldo = 0
    select @saldo_b = isnull((select isnull(t.saldo_db,0) - isnull(t.saldo_cb,0)
                              from #tmp_osv t
                              where t.acc_id = @acc_id
                                and ((isnull(t.subc_eff_num,'') like '%' + @subc_eff_num + '%')
                                or (@subc_eff_num like '%' + isnull(t.subc_eff_num,'') + '%'))), 0)
            select @saldo_sub_b=@saldo_b
            select @saldo_acc_b=@saldo_b
  end

if @acc_last_num_eff_old is not null
begin

if (isnull(@subc_eff_num_old,'') <> isnull(@subc_eff_num,'')) or (@acc_last_num_eff_old<>@acc_last_num_eff)
begin

   update #rep set
   saldo_sub_e=@saldo_e,
   saldo_acc_e=@saldo_e
  where id=@old_id

end

end

select @saldo_e = @saldo_b + (@op_sum_in - @op_sum_out) - isnull(@com_br,0) - isnull(@com_other,0)
select @saldo = @saldo_e
select
  @subc_eff_num_old = @subc_eff_num,
  @acc_num_eff_old  = @acc_num_eff,
  @acc_last_num_eff_old = @acc_last_num_eff,
  @old_id=@gen_id

insert #rep
 select @gen_id,r.item_id, r.op_name, r.acc_id, r.acc_num_eff, r.acc_last_id,
  r.acc_last_num_eff, r.acc_last_type_id, r.subc_eff_num, r.op_date,
  r.sysdate, r.in_no, @saldo_b, @saldo_e, r.op_sum_in, r.op_sum_out,
  r.type_name, r.type_code, r.first_doc, r.purpose_name, r.com_br,
  r.com_other, r.investor_name, r.investor_id, r.bank_account_name, r.portfolio_name,
  r.section_name, r.asset_place_name, r.subc_eff_num,@saldo_sub_b,@saldo_acc_b,0,0,null,r.portf_sub_id
from #result r where r.id = @id
order by acc_num_eff, acc_last_num_eff, subc_eff_num, op_date, sysdate, isnull(in_no,'')

fetch curs3 into @id

end

close curs3

.DEALLOCATE curs3

   update #rep set
   saldo_sub_e=@saldo_e,
   saldo_acc_e=@saldo_e
  where id=@old_id

end


-- курсовая разница

declare @item_id numeric, @cur numeric, @pay_cur numeric, @sum money, @date1 datetime,
  @date2 datetime, @sum1 money, @sum2 money, @sum3 money, @rur numeric, @rate money, @rate_date datetime
declare @type_code varchar(255)

select @rur = id
  from t_currencies
  where cur_code = 'RUR'
    and .ITEMS_EXISTS(t_currencies.id)

declare curs4 cursor for
select id, item_id, type_code
  from #rep
  where
 upper(type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR',
'DOG_DEALINGS_SALE_VBR','REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT')
--and item_id =21535000002048450 --test
open curs4
fetch curs4 into @id, @item_id, @type_code

while .CURSOR_STATE=0
begin

if @type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
select @cur = tbd.cur_id, @pay_cur = tbd.pay_cur_id, @sum = tbd.summ_doc_total,
    @date1 = tdd.in_date, @date2 = tpd.fact_pay_date
    from tb_baysale_docitems tbd, tb_plan2fact_dates tpd, td_depo_docs tdd
    where tbd.id = @item_id
      and tpd.id = tbd.id
      and tdd.id = tbd.id


if @type_code in ('REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT')
select @cur = t_paydocs.cur_dealing_id, @pay_cur = tbd.pay_cur_id,
@sum=t_paydocs.doc_sum, @sum3= tbd.sum_in_paycur,
@date1 = tdd.in_date, @date2 = tpd.fact_pay_date
    from tb_baysale_docitems tbd, tb_plan2fact_dates tpd,
         td_depo_docs tdd,t_paydocs
    where tpd.id = tbd.id
      and tdd.id = tbd.id
and t_paydocs.foundation_id=tbd.id
and t_paydocs.id=@item_id


--select @cur, @pay_cur, @sum, @date1, @date2, @item_id, @id, @type_code --test
  if ((@cur <> @pay_cur) or (@cur <> @rur)) and (@date1 is not null) and (@date2 is not null) and (@pay_cur is not null)
  begin
    select @date2 = dateadd(dd, -1, @date2)
    if @type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
    begin
    .CONVERT_SUM(@cur,     @rur, @date1, 0, @sum, @sum1, @rate, @rate_date)
    .CONVERT_SUM(@pay_cur, @rur, @date2, 0, @sum, @sum2, @rate, @rate_date)
     update #rep set
      curs_diff = @sum2 - @sum1
      where id = @id and item_id = @item_id
        and type_code=@type_code
    end
    /*if @type_code in ('REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT')
    begin
    .CONVERT_SUM(@pay_cur,     @rur, @date2, 0, @sum, @sum1, @rate, @rate_date)
    .CONVERT_SUM(@cur,         @rur, @date1, 0, @sum3, @sum2, @rate, @rate_date)
    end*/
if @type_code in ('REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT')
    begin
    update #rep set
    curs_diff = @sum - @sum3
      where id = @id and item_id = @item_id
        and type_code=@type_code
    end
 end

--select @sum1 as sum1, @sum2 as sum2, @sum as sum, @sum3 as sum3, curs_diff, item_id,@type_code     --test
--from #rep set

  fetch curs4 into @id, @item_id, @type_code
end

close curs4
.DEALLOCATE curs4

update #rep set
sysdate=(select max(h.action_date) from t_actions_history h where h.doc_id=#rep.item_id)

--====отладка
--select t_items.item_name,#rep.* from #rep inner join t_items on t_items.id = #rep.item_id

--добавлено 'REAL_PAYMENT_OUT','REAL_PAYMENT_IN' поскольку http://support.systems.ru/mantis/view.php?id=564  -пункт3--Indi

select
acc_num_eff,
(select a.acc_name from t_accounts a where a.id=#rep.acc_id) as acc_name,
acc_last_num_eff,
(select a.acc_name from t_accounts a where a.id=#rep.acc_last_id) as acc_last_name,
op_date,
in_no,--sysdate,item_id,
type_name,
isnull(saldo_b,0),
isnull((case
     when ((upper(type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
 or exists(select 1 from t_paydocs p, t_purposes pu
         where p.purpose_id = pu.id  and p.id = #rep.item_id and pu.code in (10,15)))

           and not(exists(select 1 from tb_portfolio_section,tb_broker_change
           where tb_portfolio_section.id=#rep.portf_sub_id
           and tb_broker_change.id=tb_portfolio_section.investor_id)
   and exists(select 1 from tb_baysale_docitems
           where tb_baysale_docitems.id=#rep.item_id
           and tb_baysale_docitems.character_id=2)))
            then op_sum_in
     else null
end),0) as deal_sum_in,
isnull((case
     when ((upper(type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
 or exists(select 1 from t_paydocs p, t_purposes pu
         where p.purpose_id = pu.id  and p.id = #rep.item_id and pu.code in (10,15)))
 and not(exists(select 1 from tb_portfolio_section,tb_broker_change
           where tb_portfolio_section.id=#rep.portf_sub_id
           and tb_broker_change.id=tb_portfolio_section.investor_id)
   and exists(select 1 from tb_baysale_docitems
           where tb_baysale_docitems.id=#rep.item_id
           and tb_baysale_docitems.character_id=2)))
            then null
     else op_sum_in
end),0) as other_sum_in,
isnull((case
     when ((upper(type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
 or exists(select 1 from t_paydocs p, t_purposes pu
         where p.purpose_id = pu.id  and p.id = #rep.item_id and pu.code in (10,15)))
           and not(exists(select 1 from tb_portfolio_section,tb_broker_change
           where tb_portfolio_section.id=#rep.portf_sub_id
           and tb_broker_change.id=tb_portfolio_section.investor_id)
   and exists(select 1 from tb_baysale_docitems
           where tb_baysale_docitems.id=#rep.item_id
           and tb_baysale_docitems.character_id=2)))
            then op_sum_out
     else null
end),0) as deal_sum_out,
isnull((case
     when ((upper(type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
 or exists(select 1 from t_paydocs p, t_purposes pu
         where p.purpose_id = pu.id  and p.id = #rep.item_id and pu.code in (10,15)))

           and not(exists(select 1 from tb_portfolio_section,tb_broker_change
           where tb_portfolio_section.id=#rep.portf_sub_id
           and tb_broker_change.id=tb_portfolio_section.investor_id)
   and exists(select 1 from tb_baysale_docitems
           where tb_baysale_docitems.id=#rep.item_id
           and tb_baysale_docitems.character_id=2)))
            then null
     else op_sum_out
end),0) as other_sum_out,
isnull(saldo_e,0),
op_name,
first_doc,
purpose_name,
isnull(com_br,0),
isnull(com_other,0),
investor_name,
bank_account_name,
portfolio_name,
section_name,
asset_place_name,
subconto,
isnull(saldo_sub_b,0),
isnull(saldo_acc_b,0),
isnull(saldo_sub_e,0),
isnull(saldo_acc_e,0),
(case when upper(type_code) in ('REAL_PAYMENT_IN','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR')
then curs_diff else null end) as curs_diff_in,
(case when upper(type_code) in ( 'REAL_PAYMENT_OUT','DEALINGS_VIA_BROKER','DOG_DEALINGS_BAY_VBR')
then curs_diff else null end) as curs_diff_out
from #rep
order by isnull(acc_num_eff,''), isnull(acc_last_num_eff,''), isnull(subconto,''), op_date, sysdate, isnull(in_no,'')

end