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
--execute dbo.ap_naufor32_other_op_list;1 @broker_id_s = '21535000001051575', @date_b_d = '01/10/05', @date_e_d = '31/12/05', @investor_id_s='21535000002034982'
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_b_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,d.in_date,103)
      when b.acc_type_id =1 then 'Выписка по расчетному счету от '+convert(varchar,d.in_date,103) end),
    p.client_contract_id
from t_purposes pu, t_paydocs p, td_depo_docs d, t_bank_accounts b--, td_depo_docs dd
where d.in_date <= @date_e
  and d.in_date >= @date_b
  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
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 +', '+ 'Эмитент: '+
(select td_emitent_info.emi_name from td_emitent_info, td_stock_emis
  where td_emitent_info.id = td_stock_emis.emmitent_id
    and td_stock_emis.id = ts_sd_stock_moves.stock_id),

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)

-----------------начисление дивидендов---------------------------
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
( case when t_bank_accounts.acc_type_id = 1 then 'Выписка по расчетному счету '+ t_bank_accounts.bank_account_no
       when t_bank_accounts.acc_type_id = 1 then 'Отчет брокера '+ ' от '+convert(varchar,d.in_date,103) end),
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, t_bank_accounts
where ts_sd_stock_moves.id = l.item_id
  and d.id = ts_sd_stock_moves.id
  and pu.code = 47
  and pu.id = d.perpose_id
  and d.in_date <= @date_e
  and i.id = d.id
  and ts_sd_stock_moves.bank_acc_id  = t_bank_accounts.id
  and (l.investor_id = @investor_id or @investor_id is null)
--------------------конвертация ЦБ-----------------------
/*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 = 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 @subc_str_bal= ''
.SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @investor_id, Y)

insert into #result (first_doc, item_id,op_name,purpose_name,
op_date, in_no,op_sum,
stock_qty,
foundation_id, 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 m.id=sm.id and ds.id=sm.s_subacc_id and da.id=ds.depo_acc_id and p.id=da.organisation_id)*/
(select p.partner_short_name
from t_partners p,td_depo_accounts da,td_depo_subacc ds
where da.id=ds.depo_acc_id
  and p.id=da.organisation_id
  and l.depo_subacc_id = ds.id)+' от '+convert(varchar,d.in_date,103),
m.id, pu.name,
(pu.name + ', Эмитент: ' + (select td_emitent_info.emi_name from td_emitent_info, td_stock_emis
                             where td_emitent_info.id = td_stock_emis.emmitent_id
                               and td_stock_emis.id = os.item_id)) as purpose_name,
d.in_date, d.in_no, null,-- ol.sum_bal,
ol1.qty_sum,
/*(case when ol.deb_or_cred = 1 then m.stock_qty
      when ol.deb_or_cred = -1 then m.stock_new_qty end),*/
l.id, l.investor_id

from t_operations o,t_oper_list ol, t_oper_list ol1, t_oper_subconto os, t_subconto_types st
, t_items i, t_types t,t_accounts a, td_depo_docs d
, tb_list_stock_redemption l, ts_sd_stock_moves m,t_purposes pu
where o.id = ol.head_id
  and ol.partner2plan_id = @plan2partner_id
  and a.acc_num_eff like '2.10.3'+ '%'
  and ol.acc_id = a.id
  and ol.op_date >=@date_b
  and  ol.op_date<= @date_e
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and st.subc_code = 'STOCK_EMIS'
  and os.subconto_type_id = st.id
  and os.id = ol.id
  and o.item_id = l.id
  and i.id = m.id
  and i.type_id = t.id
  and m.id = l.item_id
  and d.id = m.id
  and pu.code = 48
  and pu.id = d.perpose_id
  and o.id = ol1.head_id
  and ol1.id <> ol.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,
       (case when pu.code = 93 then null else isnull((ol.sum_bal * ol.deb_or_cred),0) end) as op_sum,
       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
------------------------------депозиты------------------------
create table #bank_acc
(bank_acc_id numeric null,
 acc_type_id numeric null,
 bank_acc_no varchar(50) null)

insert into #bank_acc
select t_bank_accounts.id,t_bank_accounts.acc_type_id, t_bank_accounts.bank_account_no
from tb_contracts c, t_bank_accounts
where c.dog_type_id = 8
  and c.money_acc_id = t_bank_accounts.id
  and c.contract_id = @investor_id

select @subc_str_bal= ''
.SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @investor_id, Y)

insert into #result (item_id,op_name, op_date, op_sum, investor_id, purpose_name, in_no, first_doc )
select o.item_id, o.op_name, o.op_date, isnull((ol.sum_bal * ol.deb_or_cred),0) as op_sum,
(case when t.type_code = 'LIST_FOR_MONEY_REDEMPTION' then (select l.contract_id
                                                           from tb_list2money_oper l
                                                           where o.item_id = l.id)
      when t.type_code = 'REAL_PAYMENT_IN' then  (select p.client_contract_id
                                                   from t_paydocs p
                                                  where o.item_id = p.id)
end)
,(case when t.type_code = 'LIST_FOR_MONEY_REDEMPTION' then ('Групповая операция, Начисление % по депозиту'+', ' +
          (select t_banks.bank_name from t_banks, t_bank_accounts b where os.item_id = b.id and b.bank_id = t_banks.id))
      when t.type_code = 'REAL_PAYMENT_IN' then ('Входящий платеж, Начисление % по депозиту' + ', '+
          (select t_banks.bank_name from t_banks, t_bank_accounts b where os.item_id = b.id and b.bank_id = t_banks.id))
 end),
(case when t.type_code = 'LIST_FOR_MONEY_REDEMPTION' then (select d.in_no
                                                           from tb_list2money_oper l,
                                                                td_depo_docs d
                                                           where l.item_id = d.id
                                                             and o.item_id = l.id)
      when t.type_code = 'REAL_PAYMENT_IN' then (select d.in_no
                                                   from t_paydocs p, td_depo_docs d
                                                  where o.item_id = p.id
                                                    and p.id = d.id)
end) as in_no,
(case when t.type_code = 'LIST_FOR_MONEY_REDEMPTION' then 'Выписка по расчетному счету от '+
convert(varchar,(select d.in_date from tb_list2money_oper l, td_depo_docs d  where l.item_id = d.id and o.item_id = l.id),103)
      when t.type_code = 'REAL_PAYMENT_IN' then 'Выписка по расчетному счету от '+
convert(varchar,(select d.in_date from t_paydocs p, td_depo_docs d  where o.item_id = p.id and p.id = d.id),103)
 end) as first_doc

from t_operations o,t_oper_list ol, t_oper_subconto os, t_subconto_types st
, t_items i, t_types t
, t_accounts a
where o.id = ol.head_id
  and ol.partner2plan_id = @plan2partner_id
  and a.acc_num_eff like '2.10.4'+ '%'
  and ol.acc_id = a.id
  and i.id = o.item_id
  and ol.op_date >=@date_b
  and ol.op_date<= @date_e
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and st.subc_code = 'BANK_ACCOUNT'
  and os.subconto_type_id = st.id
  and os.item_id  in (select bank_acc_id from #bank_acc)
  and os.id = ol.id
  and i.type_id = t.id
---------------------------
--execute dbo.ap_naufor32_other_op_list;1 @broker_id_s = '21535000001051575', @date_b_d = '01/10/01', @date_e_d = '01/09/06', @investor_id_s='21535000002034982'
---------------------------
insert into #result (item_id,op_name, op_date, op_sum, investor_id, purpose_name, in_no)
select o.item_id, o.op_name, o.op_date, isnull((ol.sum_bal * ol.deb_or_cred),0) as op_sum,
p.client_contract_id,
(case when t.type_code = 'MONEY_MOVE_COMMISSION_2' then ('Перевод ДС, '+
            (select t_banks.bank_name from t_banks, t_bank_accounts b where os.item_id = b.id and b.bank_id = t_banks.id))
     when t.type_code = 'REAL_PAYMENT_IN' then ('Зачисление ДС, '+
            (select t_banks.bank_name from t_banks, t_bank_accounts b where os.item_id = b.id and b.bank_id = t_banks.id))
     when t.type_code = 'REAL_PAYMENT_OUT' then ('Снятие ДС, ' + ', '+
          (select t_banks.bank_name from t_banks, t_bank_accounts b where os.item_id = b.id and b.bank_id = t_banks.id))
end) as purpose_name,
 d.in_no
from t_operations o,t_oper_list ol, t_oper_subconto os, t_subconto_types st
, t_items i, td_depo_docs d, t_types t
, t_accounts a, t_paydocs p
where o.id = ol.head_id
  and ol.partner2plan_id = @plan2partner_id
  and a.acc_num_eff like '2.0.13'+ '%'
  and ol.acc_id = a.id
  and i.id = o.item_id
  and ol.op_date >=@date_b
  and  ol.op_date<= @date_e
  and ol.subc_eff_num like '%'+ @subc_str_bal + '%'
  and st.subc_code = 'BANK_ACCOUNT'
  and os.subconto_type_id = st.id
  and os. item_id  in (select bank_acc_id from #bank_acc)
  and os.id = ol.id
  and p.id = o.item_id
  and p.id = d.id
  and i.type_id = t.id
---------------------------------------------------------------

/*select t.*, #result.*  --test
from t_types t,t_items i,td_depo_docs d, #result
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)*/
------------------------------отсеить договора с классификатором 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=isnull(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=isnull(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)

/*----------------Начисление % по депозиту ЦН=120
insert into #result (item_id,op_name, op_date, in_no,op_sum, foundation_id, investor_id, purpose_name, first_doc)
select p.id, pu.name, d.in_date, d.in_no, m.list_sum,  m.item_id, m.contract_id, 'Начисление % по депозиту, '+ t_banks.bank_name,
'Выписка по расчетному счету от '+convert(varchar,d.in_date,103)
from t_purposes pu, t_paydocs p, td_depo_docs d, tb_list2money_oper m, t_bank_accounts, t_banks
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
  and m.bank_account_id = t_bank_accounts.id
  and t_bank_accounts.bank_id = t_banks.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 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