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,
  @investor_id_s varchar(30) = null
-- 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 numeric
if @investor_id_s is NULL or @investor_id_s=''
select @investor_id = null
else select @investor_id = convert(numeric,@investor_id_s)

declare @stock_id numeric
if @stock_id_s is NULL or @stock_id_s=''
   select @stock_id = null
else 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 @subc_eff_num varchar(255)

select @date_b = .DATE_NORM(@date_e_d, B)
select @date_e = .DATE_NORM(@date_e_d, E)
----------------------------создаем временные таблицы--------------
create table #result(
  id numeric IDENTITY,
  item_id numeric null,
  op_name varchar(255) null,
  op_date 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,
  com money null,
  stock_id numeric null,
  investor_id numeric(18,0) null,
  stock_name varchar(255) null,
  bank_account_id numeric null,
  stock_qty money null,
  foundation_id numeric null,
  client_name varchar(255) null
)
----------находим плана и партнера----------
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)
---------------------находим счет 100--------
declare @acc100_id numeric
declare @acc100_num_eff varchar(255)

--=================================================================================
----------------------------------ПЛАТЕЖИ ИНЫЕ-------------------------------------
--=================================================================================
---------------------ЦН=37,39 Зачисление и списание ДС с клиента,перевод вн.инв-------------------
insert into #result (item_id,op_name,op_date,in_no,op_sum, first_doc, investor_id)
select p.id, pu.name, d.in_date, d.in_no, p.doc_sum,
(case when b.acc_type_id = 4 then 'Отчет брокера от '+convert(varchar,dd.in_date,103)
      when b.acc_type_id =1 then 'Выписка по расчетному счету от '+convert(varchar,dd.in_date,103) end),
     p.client_contract_id
from t_purposes pu, t_paydocs p, td_depo_docs d, td_depo_docs dd, t_bank_accounts b
where d.in_date <= @date_e
  and pu.code in (37,39,70) ---(52, 45 - см. ниже)
  and p.id = d.id
  and p.purpose_id = pu.id
  and (p.client_contract_id = @investor_id or @investor_id is null)
  and dd.id = p.to_partner_acc_id
  and b.id = p.to_partner_acc_id
-----------------погашение купона и ЦБ, начиление дивидендов! ЦН = 52, 45, 47
insert into #result (first_doc, item_id,op_name,purpose_name, op_date, in_no,op_sum,stock_qty, foundation_id, price, investor_id )
select 'Уведомление депозитария '+(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),
ts_sd_stock_moves.id, pu.name, pu.name,d.in_date, d.in_no, l.list_sum, l.stock_qty, l.item_id,
(case when isnull(l.stock_qty,0) <> 0 then l.list_sum/l.stock_qty
     else null end), l.investor_id
from tb_list_stock_redemption l, ts_sd_stock_moves, td_depo_docs d,t_purposes pu, t_items i
where ts_sd_stock_moves.id = l.item_id
  and d.id = ts_sd_stock_moves.id
  and pu.code in (52, 45, 47,48)
  and pu.id = d.perpose_id
  and d.in_date <= @date_e
  and i.id = d.id
  and (l.investor_id = @investor_id or @investor_id is null)

/*select p.id, pu.name, d.in_date, d.in_no, l.list_sum, l.stock_qty, l.item_id,
(case when isnull(l.stock_qty,0) <> 0 then l.list_sum/l.stock_qty
     else null end), l.investor_id
from t_purposes pu, t_paydocs p, td_depo_docs d, tb_list_stock_redemption l
where p.foundation_id = l.id
  and (l.investor_id = @investor_id or @investor_id is null)
  and d.in_date <= @date_e
  and pu.code in (52, 45, 47)
  and p.id = d.id
  and p.purpose_id = pu.id
--  and (l.stock_id = @stock_id or @stock_id is null)*/
----------------Начисление % по депозиту ЦН=120
insert into #result (item_id,op_name, op_date, in_no,op_sum, foundation_id, investor_id )
select p.id, pu.name, d.in_date, d.in_no, m.list_sum,  m.item_id, m.contract_id
from t_purposes pu, t_paydocs p, td_depo_docs d, tb_list2money_oper m
where p.foundation_id = m.id
  and (m.contract_id = @investor_id or @investor_id is null)
  and d.in_date <= @date_e
  and pu.code = 120
  and p.id = d.id
  and p.purpose_id = pu.id
----------------------------перевод ДС-----------------------------------
--t_paydocs.to_partner_acc_id  :  Целевой р/с
--сделано выше
--=================================================================================
----------------------ДЕПОЗИТАРНЫЕ ОПЕРАЦИИ ИНЫЕ-----------------------------------
--=================================================================================
select @subc_str_bal= ''
.SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @investor_id, Y)

insert into #result (item_id,op_name, op_date,in_no,op_sum,/*price,*/ stock_qty, investor_id)
select o.item_id, o.op_name,-- ol.subc_eff_num,
       o.op_date, d.in_no,
       isnull((ol.sum_bal * ol.deb_or_cred),0) as op_sum,/*isnull(m.stock_price,0),*/
       m.stock_qty, m.client_contract_id
from t_operations o,t_oper_list ol, t_accounts a, td_stock_moves m, t_purposes pu, td_depo_docs d
where o.id = ol.head_id
  and a.acc_num_eff like '1.0.0'
  and ol.acc_id = a.id
--  and ol.op_date >=@date_b
  and ol.op_date<= @date_e
  and ol.partner2plan_id = @plan2partner_id
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and o.item_id = m.id
  and m.id = d.id
  and m.is_dogovor_id = pu.id
  and pu.code in (29,/*93,*/32,150)---(????-50,66,94)  --not in (27, 28, 43, 108,109, 30)  --

delete #result where isnull(op_sum,0) =0 and isnull(stock_qty,0) = 0 and isnull(doc_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 ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT','MONEY_MOVE_COMMISSION_2','MONEY_MOVE2INVESTORS') then 'Выписка по расчетному счету от '+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')
                                  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')
                                  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)
      else 'Не задан'
end),
  purpose_name=(case
      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)
      when upper(t.type_code) in ('STOCK_MOVE','STOCK_TRANSFER','STOCK_TRANSFER_OTHER_DEPO',
                                  'D_STOCK_IN','D_STOCK_IN_MOVE','D_STOCK_IN_OTHER_DEPO',
                                  'D_STOCK_OUT','D_STOCK_OUT_MOVE','D_STOCK_OUT_OTHER_DEPO') then (select purp.name from
                                   t_purposes purp,td_stock_moves p where p.id=d.id and purp.id=p.is_dogovor_id)
--      when upper(t.type_code) in ('D_STOCK_ZALOG') then 98
--      when upper(t.type_code) in ('TAKING_CHARGE_ZALOG') then 99
      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
  and (first_doc is null or purpose_name is null)

----------------------платежи фонда--------------------------
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 client_name = p.partner_name
from tb_contracts c, t_partners p
where c.id = investor_id
  and c.client_id = p.id

--select * from #result --test

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,
  #result.price,
  #result.stock_qty,
  #result.first_doc,
  #result.purpose_name,
  #result.client_name as investor_name,
  #result.stock_name,
  isnull(#result.com,0) as com,
  #result.purpose_code
from #result
order by op_date,in_no

end