create procedure dbo.%PROC% @sid varchar(30) = null
as

declare @id numeric

if @sid='' select @sid=null
select @id=convert(numeric,@sid)


create table #lines (
 row int not null,
 row_name varchar(255) not null,
 detail_id numeric null,
 row_code varchar(10) null,
 sum_beg money null,
 sum_end money null,
 asset_part decimal(18,8) null,
 period decimal(18,8) null
)

insert into #lines (row,row_name,row_code)
select 50,'Имущество, составляющее паевой инвестиционный фонд',null
union select 100,'Денежные средства на банковских счетах, всего','010'
union select 105,'в том числе:',null
union select 110,'- в рублях','011'
union select 120,'- в иностранной валюте','012'
union select 200,'Денежные средства в банковских вкладах, всего','020'
union select 205,'в том числе:',null
union select 210,'- в рублях','021'
union select 220,'- в иностранной валюте','022'
union select 300,'Ценные бумаги российских эмитентов, имеющие признаваемую котировку, всего','030'
union select 305,'в том числе:',null
union select 310,'- акции','031'
union select 320,'- облигации','032'
union select 400,'Ценные бумаги российских эмитентов, не имеющие признаваемую котировку, всего','040'
union select 405,'в том числе:',null
union select 410,'- акции','041'
union select 420,'- облигации','042'
union select 430,'- векселя','043'
union select 440,'- иные ценные бумаги','044'
union select 500,'Дебиторская задолженность, в том числе:','050'
union select 510,'- средства, переданные профессиональным участникам рынка ценных бумаг','051'
union select 520,'- дебиторская задолженность по сделкам купли-продажи имущества','052'
union select 530,'- дебиторская задолженность по процентному (купонному) доходу по банковским вкладам и ценным бумагам','053'
union select 540,'- прочая дебиторская задолженность','054'
union select 600,'Инвестиционные паи паевых инвестиционных фондов','060'
union select 700,'Ценные бумаги иностранных эмитентов, всего, в том числе','070'
union select 710,'- ценные бумаги иностранных государств','071'
union select 720,'- ценные бумаги международных финансовых организаций','072'
union select 730,'- акции иностранных акционерных обществ','073'
union select 740,'- облигации иностранных коммерческих организаций','074'
union select 800,'Доли в российских обществах с ограниченной ответственностью','080'
union select 900,'Доходные вложения в материальные ценности, всего, в том числе','090'
union select 910,'- объекты недвижимого имущества, кромя строящихся и реконструируемых объектов','091'
union select 920,'- строящиеся и реконструируемые объекты недвижимого имущества','092'
union select 930,'- имущественные права на недвижимое имущество','093'
union select 940,'- проектно-сметная документация','094'
union select 1000,'Итого имущество: (строки 010 + 020 + 030 + 040 + 050 + 060 + 070 + 080 + 090)','100'
union select 1050,'Обязательства, исполнение которых осуществляется за счет имущества, составляющего паевой инвестиционный фонд',null
union select 1100,'Кредиторская задолженность','110'
union select 1200,'Резервы на выплату вознаграждений','120'
union select 1300,'Инвестиционные паи',130
union select 1400,'Итого обязательства: (строки 110 + 120 + 130)','140'


--------------------------------------------------------------------------------------------



declare
 @plan_id numeric,
 @fund_id numeric,

 @acc011_id numeric,
 @acc081_id numeric,
 @acc51_id numeric,
 @acc581_id numeric,
 @acc582_id numeric,
 @acc583_id numeric,
 @acc551_id numeric, --sys=76.5.4
 @acc7651_id numeric,
 @acc7652_id numeric,
 @acc7653_id numeric,
 @acc7655_id numeric,
 @acc7610_id numeric,
 @acc7612_id numeric,
 @acc7613_id numeric,
 @acc7615_id numeric,
 @acc7616_id numeric,
 @acc861_id numeric,--sys=96.1
 @acc961_id numeric,--sys=89.1
 @acc963_id numeric,--sys=89.3
 @acc969_id numeric,
 @acc57_id numeric

select @plan_id=.ARG_PLAN('PIF_ACCOUNT')
select @acc011_id = .SD_ACCID_FROM_EFFNO(@plan_id,'01.1')
select @acc081_id = .SD_ACCID_FROM_EFFNO(@plan_id,'08.1')
select @acc51_id = .SD_ACCID_FROM_EFFNO(@plan_id,'51')
select @acc581_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @acc582_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.2')
select @acc583_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.3')
select @acc551_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.4')
select @acc7651_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.1')
select @acc7652_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.2')
select @acc7653_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.3')
select @acc7655_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.5')
select @acc7610_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.10')
select @acc7612_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.12')
select @acc7613_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.13')
select @acc7615_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.15')
select @acc7616_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.16')
select @acc861_id = .SD_ACCID_FROM_EFFNO(@plan_id,'96.1')
select @acc961_id = .SD_ACCID_FROM_EFFNO(@plan_id,'89.1')
select @acc963_id = .SD_ACCID_FROM_EFFNO(@plan_id,'89.3')
select @acc969_id = .SD_ACCID_FROM_EFFNO(@plan_id,'96.9')
select @acc57_id = .SD_ACCID_FROM_EFFNO(@plan_id,'57')

--Метрополь
declare @acc7659_id numeric
select @acc7659_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.9')
---------------


declare
 @class_stock_type_id numeric,
 @class_emitent_type_id numeric,
 @class_asset_type_id numeric,

 @value_notgos_owner_id numeric,
 @value_foreign_owner_id numeric,
 @value_pifpay_stock_id numeric,
 @value_oblig_stock_id numeric,
 @value_stock_stock_id numeric,
 @value_veks_stock_id numeric,
 @value_building_id numeric,
 @value_other_id numeric

select @class_stock_type_id=max(id) from t_nclass_types where upper(class_code)='STOCK_TYPE'
select @class_emitent_type_id=max(id) from t_nclass_types where upper(class_code)='STOCK_EMITENT_TYPE'
select @class_asset_type_id=max(id) from t_nclass_types where upper(class_code)='ASSET_TYPE_OTHER'

select @value_notgos_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_NOTGOS_PARTNER'
select @value_foreign_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_FOREIGN_PARTNER'

select @value_pifpay_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='PIFPAY'
select @value_oblig_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='OBLIG'
select @value_stock_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='STOCK'
select @value_veks_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='VEKS'

select @value_building_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='BUILDING'
select @value_other_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='OTHER'


--*********************************** INFORMATION ********************************************

declare
 @is_end int, --if end_of_year then 1, if begin_of_year then 0
 @srez_e_id numeric, --end_of_year_srez
 @srez_b_id numeric, --begin_of_year_srez
 @srez_e_date datetime, --end_of_year_srez_date
 @srez_b_date datetime, -- begin_of_year_srez_date
 @srez_id numeric, -- for cycle only
 @srez_date datetime -- for cycle only

select @srez_e_id=@id

select @srez_b_id=startsrez_id,
       @srez_e_date=srez_date,
       @fund_id=fond_id
 from tp_pif_srez where tp_pif_srez.id = @srez_e_id

if @srez_b_id is null
  .EXIT_MESSAGE('Не задан срез на начало года')

select @srez_b_date=srez_date
 from tp_pif_srez where tp_pif_srez.id = @srez_b_id


create table #assets (
 acc_id numeric not null,
 asset_id numeric not null,
 asset_sum money null,
 stock_qty decimal(18,8) null,
 is_rate int null,
 asset_type_id numeric null, -- значение класс.,определяющего тип актива
 owner_type_id numeric null, -- значение класс.,определяющего принадлежность актива,
 stock_emiss_no varchar(100) null, -- detailed info = stock_emis_no
 qty_in_emission decimal(18,0) null, -- detailed info = emission stock qty
 emitent varchar(255) null, -- detailed info = emitent
 asset_type varchar(100) null, --detailed info = вид актива
 stock_cancel_date datetime null, --detailed info = дата погашения
 row int null
)


create table #details(
 row int not null,
 detail_id numeric null,
 detail varchar(255) null,
 sum_beg money null,
 sum_end money null,
 part_beg decimal(18,8) null,
 part_end decimal(18,8) null,
 period decimal(18,8) null
)

-------------------------------------------------------------------------------------
select @srez_id=@srez_e_id, @srez_date=@srez_e_date
select @is_end=1
--------------------------------------------- CYCLE -----------------------------
while @is_end>=0 -- is evaluating 2 times: =1 (end_of_year) and =0 (begin_of_year)
 begin

---------------------------------
-- stocks and others
----------------------------------

-- stock
insert into #assets (
 acc_id, asset_id, asset_sum, stock_qty,
 is_rate,
 asset_type_id,
 owner_type_id
)
select
 p.acc_id, p.stock_id, p.stock_sum, p.stock_qty,
 (case when isnull(p.is_rate,0)=1 and isnull(p.stock_rate,0)<>0
            and p.rate_date is not null --and p.rate_date=@srez_date
        then 1
       else 0 end),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_stock_type_id),0),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_emitent_type_id),0)
  from tp_pif_srez_portfolio p
 where p.srez_id=@srez_id
   and p.acc_id=@acc581_id
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO')

-- other assets
insert into #assets (
 acc_id, asset_id, asset_sum, stock_qty,
 is_rate,
 asset_type_id
 )
select
 p.acc_id, p.stock_id, p.stock_sum, p.stock_qty,
 0,
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_asset_type_id),0)
  from tp_pif_srez_portfolio p
 where p.srez_id=@srez_id
   and p.acc_id in (@acc011_id,@acc081_id,@acc583_id)
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')


update #assets set
 row=case
-- ЦБ котируемые - rated stocks
      when is_rate=1 and owner_type_id<>@value_foreign_owner_id and asset_type_id=@value_stock_stock_id
      then 310
      when is_rate=1 and owner_type_id<>@value_foreign_owner_id and asset_type_id=@value_oblig_stock_id
      then 320

-- ЦБ некотируемые -- not rated stocks
      when is_rate=0 and owner_type_id<>@value_foreign_owner_id and asset_type_id=@value_stock_stock_id
      then 410
      when is_rate=0 and owner_type_id<>@value_foreign_owner_id and asset_type_id=@value_oblig_stock_id
      then 420
      when is_rate=0 and owner_type_id<>@value_foreign_owner_id and asset_type_id=@value_veks_stock_id
      then 430
      when is_rate=0 and owner_type_id<>@value_foreign_owner_id
           and asset_type_id not in (@value_stock_stock_id,@value_oblig_stock_id,@value_veks_stock_id)
           and isnull(asset_type_id,0)!=0
      then 440
-- Pays
      when owner_type_id=@value_notgos_owner_id and asset_type_id=@value_pifpay_stock_id
      then 600
-- Foreign stocks, realty and parts (!!! 710,720,920,940 - can't find, additional classification is needed)
      when owner_type_id=@value_foreign_owner_id and asset_type_id=@value_oblig_stock_id
      then 740
      when owner_type_id=@value_foreign_owner_id and asset_type_id=@value_stock_stock_id
      then 730
      when acc_id=@acc011_id and asset_type_id=@value_building_id
      then 910
      when acc_id=@acc081_id and asset_type_id=@value_building_id
      then 930
 /*     when acc_id=@acc583_id and asset_type_id=@value_other_id-----???????????????
      then 800*/
    end





select @value_pifpay_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='PIFPAY'
select @value_oblig_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='OBLIG'
select @value_stock_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='STOCK'
select @value_veks_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='VEKS'

select @value_building_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='BUILDING'
select @value_other_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='OTHER'



--getting detail information----------------------------------
update #assets set
 stock_emiss_no=e.emiss_statereg_no,
 qty_in_emission=e.qty_in_emission,
 emitent=(select ei.emi_name from td_emitent_info ei where ei.id=e.emmitent_id),
 asset_type=case
             when asset_type_id=@value_pifpay_stock_id
             then 'Пай'
             when asset_type_id=@value_oblig_stock_id
             then 'Облигация'
             when asset_type_id=@value_stock_stock_id
             then 'Акция'
             when asset_type_id=@value_veks_stock_id
             then 'Вексель'
             when asset_type_id=@value_building_id
             then 'Недвижимость'
             when asset_type_id=@value_other_id
             then 'Проч. актив'
            end,
 stock_cancel_date=e.stock_cancel_date
from td_stock_emis e
where e.id=#assets.asset_id and
      .ITEMS_EXISTS_BY_TYPE(#assets.asset_id,'TD_STOCK_EMIS')

update #assets set
 stock_emiss_no=isnull(r.realty_other_assets_name,'?')+', '+
                isnull(r.realty_other_assets_code,'?')+', '+isnull(r.reg_no,'?'),
 emitent=(select tp.partner_name from t_partners tp where tp.id=r.owner_id)
from t_realty_other_assets r
where r.id=#assets.asset_id and
      .ITEMS_EXISTS_BY_TYPE(#assets.asset_id,'REALTY_OTHER_ASSETS')


insert #details(row,detail_id,detail,sum_beg,period)
select a.row,a.asset_id,
       case
        when a.row in (310,320,410,420,430,440,710,730,740)
        then isnull(a.emitent,'X')+' '+isnull(('№ гос. рег.: '+a.stock_emiss_no),'X') +' '+isnull(('('+a.asset_type+')'),'')
        when a.row in (910,930)
        then isnull(a.stock_emiss_no,'X')
        when a.row=600 -- паи
        then isnull(a.emitent,'X')+' '+isnull((select stock_name from td_stock_emis where id=a.asset_id),'X')
        end,
       isnull(a.asset_sum,0),
       isnull(convert(decimal(18,8),datediff(day,@srez_e_date,a.stock_cancel_date))/365,0) --срок от даты отчета до даты погашения "в годах"
from #assets a
where a.row in (310,320,410,420,430,440,600,710,730,740,910,930)



-------------------------------------------------------------

-- to report
update #lines set
 sum_beg=(select sum(isnull(#assets.asset_sum,0))
            from #assets where #assets.row=#lines.row and #assets.row is not null)

--030
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
           where row in (310,320))
where row=300

--040
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
           where row in (410,420,430,440))
where row=400

--070
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
           where row in (710,720,730,740))
where row=700

--090
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
           where row in (910,920,930,940))
where row=900


----------------
-- Money
----------------

-- 010 - at bank accounts
insert #details(row,detail_id,detail,sum_beg)
select 110,tb.id,isnull('Расчетный счет в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@srez_id
   and p.acc_id=@acc51_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)='RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

insert #details(row,detail_id,detail,sum_beg)
select 120,tb.id,isnull('Расчетный счет в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@srez_id
   and p.acc_id=@acc51_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)<>'RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

update #lines set
 sum_beg=(select sum(isnull(d.sum_beg,0))
            from #details d
           where d.row=#lines.row)
 where row in (110,120)

update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines where row in (110,120))
 where row=100


-- 020 - deposit
insert #details(row,detail_id,detail,sum_beg)
select 210,tb.id,isnull('Депозит в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@srez_id
   and p.acc_id=@acc551_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)='RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

insert #details(row,detail_id,detail,sum_beg)
select 220,tb.id,isnull('Депозит в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@srez_id
   and p.acc_id=@acc551_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)<>'RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

--добавляем сроки для депозитов
update #details
 set period=isnull(convert(decimal(18,8),c.qty_day_depo)/isnull(c.qty_day_yy,365),0)
from tb_contracts c, tb_broker2exchange b2e, t_bank_accounts tb
where #details.row in (210,220) and
      c.id=b2e.ext_broker_deal and
      tb.asset_place_id=b2e.id and
      tb.id=#details.detail_id


update #lines set
 sum_beg=(select sum(isnull(d.sum_beg,0))
            from #details d
           where d.row=#lines.row)
 where row in (210,220)

update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines where row in (210,220))
 where row=200


----------------
-- Задолженность-debts
----------------

-- 050 - деб.задолженность
update #lines set
 sum_beg=(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
-----из-за 053-------
+isnull((select isnull(sum(isnull(p.coupon_sum,0)),0)
            from tp_pif_srez_portfolio p
            where p.srez_id=@srez_id
              and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO')),0)
----------
 where row=500

-- kashuba_av begin Для Webinvest добавляем в стоки 050 и 054 дебет 57 счета
if rtrim(ltrim(.GET_CONST('CLIENT_CODE'))) = 'WEBINVEST'
begin
  update #lines
   set sum_beg=isnull(sum_beg,0)
              +isnull((select sum(isnull(b.saldo_deb,0))
                           from tp_pif_srez_balance b
                              where b.srez_id = @srez_id
                                 and b.acc_id = @acc57_id
                                  and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_BALANCE')),0)
  where row=500
end
-- kashuba_av end

-- 051 - проф.участники - profi
update #lines set
 sum_beg=(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and b.acc_id in (@acc551_id,@acc7659_id) --76.5.9 - только Метрополь
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=510


-- 052 - покупка имущества - asset buying
update #lines set
 sum_beg=(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and b.acc_id in (@acc7651_id,@acc7652_id,@acc7653_id,@acc7655_id,
                              @acc7612_id,@acc7613_id,@acc7615_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=520

-- 053 - купоны и % по депозитам - coupon and deposit percents
update #lines set
 sum_beg=(select isnull(sum(isnull(p.coupon_sum,0)),0)
            from tp_pif_srez_portfolio p
            where p.srez_id=@srez_id
              and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO'))
--Метрополь
+isnull((select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and b.acc_id=@acc7610_id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')),0)
 where row=530

-- 054 - проч.деб.задолженность - other debts

update #lines set
 sum_beg=(select isnull(sum_beg,0) from #lines where row=500)-
           (select sum(isnull(sum_beg,0)) from #lines where row in (510,520,530))
where row=540

--Детали по Деб. задолженности--------------------------------------------

-- 051 - проф. участники
insert #details(row,detail_id,detail,sum_beg)
select 510,null,null, isnull(sum(isnull(b.saldo_deb,0)),0)
 from tp_pif_srez_balance b
 where b.srez_id=@srez_id
   and b.acc_id in (@acc551_id,@acc7659_id) --76.5.9 - только Метрополь
   and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')

update #details
 set detail_id=b.partner_id,
     detail=isnull('задолженность '+tp.partner_name,'')
from tp_pif_srez_balance b, t_partners tp
where row=510 and detail_id is null and detail is null
   and b.srez_id=@srez_id
   and b.acc_id=@acc551_id --76.5.4 - только Метрополь
   and b.partner_id=tp.id
   and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')

-- 052 - покупка имущества
insert #details(row,detail_id,detail,sum_beg)
select 520,b.partner_id,isnull('задолженность '+tp.partner_name,''),isnull(b.saldo_deb,0)
 from tp_pif_srez_balance b, t_partners tp
 where b.srez_id=@srez_id
   and b.acc_id in (@acc7651_id,@acc7652_id,@acc7653_id,@acc7655_id,
                              @acc7612_id,@acc7613_id,@acc7615_id)
   and b.partner_id=tp.id
   and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')


---------------------------------------------------------------------------


-- 100 - Итого имущество
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
             where row in (100,200,300,400,500,600,700,800,900))
where row=1000



-- Обязательства
-- 110 - кред. задолженность
update #lines set
 sum_beg=(select sum(isnull(b.saldo_cred,0))
            from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=1100

-- kashuba_av begin Для Webinvest добавляем в стоку 100 кредит 57 счета
if rtrim(ltrim(.GET_CONST('CLIENT_CODE'))) = 'WEBINVEST'
begin
update #lines
   set sum_beg=isnull(sum_beg,0)
              +isnull((select sum(isnull(b.saldo_cred,0))
                   from tp_pif_srez_balance b
                  where b.srez_id = @srez_id
                    and b.acc_id = @acc57_id
                    and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_BALANCE')),0)
 where row = 1100
end
-- kashuba_av end

-- 120 - резервы на выплату вознагр.
update #lines set
 sum_beg=(select isnull(sum(isnull(b.saldo_cred,0)),0)
           from tp_pif_srez_balance b
           where b.srez_id=@srez_id
             and b.acc_id in (@acc961_id,@acc963_id,@acc969_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_BALANCE'))
 where row=1200

-- 130 - инв. паи
update #lines set
 sum_beg=(select isnull(scha_sum,0) from tp_pif_srez where id=@srez_id)
 where row=1300

-- 140 - Итого обязательства
update #lines set
 sum_beg=(select sum(isnull(sum_beg,0)) from #lines
             where row in (1100,1200,1300))
where row=1400


--assets that don't have needed classification--------------------------------------------

if exists(select 1 from #assets where row is null)
  insert #lines (row,row_name,row_code,sum_beg)
  select 30000,
         'Актив: '+isnull(ti.item_name,'-')+
         ', эмитент/владелец: '+isnull(a.emitent,'-')+
         ', №: '+isnull(a.stock_emiss_no,'-'),
         null,
         a.asset_sum
  from #assets a, t_items ti
  where a.row is null and
        ti.id=a.asset_id
--------------------------------------------------------------------------------------------

-- finish of while cycle---------------------------------------------------------
  if @is_end=1
   begin
    update #lines set sum_end=sum_beg
    update #lines set sum_beg=0

    truncate table #assets

    update #details set sum_end=sum_beg
    update #details set sum_beg=null

    select @srez_id=@srez_b_id, @srez_date=@srez_b_date

   end  --if------------------------------------------------------

  select @is_end=@is_end-1

end --end_of while @is_end...------------------------------------------------




declare @itogo_sum_beg money,
        @itogo_sum_end money

select @itogo_sum_beg=sum_beg,
       @itogo_sum_end=sum_end
 from #lines
 where row=1000

/*
--для разбивки по активам с суммой более 5%
update #details
 set part_beg=round(d.sum_beg/@itogo_sum_beg,2),
     part_end=round(d.sum_end/@itogo_sum_end,2)
*/

-- adding details---------------


----q
if @itogo_sum_beg = 0 select @itogo_sum_beg = 1
if @itogo_sum_end = 0 select @itogo_sum_end = 1

----q


-- adding details---------------

insert #lines(row,row_name,detail_id,sum_beg,period)
 select d.row,d.detail,d.detail_id,d.sum_beg,d.period
  from #details d
  where d.sum_beg is not null and
        d.row in (320,420,430,740) and
        isnull(d.sum_beg,0)/isnull(@itogo_sum_beg,1)>=0.05

  update #lines set
   sum_end=d.sum_end
  from #details d
  where d.detail_id=#lines.detail_id and
        d.row=#lines.row and
        d.row in (320,420,430,740) and
        isnull(d.sum_end,0)/isnull(@itogo_sum_end,1)>=0.05


  insert #lines(row,row_name,detail_id,sum_end,period)
  select d.row,d.detail,d.detail_id,d.sum_end,d.period
   from #details d
   where d.sum_end is not null and
         d.row in (320,420,430,740) and
         isnull(d.sum_end,0)/isnull(@itogo_sum_end,1)>=0.05 and
         not exists(select 1 from #lines
                     where #lines.detail_id=d.detail_id)
--------------------------------

--строки для разбивки--------------------
declare @cur_row int,
        @cur_period decimal(18,8),
        @period int,
        @row_name varchar(255)

declare comments_cur_p cursor for
 select row, period from #lines
  where detail_id is not null
    and row in (320,420,430,740)

open comments_cur_p
fetch comments_cur_p into @cur_row, @cur_period
while .CURSOR_STATE=0
 begin






  select @period=case when isnull(@cur_period,0)=0 then -1
                      when @cur_period<1  then 0
                      when @cur_period>=1 and @cur_period<=3 then 1
                      when @cur_period>3 then 3
                 end

  select @row_name=case
                    when @cur_row in (320,420,430,740) and @period=-1 then 'В том числе с не заданным периодом погашения:'
                    when @cur_row in (320,420,430,740) and @period=0 then 'срок погашения менее года:'
                    when @cur_row in (320,420,430,740) and @period=1 then 'срок погашения от 1 года до 3 лет:'
                    when @cur_row in (320,420,430,740) and @period=3 then 'срок погашения более 3 лет:'
                 else ''
                   end


  if not exists(select 1 from #lines where row=@cur_row and period=@period)
   insert into #lines(row,row_name,period) values(@cur_row,@row_name,@period)

  fetch comments_cur_p into @cur_row, @cur_period
 end

close comments_cur_p
.DEALLOCATE comments_cur_p










/*

insert #lines(row,row_name,detail_id,sum_beg,period)
 select d.row,d.detail,d.detail_id,d.sum_beg,d.period
  from #details d
  where d.sum_beg is not null and
        d.sum_beg/isnull(@itogo_sum_beg,1)>=0.05

  update #lines set
   sum_end=d.sum_end
  from #details d
  where d.detail_id=#lines.detail_id and
        d.row=#lines.row and
        d.sum_end/isnull(@itogo_sum_end,1)>=0.05

  insert #lines(row,row_name,detail_id,sum_end,period)
  select d.row,d.detail,d.detail_id,d.sum_end,d.period
   from #details d
   where d.sum_end is not null and
         d.sum_end/isnull(@itogo_sum_end,1)>=0.05 and
         not exists(select 1 from #lines
                     where #lines.detail_id=d.detail_id)








--------------------------------


declare comments_cur cursor for
 select row, period from #lines
  where detail_id is not null
    and row in (210,220,310,410,440,600,710,720,730)

open comments_cur
fetch comments_cur into @cur_row, @cur_period
while .CURSOR_STATE=0
 begin

  select @period=case when isnull(@cur_period,0)=0 then -1
                      when @cur_period<1  then 0
                      when @cur_period>=1 and @cur_period<=3 then 1
                      when @cur_period>3 then 3
                 end

  select @row_name=case
                    when @cur_row in (210,220) and @period in (0,-1) then 'до 1 года'
                    when @cur_row in (210,220) and @period=1 then 'от 1 года до 3 лет'
                    when @cur_row in (210,220) and @period=3 then 'более 3 лет'
                    when @cur_row in (310,410,440,600,710,720,730) and @period=-1 then 'в том числе составляющее более 5% общей стоимости имущества:'
                    when @cur_row in (320,420,430,740) and @period=0 then 'До 1 года, в том числе составляющее более 5% общей стоимости имущества:'
                    when @cur_row in (320,420,430,740) and @period=1 then 'От 1 года до 3 лет, в том числе составляющее более 5% общей стоимости имущества:'
                    when @cur_row in (320,420,430,740) and @period=3 then 'Более 3 лет, в том числе составляющее более 5% общей стоимости имущества:'
                   end


  if not exists(select 1 from #lines where row=@cur_row and period=@period)
   insert into #lines(row,row_name,period) values(@cur_row,@row_name,@period)

  fetch comments_cur into @cur_row, @cur_period
 end

close comments_cur
.DEALLOCATE comments_cur

--------------------------------------------
*/
--Final Output!!!-------------
select
 row as row,
 row_name as row_name,
 row_code as row_code,
 sum_beg=
  case
   when sum_beg=0 then null
   else round(sum_beg/1000,3)
  end, --суммы в тысячах рублей!
 sum_end=
  case
   when sum_end=0 then null
   else round(sum_end/1000,3)
  end, --суммы в тысячах рублей!
 period as period
from #lines
order by row asc, row_code desc, period asc


drop table #lines
drop table #assets
drop table #details