# Date: 01/09/06 # Version: 105 $DOC(CLIENT_REP_FOR_PROM_TRAD) name=Клиентский отчет для Пром-Трад class=1 product=ALD_SECUR name_formula=null remark_formula=null $STATE(NEW) name=Начальный class=1 $ENDSTATE $FORM(CLIENT_PROM_TRAD_REPO) name=Клиентский отчет - РЕПО class=5 filter=null target_state= uo=uo_list_rep dw=d_nau32_client_rep_list10 procedure=ap_client_prom_trad_rep_list10 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290973) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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 declare @section_id numeric declare @stock_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255), @subc_str_bal2 varchar(255) declare @acc142_id numeric, @acc132_id numeric /*@stock_qty_e money, @acc440_id numeric, @acc444_id numeric, @acc2013_id numeric, @acc2102_id numeric*/ declare @nkd_acc_id numeric declare @stock_rate decimal(18,8) declare @mess varchar(255) declare @bs_stock_id numeric declare @bs_stock_qty money, @nkd money declare @investor2portfolio_id numeric, @acc132_num_eff varchar(255),@acc142_num_eff varchar(255) declare @stock_id_bb numeric declare @stock_id_ee numeric create table #stock_rep ( -- id numeric IDENTITY not null, acc_id numeric null, stock_id numeric null, op_date datetime null, place_id numeric null, stock_s_b money null, obor_d money null, obor_c money null, stock_s_e money null, stock_qty_b money null, stock_qty_e money null, obor_qty_d money null, obor_qty_c money null, port_price money null, nkd money null ) --------------------------проверка на наличие входных данных---------------------- if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) select @broker_id = convert(numeric, @broker_id_s) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) if @stock_id is null begin select @stock_id_bb=-999999999999999998 select @stock_id_ee=999999999999999999 end select @date_b = convert(datetime, @date_b_d, 3) select @date_e = convert(datetime, @date_e_d, 3) --------------------------находим план и уч. организацию---------------------------- 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) ---------------------собираем строку субконто---------------------------------------- select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @dogovor_id, Y) select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y) ---------------------------------находим id счета 1.3.2---------------------------------------- select @acc132_id = .ACCID_FROM_EFFNO('BACK_MONEY','1.3.2') select @acc132_num_eff = acc_num_eff from t_accounts where id = @acc132_id --------------------------------формируем бухгалтерские итоги---------------------------- .TOTALS_QUERY(@cur_partner, @cur_plan, @date_b, @date_e,@acc132_id, @subc_str_bal, L, N, Y, D, P) --select * from #tmp_osv -- отладка delete #tmp_osv where (saldo_de = 0) and (saldo_ce = 0) and (q_saldo_de = 0) and (q_saldo_ce = 0) --------------------------------Вставляем в результирующую таблицу итоги по определенной ЦБ из табл. #tmp_osv ------- insert into #stock_rep( stock_id, stock_s_b, stock_s_e, obor_d, obor_c, stock_qty_b, stock_qty_e, obor_qty_d, obor_qty_c ) select distinct /*#tmp_osv.s1_id, sum(#tmp_osv.saldo_db) - sum(#tmp_osv.saldo_cb), sum(#tmp_osv.saldo_ce) - sum(#tmp_osv.saldo_de), --iii?aaeaii sum(#tmp_osv.obor_d), sum(#tmp_osv.obor_c), sum(#tmp_osv.q_saldo_db) - sum(#tmp_osv.q_saldo_cb), sum(#tmp_osv.q_saldo_ce) - sum(#tmp_osv.q_saldo_de), --поправлено sum(#tmp_osv.q_obor_d), sum(#tmp_osv.q_obor_c)*/ #tmp_osv.s1_id, (#tmp_osv.saldo_cb) - (#tmp_osv.saldo_db), --поправлено (#tmp_osv.saldo_ce) - (#tmp_osv.saldo_de), --поправлено (#tmp_osv.obor_d), (#tmp_osv.obor_c), (#tmp_osv.q_saldo_db) - (#tmp_osv.q_saldo_cb), (#tmp_osv.q_saldo_ce) - (#tmp_osv.q_saldo_de), --поправлено (#tmp_osv.q_obor_d), (#tmp_osv.q_obor_c) from #tmp_osv, t_items, t_types, t_states, td_depo_docs dd, tb_baysale_docitems, td_stock_emis -- tb_repo_params trp -- t_purposes tp where #tmp_osv.s1_id = tb_baysale_docitems.stock_id and (tb_baysale_docitems.client_contract_id=@dogovor_id or tb_baysale_docitems.client_contract_id is null) and t_items.id = tb_baysale_docitems.id and t_items.type_id = t_types.id and t_items.state_id = t_states.id and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') and t_states.state_code not in ('PODT', 'OBYAZ_END') and t_items.id = dd.id and dd.owner_id = @cur_partner and dd.in_date >= @date_b and dd.in_date < dateadd(dd, 1, @date_e) and #tmp_osv.s1_id = td_stock_emis.id and (td_stock_emis.id = @stock_id or td_stock_emis.id between @stock_id_bb and @stock_id_ee) /* and trp.id = tb_baysale_docitems.id and trp.repo_doc_id = t_items.id and t_items.state_id = st.id and t_states.state_code in ('PODT', 'OBYAZ_END') and tp.id = trp.repo_type_id and tp.code in (56,57)*/ --group by s1_id --select * from #stock_rep --отладка ---------------------------------находим id счета 1.4.2---------------------------------------- select @acc142_id = .ACCID_FROM_EFFNO('BACK_MONEY','1.4.2') select @acc142_num_eff = acc_num_eff from t_accounts where id = @acc142_id --------------------------------формируем бухгалтерские итоги---------------------------- .TOTALS_QUERY(@cur_partner, @cur_plan, @date_b, @date_e,@acc142_id, @subc_str_bal, L, N, Y, D, P) --select * from #tmp_osv -- отладка delete #tmp_osv where (saldo_de = 0) and (saldo_ce = 0) and (q_saldo_de = 0) and (q_saldo_ce = 0) --------------------------------Вставляем в результирующую таблицу итоги по определенной ЦБ из табл. #tmp_osv ------- insert into #stock_rep( stock_id, stock_s_b, stock_s_e, obor_d, obor_c, stock_qty_b, stock_qty_e, obor_qty_d, obor_qty_c ) select distinct /* #tmp_osv.s1_id, sum(#tmp_osv.saldo_db) - sum(#tmp_osv.saldo_cb), sum(#tmp_osv.saldo_de) - sum(#tmp_osv.saldo_ce), sum(#tmp_osv.obor_d), sum(#tmp_osv.obor_c), sum(#tmp_osv.q_saldo_db) - sum(#tmp_osv.q_saldo_cb), sum(#tmp_osv.q_saldo_de) - sum(#tmp_osv.q_saldo_ce), sum(#tmp_osv.q_obor_d), sum(#tmp_osv.q_obor_c)*/ #tmp_osv.s1_id, (#tmp_osv.saldo_db/*sd*/) - (#tmp_osv.saldo_cb), (#tmp_osv.saldo_de) - (#tmp_osv.saldo_ce), (#tmp_osv.obor_d), (#tmp_osv.obor_c), (#tmp_osv.q_saldo_db) - (#tmp_osv.q_saldo_cb), (#tmp_osv.q_saldo_de) - (#tmp_osv.q_saldo_ce), (#tmp_osv.q_obor_d), (#tmp_osv.q_obor_c) from #tmp_osv, t_items, t_types, t_states, td_depo_docs dd, tb_baysale_docitems, td_stock_emis -- tb_repo_params trp -- t_purposes tp where tb_baysale_docitems.stock_id = #tmp_osv.s1_id and t_items.id = tb_baysale_docitems.id and tb_baysale_docitems.id = dd.id and dd.owner_id = @cur_partner and t_types.id = t_items.type_id and t_states.id = t_items.state_id and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') and t_states.state_code not in ('PODT', 'OBYAZ_END') and dd.in_date >= @date_b and dd.in_date < dateadd(dd, 1, @date_e) and (tb_baysale_docitems.client_contract_id=@dogovor_id or tb_baysale_docitems.client_contract_id is null) and (td_stock_emis.id = @stock_id or td_stock_emis.id between @stock_id_bb and @stock_id_ee) and tb_baysale_docitems.stock_id = td_stock_emis.id /* and trp.id = tb_baysale_docitems.id and trp.repo_doc_id = t_items.id and t_items.state_id = st.id and t_states.state_code in ('PODT', 'OBYAZ_END') and tp.id = trp.repo_type_id and tp.code in (56,57)*/ --group by s1_id --select * from #stock_rep --отладка -------------------------------формируем и запускаем курсор для расчета -------------------------------------- declare curs1 cursor for select stock_id, (stock_s_e/stock_qty_e) from #stock_rep declare @price money, @rur_id numeric, @const varchar(30), @stock_s_e money, @stock_qty_e money, @stock_type numeric, @nominal money, @op_id numeric, @canc_date datetime, @dd1 int, @dd2 int, @place varchar (100) declare @dd datetime select @rur_id = id from t_currencies where cur_code = 'RUR' and .ITEMS_EXISTS(t_currencies.id) open curs1 fetch curs1 into @stock_id, @stock_s_e --,@stock_qty_e -- @op_id, while .CURSOR_STATE = 0 begin select @stock_type = tst.stock_type_id, @nominal = tse.stock_nominal, @canc_date = tse.stock_cancel_date from t_stock_types tst, td_stock_emis tse where tse.id = @stock_id and tst.id = tse.stock_type_id select @place = (select base_place_id from td_stock_emis where id = @stock_id) if @stock_type = 3 begin -- Векселя: (Номинал – Балансовая стоимость) / (Дата погашения – Дата постановки на баланс) * (Дата переоценки - Дата постановки на баланс) + Балансовая стоимость. select @subc_str_bal2 = '' .SUBC_STR_ADD(@subc_str_bal2, 'INVESTORS', @dogovor_id, Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal2, 'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal2, 'STOCK_EMIS', @stock_id, Y) select @op_date = max(o.op_date) from t_operations o, t_oper_list ol, t_accounts a --надо учесть счет where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id --надо учесть счет and (a.acc_num_eff like @acc132_num_eff + '.%' or a.acc_num_eff like @acc142_num_eff + '.%') and ol.head_id = o.id and ol.subc_eff_num like '%' + @subc_str_bal2 + '%' -- and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) select @dd1 = datediff(dd, @op_date, @canc_date), @dd2 = datediff(dd, @op_date, @date_e) select @price = (((@nominal - @stock_s_e) / @dd1) * @dd2) + @stock_s_e end else begin if @dogovor_id_s = '21535000000625521' -- если выбран договор "Доверительного управления пенсионными накоплениями ПФР 22-03У012" begin .GET_STOCK_RATE_MP/*2*/(@stock_id, @date_e, @rur_id, @price) -- оценочную стоимость считаем по цене Рыночная(2) end else begin while isnull(@price,0) = 0 and @date_b >= @dd begin .GET_STOCK_RATE2PLACE(@stock_id,@dd,@cur_partner,@rur_id,@place,@price) --оценочная стоимость по площадке if @price = null .GET_STOCK_RATE_MP(@stock_id, @dd, @rur_id, @price) -- оценочную стоимость считаем по цене Рыночная select @dd = dateadd(dd,-1,@date_e) end --.GET_STOCK_MARKET_PRICE(@stock_id,@date_e,@rur_id,@price) --не нужен end end -------------расчитываем НКД------------------ .GET_STOCK_RATE_COUPON(@stock_id, @date_e, @rur_id, @nkd) select @stock_qty_e = (select stock_qty_e from #stock_rep where stock_id = @stock_id) if isnull(@nkd,0) = 0 .BACK_CALC_COUPON(@stock_id,@stock_qty_e,@date_e,1,@nkd,@rur_id) ---------------------------------------------- select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) update #stock_rep set port_price = isnull(@price, 0) * stock_qty_e, nkd = @nkd * stock_qty_e where stock_id = @stock_id -- and id = @op_id fetch curs1 into @stock_id, @stock_s_e -- @op_id, end close curs1 deallocate curs1 --------------------------------удаляем пустые строки------------------------------------- delete #stock_rep where isnull(stock_s_b,0) = 0 and isnull(stock_s_e,0) = 0 and isnull(obor_d,0) = 0 and isnull(obor_c,0) = 0 --------------------------------результирующий селект------------------------------------- select td_stock_emis.stock_name + ' ' + td_stock_emis.emiss_statereg_no, --название ЦБ td_stock_emis.emiss_statereg_no, --номер #stock_rep.stock_s_e, --балансовая стоимость #stock_rep.stock_qty_e, --количество #stock_rep.port_price, --оценочная стоимость td_stock_emis.stock_nominal, --номинал #stock_rep.nkd as nkd --нкд from #stock_rep, td_stock_emis where #stock_rep.stock_id *= td_stock_emis.id order by td_stock_emis.stock_name end $ENDTEXT(16290973) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_FILTER) name=Фильтр клиентского отчёта class=6 filter=null target_state= uo=uo_filter dw=d_client_prom_trad_rep_filter procedure=ap_client_prom_trad_rep_filter patterns=null user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=2304 height=938 proc_text=$TEXT(16290974) create procedure dbo.%PROC% as begin declare @operday datetime select @operday = .OPERDAY declare @broker_id numeric declare @broker_code varchar(255) declare @broker_name varchar(255) select @broker_id = .ORG_FROM_PARTNER((.ORGANIZATION)) select @broker_name = tb_broker_change.broker_name, @broker_code = tb_broker_change.broker_code from tb_broker_change where tb_broker_change.id = @broker_id select convert(datetime,@operday) as date_b_d, convert(datetime,@operday) as date_e_d, convert(varchar(30), @broker_id) as broker_id_s, convert(varchar(30), null) as dogovor_id_s, convert(varchar(30), null) as stock_id_s, convert(varchar(30), null) as section_id_s, @broker_name as broker_name, @broker_code as broker_code, convert(varchar(255), null) as contract_name, convert(varchar(255), null) as stock_name, convert(varchar(255), null) as stock_code, convert(datetime,@operday) as data_sost_d, convert(varchar(255), null) as section_name end $ENDTEXT(16290974) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_HEAD) name=Клиентский отчёт class=4 filter=CLIENT_PROM_TRAD_REP_FILTER target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_head procedure=ap_client_prom_trad_rep_head patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290974) create procedure dbo.%PROC% @sid varchar(30) = null, @date_b_d varchar(30) = null, @date_e_d varchar(30) = null, @broker_id_s varchar(30) = null, @dogovor_id_s varchar(30) = null, @stock_id_s varchar(30) = null, @is_bal int = null, @data_sost_d varchar(30) = null, @section_id_s varchar(30) = null, @section_name varchar(255) = null as .BEGIN('N') .CHECK_USER declare @client_name varchar(255) declare @dogovor_name varchar(255) if @broker_id_s='' select @broker_id_s=null if @broker_id_s is null .EXIT_MESSAGE('Не задан брокер') if @dogovor_id_s is null begin select @client_name = partner_short_name from tb_broker_change,t_partners where tb_broker_change.id=convert(numeric,@broker_id_s) and t_partners.id=tb_broker_change.partner_id select @dogovor_id_s=@broker_id_s select @dogovor_name='' end else begin select distinct @client_name = isnull(tb_contracts.contr_code + ' ', '') + t_partners.partner_name, @dogovor_name = isnull(td_depo_docs.out_no, td_depo_docs.in_no) + ' от ' + convert(varchar(10), td_depo_docs.in_date, 3) from tb_contracts, t_partners, td_depo_docs where tb_contracts.client_id = t_partners.id and tb_contracts.id = td_depo_docs.id and tb_contracts.id = convert(numeric, @dogovor_id_s) end if @section_name='' select @section_name=null /* по счёту 2.5.2 */ 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 @acc252_id numeric, @acc2102_id numeric declare @dogovor_id numeric select @date_b = convert(datetime, @date_b_d, 3) select @date_e = convert(datetime, @date_e_d, 3) select @date_e = .DATE_NORM(@date_e,E) declare @data_sost datetime select @data_sost = convert(datetime, @data_sost_d , 3) select @broker_id = convert(numeric, @broker_id_s), @dogovor_id = convert(numeric, @dogovor_id_s) select @cur_partner = partner_id, @cur_plan = back_money_id from tb_broker_change where id = @broker_id select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @dogovor_id, Y) .ACCID_FROM_CONST2PLAN(@acc252_id, 'BACK_ACC_OBLIG_COMIS', @cur_plan, 3, 1, 'Задолженность по комиссиям', @mess) .ACCID_FROM_CONST2PLAN(@acc2102_id, 'BACK_ACC_COUPON', @cur_plan, 2, 1, 'Задолженность по купонам', @mess) declare @saldo_b money, @saldo_e money, @saldo_ras money, @saldo_debit money .SALDO(@cur_partner, @cur_plan, @date_e, @acc252_id, @subc_str_bal, N, K, @saldo_e) .SALDO(@cur_partner, @cur_plan, @date_e, @acc2102_id, @subc_str_bal, N, D, @saldo_debit) select @date_b_d as date_b_d, @date_e_d as date_e_d, @broker_id_s as broker_id_s, @dogovor_id_s as dogovor_id_s, (select tb_broker_change.broker_name from tb_broker_change where tb_broker_change.id = convert(numeric, @broker_id_s)) as broker_name, @client_name as client_name, @dogovor_name as dogovor_name, @stock_id_s, @is_bal, @data_sost_d as data_sost_d, @section_id_s as section_id_s, isnull(@section_name,'По всем') as section_name, @saldo_e as kredit, isnull(@saldo_debit, 0) as saldo_debit, @date_b, @date_e, @data_sost .END $ENDTEXT(16290974) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST1) name=Клиентский отчет - сделки class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list1 procedure=ap_client_prom_trad_rep_list1 patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290975) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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, @stock_id numeric declare @section_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255) declare @acc_id numeric declare @mess varchar(255) declare @cur_partner2plan numeric declare @stock_subc numeric declare @investor2portfolio_id numeric declare @nds money declare @stock_id_bb numeric declare @stock_id_ee numeric declare @character_id numeric /********************/ select @nds = 6.0 /********************/ 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) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) if @stock_id is null begin select @stock_id_bb=-999999999999999998 select @stock_id_ee=999999999999999999 end if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) select @cur_partner = partner_id, @cur_plan = back_money_id from tb_broker_change where id = @broker_id if exists(select 1 from tb_contracts where id=@dogovor_id) select @character_id=2 else select @character_id=1 create table #result( id numeric, type_code varchar(255) null, type_name varchar(255) null, in_date datetime, in_no varchar(100) null, stock_id varchar(30), stock_name varchar(255), emiss_statereg_no varchar(255) null, stock_price decimal(18,8), stock_qty money, coupon_sum money null, summ_doc_total money, comis_ts money null, comis_ts_nds money null, comis_dp money null, comis_dp_nds money null, comis_kk money null, comis_kk_nds money null, comis_vb money null, comis_vb_nds money null, comis_ou money null, comis_ou_nds money null, sale_place varchar(255) null, direction_no varchar(255) null, in_time datetime null, price_in money null, -- rasxod_in money null, pur_code int null ) insert #result( id, type_code, type_name,in_date, in_no,stock_id,stock_name, emiss_statereg_no,stock_price, stock_qty,coupon_sum,summ_doc_total,sale_place, in_time, price_in, rasxod_in ) select tb_baysale_docitems.id, t_types.type_code, /* type_name = case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' then 'Покупка' when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR' then 'Покупка' when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' then 'Продажа' when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' then 'Продажа' else '' end, */ type_name = case when (upper(t_types.type_code) in ('DEALINGS_VIA_BROKER','DOG_DEALINGS_BAY_VBR')) and not exists (select 1 from tb_repo_params trp, t_purposes tp where trp.id = tb_baysale_docitems.id and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57')) then 'Покупка' when (upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR')) and not exists (select 1 from tb_repo_params trp, t_purposes tp where trp.id = tb_baysale_docitems.id and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57')) then 'Продажа' when exists (select 1 from tb_repo_params trp, t_purposes tp where trp.id = tb_baysale_docitems.id and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57')) then (select tp.name from tb_repo_params trp, t_purposes tp where trp.id = tb_baysale_docitems.id and tp.id = trp.repo_type_id and tp.code in ('54','55','56','57')) else '' end, contr1.in_date, contr1.in_no, tb_baysale_docitems.stock_id, td_stock_emis.stock_name, td_stock_emis.emiss_statereg_no, round(tb_baysale_docitems.summ_doc_total/tb_baysale_docitems.stock_qty,8), tb_baysale_docitems.stock_qty, tb_baysale_docitems.coupon_deal_sum, tb_baysale_docitems.summ_doc_total, (select place_name from td_rate_places where id=tb_baysale_docitems.sale_place_id) as sale_place, tb_baysale_docitems.in_time, (case when t_types.type_code in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') then tb_baysale_docitems.bal_sum_out else null end), (case when t_types.type_code in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') then tb_baysale_docitems.loss4real_in else null end) from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems, td_stock_emis,tb_plan2fact_dates d where t_items.id=tb_baysale_docitems.id and tb_baysale_docitems.id = contr1.id and d.id=contr1.id and (tb_baysale_docitems.portfolio_sub_id =@section_id or @section_id is null) and contr1.owner_id = @cur_partner -- and (tb_baysale_docitems.character_id= @character_id or @character_id is null) and t_types.id=t_items.type_id and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER', 'DOG_DEALINGS_BAY_VBR','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') and t_states.id=t_items.state_id and t_states.class_id = 0 and t_states.state_code in ('PODT', 'OBYAZ_END', 'PODP', 'POLUCH', 'WAIT_EXEC','CLOSED') and contr1.in_date >=@date_b and contr1.in_date= isnull(d.fact_pay_date, contr1.in_date) and isnull(d.fact_reg_date, contr1.in_date) < dateadd(dd, 1, @date_e)) or (isnull(d.fact_reg_date, contr1.in_date) < isnull(d.fact_pay_date, contr1.in_date) and isnull(d.fact_pay_date, contr1.in_date) < dateadd(dd, 1, @date_e))) and (tb_baysale_docitems.client_contract_id=@dogovor_id or @dogovor_id is null) and (td_stock_emis.id = @stock_id or td_stock_emis.id between @stock_id_bb and @stock_id_ee) and tb_baysale_docitems.stock_id = td_stock_emis.id declare @op_partner2plan_id numeric select @op_partner2plan_id =t_plans2partner.id from t_plans2partner,t_items,t_states where t_plans2partner.plan_id = @cur_plan and t_plans2partner.partner_id = @cur_partner and t_plans2partner.id=t_items.id and t_items.state_id=t_states.id and t_states.class_id !=2 select @subc_str_bal = '' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@section_id,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) ----начисление дивидендов insert #result( id,type_name,in_date, in_no,stock_id,stock_price, stock_qty,coupon_sum,summ_doc_total, sale_place) select o.item_id, p.name, d.in_date, d.in_no, m.stock_id,m.total_sum, ol.sum_bal/m.total_sum as qty, m.nkd, ol.sum_bal, (select place_name from td_rate_places where id=m.asset_place_id) as sale_place from t_operations o,t_oper_list ol, t_accounts a, td_depo_docs d, t_purposes p, ts_sd_stock_moves m where o.id = ol.head_id and a.acc_num_eff like '2.10.0' and ol.acc_id = a.id and ol.op_date >=@date_b and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and m.id = d.id and o.item_id = d.id and d.perpose_id = p.id and p.code = 47 ---погашение купона ВБР insert #result(id,type_code,type_name,in_date,in_no,stock_id,stock_price,stock_qty,coupon_sum,summ_doc_total, sale_place, pur_code) select m.id, t.type_code, i.item_name+', '+ p.name, d.in_date, d.in_no, m.stock_id,m.total_sum, ol.sum_bal/m.total_sum as qty, m.nkd, ol.sum_bal, (select place_name from td_rate_places where id=m.asset_place_id) as sale_place, p.code from t_operations o,t_oper_list ol, t_oper_subconto os, t_subconto_types st , t_items i, ts_sd_stock_moves m, t_types t , t_accounts a, td_depo_docs d, t_purposes p where o.id = ol.head_id and a.acc_num_eff like '2.10.2' and ol.acc_id = a.id and ol.op_date >=@date_b and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and m.id = d.id and st.subc_code = 'DOC_SEC' AND os.subconto_type_id = st.id and os. item_id = m.id and os.id = ol.id and i.id = m.id and i.type_id = t.id and d.perpose_id = p.id and p.code = 52 ---погашение ЦБ в ТС select @subc_str_bal = '' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@section_id,Y) insert #result(id,type_name,in_date,in_no,stock_id,stock_price,stock_qty,coupon_sum,summ_doc_total, sale_place) select m.id,i.item_name+', '+ p.name, d.in_date, d.in_no, m.stock_id,m.total_sum, ol1.qty_sum as qty, m.nkd, ol.sum_bal, (select place_name from td_rate_places where id=m.asset_place_id) as sale_place from t_operations o,t_oper_list ol , t_items i, ts_sd_stock_moves m,t_oper_list ol1 , t_accounts a, td_depo_docs d, t_purposes p where o.id = ol.head_id and a.acc_num_eff like '2.0.10.1' and ol.acc_id = a.id and ol.op_date >=@date_b and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and ol1.head_id = ol.head_id and ol.deb_or_cred = 1 and ol1.deb_or_cred = -1 and m.id = d.id and i.id = o.item_id and o.item_id = d.id and d.perpose_id = p.id and p.code = 63 --погашение купона в ТС .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) insert #result(id, type_code, type_name,in_date,in_no,stock_id,stock_price,stock_qty,coupon_sum,summ_doc_total, sale_place, pur_code) select m.id, t.type_code, i.item_name+', '+ p.name, d.in_date, d.in_no, m.stock_id, null,sum(isnull(ol1.qty_sum,0)) as qty, sum(isnull(m.total_sum,0)), sum(isnull(ol.sum_bal,0)), (select place_name from td_rate_places where id=m.asset_place_id) as sale_place, p.code from t_operations o,t_oper_list ol , t_items i, ts_sd_stock_moves m,t_oper_list ol1, t_types t , t_accounts a, td_depo_docs d, t_purposes p, t_accounts a1 where o.id = ol.head_id and a.acc_num_eff like '4.4.1' and ol.acc_id = a.id and ol.op_date >=@date_b and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and ol1.head_id = ol.head_id and ol.deb_or_cred = 1 and ol1.deb_or_cred = -1 and ol1.acc_id = a1.id and a1.acc_num_eff like '4.4.0' and m.id = d.id and i.id = o.item_id and i.type_id = t.id and o.item_id = d.id and d.perpose_id = p.id and p.code = 46 group by m.id,i.item_name, p.name, d.in_date, d.in_no, m.stock_id, asset_place_id, t.type_code, p.code --погашение ЦБ внебиржевое -------------------------- ------------------------------4.4.0 и 4.4.6--------------------------------------- declare @acc440_id numeric declare @acc446_id numeric .ACCID_FROM_CONST2PLAN(@acc440_id,'BACK_ACC_COM_IN',@cur_plan,1,1,'Счет затрат на приобретение',@mess) .ACCID_FROM_CONST2PLAN(@acc446_id,'BACK_ACC_UNKD',@cur_plan,1,1,'Счет УНКД',@mess) select @subc_str_bal = '' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) declare @stock_cur numeric, @id_cur numeric declare @price_in money, @rasxod_in money declare curs cursor for select stock_id, id from #result where pur_code in (46,52) open curs fetch curs into @stock_cur, @id_cur while .CURSOR_STATE = 0 begin select @price_in = (select sum(ol.deb_or_cred * ol.sum_bal) from t_operations o,t_oper_list ol, t_items i, t_types t, t_oper_subconto os, t_subconto_types st where o.id = ol.head_id and ol.acc_id = @acc440_id and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and o.item_id = i.id and i.type_id = t.id and st.subc_code = 'STOCK_EMIS' and os.subconto_type_id = st.id and os.id = ol.id and os. item_id = @stock_cur) select @rasxod_in = (select sum(ol.deb_or_cred * ol.sum_bal) from t_operations o,t_oper_list ol, t_items i, t_types t, t_oper_subconto os, t_subconto_types st where o.id = ol.head_id and ol.acc_id = @acc446_id and ol.op_date<= @date_e and ol.partner2plan_id = @op_partner2plan_id and ol.subc_eff_num like '%'+ @subc_str_bal + '%' and o.item_id = i.id and i.type_id = t.id and st.subc_code = 'STOCK_EMIS' and os.subconto_type_id = st.id and os.id = ol.id and os. item_id = @stock_cur) update #result set price_in = isnull(@price_in, 0), rasxod_in = isnull(@rasxod_in, 0) where id = @id_cur and stock_id = @stock_cur fetch curs into @stock_cur, @id_cur end close curs deallocate curs --select * from #result --test ---- update #result set stock_name = td_stock_emis.stock_name, emiss_statereg_no = td_stock_emis.emiss_statereg_no from td_stock_emis where #result.stock_name is null and #result.stock_id = td_stock_emis.id --- update #result set comis_ts=(select sum(td_comis2oper.comis) from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 1 and .ITEMS_EXISTS(td_comis2oper.id)), comis_ts_nds=(select sum(td_comis2oper.comis) / @nds from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 1 and .ITEMS_EXISTS(td_comis2oper.id)), comis_dp=(select sum(td_comis2oper.comis) from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 7 and .ITEMS_EXISTS(td_comis2oper.id)) update #result set comis_dp_nds=(select sum(td_comis2oper.comis) / @nds from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 7 and .ITEMS_EXISTS(td_comis2oper.id)), comis_kk=(select sum(td_comis2oper.comis) from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 5 and .ITEMS_EXISTS(td_comis2oper.id)), comis_kk_nds=(select sum(td_comis2oper.comis) / @nds from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 5 and .ITEMS_EXISTS(td_comis2oper.id)) update #result set direction_no = (select d.in_no from td_depo_docs d, tb_baysale_docitems tbd where tbd.id = #result.id and d.id = tbd.direction_id) update #result set comis_vb=(select sum(td_comis2oper.comis) from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 3 and .ITEMS_EXISTS(td_comis2oper.id)), comis_vb_nds=(select sum(td_comis2oper.comis) / @nds from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 3 and .ITEMS_EXISTS(td_comis2oper.id)), comis_ou=(select sum(td_comis2oper.comis) from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 6 and .ITEMS_EXISTS(td_comis2oper.id)) update #result set comis_ou_nds=(select sum(td_comis2oper.comis) / @nds from td_comis2oper where td_comis2oper.item_id = #result.id and td_comis2oper.service_id = 6 and .ITEMS_EXISTS(td_comis2oper.id)) select type_name, in_date, in_no, stock_id, stock_name, emiss_statereg_no, isnull(stock_price,0), stock_qty, isnull(coupon_sum,0), isnull(summ_doc_total,0), isnull(comis_ts,0), comis_ts_nds, isnull(comis_dp,0), comis_dp_nds, isnull(comis_kk,0), comis_kk_nds, isnull(comis_vb,0), comis_vb_nds, isnull(comis_ou,0), comis_ou_nds, sale_place, direction_no, in_time, price_in, rasxod_in from #result order by stock_name,in_date,in_no end $ENDTEXT(16290975) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST2) name=Клиентский отчет - итоги ЦБ class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list2 procedure=ap_client_prom_trad_rep_list2 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290975) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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, @stock_id numeric declare @section_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255) declare @acc_id numeric declare @nkd_acc_id numeric declare @stock_rate decimal(18,8) declare @mess varchar(255) declare @bs_stock_id numeric declare @bs_stock_qty money declare @investor2portfolio_id numeric create table #stock_rep ( acc_id numeric null, stock_id numeric null, place_id numeric null, stock_qty_b money null, obor_d money null, obor_c money null, stock_qty_e money null, rate_sum money null, stock_sum money null ) select @broker_id = convert(numeric, @broker_id_s) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) select @cur_partner = partner_id, @cur_plan = back_securs_id from tb_broker_change where id = @broker_id select @date_b = convert(datetime, @date_b_d, 3) select @date_e = convert(datetime, @date_e_d, 3) --select @dogovor_id = convert(numeric, @dogovor_id_s) --select @investor2portfolio_id = convert(numeric, @investor2portfolio_id_s) --select @asset_place_id = convert(numeric, @asset_place_id_s) if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) -- S.A .ACCID_FROM_CONST2PLAN(@acc_id,'BACK_ACC_STOCK_SV',@cur_plan,1,1,'ЦБ',@mess) select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@section_id,Y) end .SUBC_STR_ADD(@subc_str_bal,'DEPO_SUBACC',null,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y) --.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) --.SUBC_STR_ADD(@subc_str_bal,'ASSET_PLACES',@asset_place_id,Y) --.SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@investor2portfolio_id,Y) --.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc_id,@subc_str_bal,L,N,A,D,P,Y) .TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,ALL,@subc_str_bal,L,N,A,D,P,Y) insert into #stock_rep( acc_id, stock_id, place_id, stock_qty_b, stock_qty_e, obor_d, obor_c) select acc_id, s1_id, s2_id, sum(saldo_db) - sum(saldo_cb), sum(saldo_de) - sum(saldo_ce), sum(obor_d), sum(obor_c) from #tmp_osv group by acc_id, s1_id, s2_id delete #stock_rep where stock_qty_b = 0 and stock_qty_e = 0 and obor_d = 0 and obor_c = 0 if (select count(*) from #stock_rep) = 0 insert into #stock_rep(stock_qty_b, stock_qty_e) values(0,0) select t_accounts.acc_num_eff + ' ' + t_accounts.acc_name as acc_name, (select item_name from t_items where t_items.id = #stock_rep.place_id) as depo_subacc_name, #stock_rep.stock_qty_b, #stock_rep.obor_d, #stock_rep.obor_c, #stock_rep.stock_qty_e, td_stock_emis.stock_name, td_stock_emis.emiss_statereg_no from #stock_rep, td_stock_emis, t_accounts where #stock_rep.stock_id *= td_stock_emis.id and #stock_rep.acc_id = t_accounts.id order by t_accounts.acc_num_eff, td_stock_emis.stock_name end $ENDTEXT(16290975) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST3) name=Клиентский отчет - итоги БС. class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list3 procedure=ap_client_prom_trad_rep_list3 patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290976) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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 declare @section_id numeric declare @stock_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255), @subc_str_bal2 varchar(255) declare @acc_id numeric, @acc20_id numeric, @acc252_id numeric, @acc440_id numeric, @acc444_id numeric, @acc2013_id numeric, @acc2102_id numeric declare @nkd_acc_id numeric declare @stock_rate decimal(18,8) declare @mess varchar(255) declare @bs_stock_id numeric declare @bs_stock_qty money, @nkd money declare @investor2portfolio_id numeric, @acc_num_eff varchar(255) create table #stock_rep ( acc_id numeric null, stock_id numeric null, op_date datetime null, place_id numeric null, stock_s_b money null, obor_d money null, obor_c money null, stock_s_e money null, stock_qty_b money null, stock_qty_e money null, obor_qty_d money null, obor_qty_c money null, port_price money null, unkd money null, nkd money null, stock_type_code varchar(50) null, type_code varchar(255) null, stock_type_id numeric null ) if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) select @broker_id = convert(numeric, @broker_id_s) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) select @cur_partner = partner_id, @cur_plan = back_money_id from tb_broker_change where id = @broker_id select @date_b = convert(datetime, @date_b_d, 3) select @date_e = convert(datetime, @date_e_d, 3) declare @plan2partner_id numeric select @plan2partner_id=.PLAN2PARTNER_ID(@cur_partner,@cur_plan) select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @dogovor_id, Y) .ACCID_FROM_CONST2PLAN(@acc440_id, 'BACK_ACC_UNKD', @cur_plan, 1, 1, 'Счёт УНКД', @mess) .ACCID_FROM_CONST2PLAN(@acc444_id, 'BACK_ACC_UNKD_SHORT', @cur_plan, 1, 1, 'Счёт УНКД на короткой позиции', @mess) .ACCID_FROM_CONST2PLAN(@acc_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'ЦБ и вложения', @mess) select @acc_num_eff = acc_num_eff from t_accounts where id = @acc_id select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y) .TOTALS_QUERY(@cur_partner, @cur_plan, @date_b, @date_e, @acc_id, @subc_str_bal, L, N, Y, D, P) delete #tmp_osv where (saldo_de = 0) and (saldo_ce = 0) and (q_saldo_de = 0) and (q_saldo_ce = 0) insert into #stock_rep( stock_id, stock_s_b, stock_s_e, obor_d, obor_c, stock_qty_b, stock_qty_e, obor_qty_d, obor_qty_c ) select s1_id, sum(saldo_db) - sum(saldo_cb), sum(saldo_de) - sum(saldo_ce), sum(obor_d), sum(obor_c), sum(q_saldo_db) - sum(q_saldo_cb), sum(q_saldo_de) - sum(q_saldo_ce), sum(q_obor_d), sum(q_obor_c) from #tmp_osv group by s1_id delete #stock_rep where isnull(stock_qty_e,0) = 0 declare curs1 cursor for select stock_id, (stock_s_e/stock_qty_e) from #stock_rep declare @price money, @rur_id numeric, @const varchar(30), @unkd_sum money, @stock_s_e money, @stock_type numeric, @nominal money, @op_id numeric, @canc_date datetime, @dd1 int, @dd2 int select @rur_id = id from t_currencies where cur_code = 'RUR' and .ITEMS_EXISTS(t_currencies.id) open curs1 fetch curs1 into @stock_id, @stock_s_e -- @op_id, while .CURSOR_STATE = 0 begin select @stock_type = tst.stock_type_id, @nominal = tse.stock_nominal, @canc_date = tse.stock_cancel_date from t_stock_types tst, td_stock_emis tse where tse.id = @stock_id and tst.id = tse.stock_type_id if @stock_type = 3 begin -- Векселя: (Номинал – Балансовая стоимость) / (Дата погашения – Дата постановки на баланс) * (Дата переоценки - Дата постановки на баланс) + Балансовая стоимость. select @subc_str_bal2 = '' .SUBC_STR_ADD(@subc_str_bal2, 'INVESTORS', @dogovor_id, Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal2, 'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal2, 'STOCK_EMIS', @stock_id, Y) select @op_date = max(o.op_date) from t_operations o, t_oper_list ol, t_accounts a where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id and a.acc_num_eff like @acc_num_eff + '.%' and ol.head_id = o.id and ol.subc_eff_num like '%' + @subc_str_bal2 + '%' -- and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) select @dd1 = datediff(dd, @op_date, @canc_date), @dd2 = datediff(dd, @op_date, @date_e) select @price = (((@nominal - @stock_s_e) / @dd1) * @dd2) + @stock_s_e end else begin .GET_STOCK_RATE_MP(@stock_id, @date_e, @rur_id, @price) end .GET_STOCK_RATE_COUPON(@stock_id, @date_e, @rur_id, @nkd) select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) select @unkd_sum = sum(ol.deb_or_cred * ol.sum_bal) from t_operations o, t_oper_list ol where ol.partner2plan_id = @plan2partner_id and ((ol.acc_id = @acc440_id) or (ol.acc_id = @acc444_id)) 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) update #stock_rep set port_price = isnull( (isnull(@price, 0) * stock_qty_e + isnull(@nkd,0)), #stock_rep.stock_s_e), unkd = @unkd_sum, nkd = @nkd * stock_qty_e where stock_id = @stock_id -- and id = @op_id update #stock_rep set port_price = stock_s_e where isnull(port_price,0)=0 --select @stock_id, @stock_s_e, @price --test --select port_price from #stock_rep where stock_id=@stock_id--test fetch curs1 into @stock_id, @stock_s_e -- @op_id, end close curs1 deallocate curs1 delete #stock_rep where stock_s_b = 0 and stock_s_e = 0 and obor_d = 0 and obor_c = 0 if (select count(*) from #stock_rep) = 0 insert into #stock_rep(stock_s_b, stock_s_e) select 0, 0 declare @bal_stoim money, @ocen_stoim money, @nkd_sum money, @unkd money select @bal_stoim = sum(isnull(#stock_rep.stock_s_e,0)), @ocen_stoim = sum(isnull(#stock_rep.port_price,0)), @nkd_sum = sum(isnull(#stock_rep.nkd,0)), @unkd = sum(isnull(#stock_rep.unkd,0)) from #stock_rep update #stock_rep set stock_type_code = t_stock_types.stock_type_code, stock_type_id = t_stock_types.stock_type_id from t_stock_types, td_stock_emis where td_stock_emis.id = #stock_rep.stock_id and t_stock_types.id = td_stock_emis.stock_type_id update #stock_rep set type_code = (case when stock_type_code in ('MFB','SUB','CBR','OFZ','GKO','FSZ','MOB', 'REG') then 'Государственные и муниципальные ценные бумаги' when stock_type_code in ('SHS','Акции обыкновенные','SHV', 'SHC', 'SHP') then 'Акции' when stock_type_code in ('CBE','BON','CBC','VIM','FIM','DMB','FSB','RFD','Коми','FCB') then 'Облигации' when stock_type_id in (3,5,8) then 'Векселя и другие неэмиссионные ценные бумаги' when stock_type_id in (5,8) then 'Депозиты и депозитные сертификаты' when stock_type_code in ('UNT', 'PAY') then 'Паи паевых инвестиционных фондов' end) --select type_code, stock_type_id from #stock_rep--test select #stock_rep.type_code as type_code, td_stock_emis.stock_name, td_stock_emis.emiss_statereg_no, #stock_rep.stock_s_e, #stock_rep.stock_qty_e, #stock_rep.port_price, isnull(#stock_rep.unkd, 0) as unkd, td_stock_emis.stock_nominal, isnull(#stock_rep.nkd, 0) as nkd /* (@bal_stoim + @money_fund - @obl_comiss + @unkd + isnull(@saldo_debit, 0)) as bal_stoim, (@ocen_stoim + @money_fund- @obl_comiss + @nkd_sum + isnull(@saldo_debit, 0)) as ocen_stoim, (@money_fund - @depozit) as money_fund, @depozit as depozit, */ from #stock_rep, td_stock_emis where #stock_rep.stock_id *= td_stock_emis.id order by #stock_rep.type_code, td_stock_emis.stock_name end $ENDTEXT(16290976) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST4) name=Клиентский отчет - итого class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list4 procedure=ap_client_prom_trad_rep_list4 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290976) create procedure dbo.%PROC% -- @sid varchar(30) = null, @broker_id_s varchar(30) = null, /* Брокер */ @date_b_d varchar(30) = null, @date_e_d varchar(30) = null, @stock_id_s varchar(30) = null, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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, @stock_id numeric declare @section_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255) declare @acc_id numeric declare @mess varchar(255) declare @cur_partner2plan numeric declare @stock_subc numeric declare @investor2portfolio_id numeric declare @nds money declare @stock_id_bb numeric declare @stock_id_ee numeric declare @character_id numeric /********************/ select @nds = 6 /********************/ 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) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) if @stock_id is null begin select @stock_id_bb=-999999999999999998 select @stock_id_ee=999999999999999999 end select @cur_partner = partner_id, @cur_plan = back_securs_id from tb_broker_change where id = @broker_id if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) create table #report( id numeric, oper_type varchar(255) null, price_total money null, qty_total money null, stock_id numeric, summ_total money null, comis_ts money null, comis_dp money null, comis_kk money null, comis_vb money null, comis_ou money null, comis_total money null) if exists(select 1 from tb_contracts where id=@dogovor_id) select @character_id=2 else select @character_id=1 insert into #report( id, oper_type, price_total, qty_total, stock_id, summ_total) select tb_baysale_docitems.id, 'Куплено акций', tb_baysale_docitems.doc_sum, -- round(tb_baysale_docitems.doc_sum/tb_baysale_docitems.stock_qty,8), tb_baysale_docitems.stock_qty, tb_baysale_docitems.stock_id, tb_baysale_docitems.doc_sum from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems, td_stock_emis where t_items.id=tb_baysale_docitems.id and tb_baysale_docitems.portfolio_sub_id = isnull(@section_id,tb_baysale_docitems.portfolio_sub_id) and tb_baysale_docitems.id = contr1.id and tb_baysale_docitems.character_id=@character_id and contr1.owner_id = @cur_partner and t_types.id=t_items.type_id and lower(t_types.type_code) in ('dog_dealings_bay_vbr','dealings_bay_in_ts','dealings_via_broker') and t_states.id=t_items.state_id and t_states.class_id = 0 and t_states.state_code in ('PODT', 'OBYAZ_END') and contr1.in_date >=@date_b and contr1.in_date=@date_b and contr1.in_date @rur_id and cur_id is not null then sum(saldo_db) else null end, case when cur_id <> @rur_id and cur_id is not null then sum(saldo_cb) else null end, case when cur_id <> @rur_id and cur_id is not null then sum(saldo_de) else null end, case when cur_id <> @rur_id and cur_id is not null then sum(saldo_ce) else null end, case when cur_id <> @rur_id and cur_id is not null then sum(obor_d) else null end, case when cur_id <> @rur_id and cur_id is not null then sum(obor_c) else null end from #tmp_osv group by acc_id, s2_id, cur_id else insert into #stock_rep( acc_id, bank_id, stock_qty_b_d, stock_qty_b_c, stock_qty_e_d, stock_qty_e_c, obor_d, obor_c, stock_qty_b_d_cur, stock_qty_b_c_cur, stock_qty_e_d_cur, stock_qty_e_c_cur, obor_d_cur, obor_c_cur) select #tmp_osv.acc_id, #tmp_osv.s3_id, case when cur_id = @rur_id or cur_id is null then sum(saldo_db) else null end, case when cur_id = @rur_id or cur_id is null then sum(saldo_cb) else null end, case when cur_id = @rur_id or cur_id is null then sum(saldo_de) else null end, case when cur_id = @rur_id or cur_id is null then sum(saldo_ce) else null end, case when cur_id = @rur_id or cur_id is null then sum(obor_d) else null end, case when cur_id = @rur_id or cur_id is null then sum(obor_c) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(saldo_db) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(saldo_cb) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(saldo_de) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(saldo_ce) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(obor_d) else null end, case when cur_id <> @rur_id or cur_id is not null then sum(obor_c) else null end from #tmp_osv group by acc_id, s3_id, cur_id delete #stock_rep where stock_qty_b_d = 0 and stock_qty_e_c = 0 and stock_qty_b_d = 0 and stock_qty_e_c = 0 and obor_d = 0 and obor_c = 0 and stock_qty_b_d_cur = 0 and stock_qty_e_c_cur = 0 and stock_qty_b_d_cur = 0 and stock_qty_e_c_cur = 0 and obor_d_cur = 0 and obor_c_cur = 0 /*if (select count(*) from #stock_rep) = 0 insert into #stock_rep(obor_d, obor_c) values(0,0)*/ declare @in_saldo money, @out_saldo money select @in_saldo = sum (stock_qty_b_d) + sum(stock_qty_b_c), @out_saldo = sum(stock_qty_e_d) + sum(stock_qty_e_c) from #stock_rep select @in_saldo, @out_saldo, t_accounts.acc_num_eff + ' ' + t_accounts.acc_name as acc_name, isnull(t_bank_accounts.bank_account_no, '') + ' ' + isnull(t_bank_accounts.bank_name, '') as bank_acc_name, #stock_rep.stock_qty_b_d, #stock_rep.stock_qty_b_c, #stock_rep.obor_d, #stock_rep.obor_c, #stock_rep.stock_qty_e_d, #stock_rep.stock_qty_e_c, #stock_rep.stock_qty_b_d_cur, #stock_rep.stock_qty_b_c_cur, #stock_rep.obor_d_cur, #stock_rep.obor_c_cur, #stock_rep.stock_qty_e_d_cur, #stock_rep.stock_qty_e_c_cur from #stock_rep, t_accounts, t_bank_accounts where #stock_rep.acc_id = t_accounts.id and #stock_rep.bank_id *= t_bank_accounts.id order by t_accounts.acc_num_eff, t_bank_accounts.bank_account_no end $ENDTEXT(16290977) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST6) name=Клиентский отчет - незавершенные сделки class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list6 procedure=ap_client_prom_trad_rep_list6 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290977) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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, @stock_id numeric declare @section_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255) declare @acc_id numeric declare @mess varchar(255) declare @cur_partner2plan numeric declare @stock_subc numeric declare @investor2portfolio_id numeric declare @nds money declare @stock_id_bb numeric declare @stock_id_ee numeric declare @character_id numeric /********************/ select @nds = 6.0 /********************/ 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) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) if @stock_id is null begin select @stock_id_bb=-999999999999999998 select @stock_id_ee=999999999999999999 end if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) select @cur_partner = partner_id, @cur_plan = back_securs_id from tb_broker_change where id = @broker_id if exists(select 1 from tb_contracts where id=@dogovor_id) select @character_id=2 else select @character_id=1 create table #result( id numeric, type_name varchar(255), in_date datetime, in_no varchar(100), stock_id_s varchar(30), stock_name varchar(255), emiss_statereg_no varchar(255) null, stock_price decimal(18,8), stock_qty money, summ_doc_total money, comis_other money null, comis_other_nds money null, comis_dp money null, comis_dp_nds money null, comis_ou money null, comis_ou_nds money null, sale_place varchar(255) null, plan_reg_date datetime null, plan_pay_date datetime null ) /* ВБР */ insert #result( id,type_name,in_date, in_no,stock_id_s,stock_name, emiss_statereg_no,stock_price, stock_qty,summ_doc_total,sale_place, plan_reg_date,plan_pay_date ) select tb_baysale_docitems.id, type_name = case when upper(t_types.type_code) = 'DOG_DEALINGS_BAY_VBR' then 'Покупка' when upper(t_types.type_code) = 'DOG_DEALINGS_SALE_VBR' then 'Продажа' else '' end, contr1.in_date, contr1.in_no, convert (varchar(30), tb_baysale_docitems.stock_id) as stock_id_s, td_stock_emis.stock_name, td_stock_emis.emiss_statereg_no, round(tb_baysale_docitems.sum_in_paycur/tb_baysale_docitems.stock_qty,8), tb_baysale_docitems.stock_qty, tb_baysale_docitems.sum_in_paycur, (select place_name from td_rate_places where id=tb_baysale_docitems.sale_place_id) as sale_place, d.plan_reg_date,d.plan_pay_date from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems, td_stock_emis,tb_plan2fact_dates d where t_items.id=tb_baysale_docitems.id and tb_baysale_docitems.id = contr1.id and tb_baysale_docitems.portfolio_sub_id = isnull(@section_id,tb_baysale_docitems.portfolio_sub_id) and d.id=tb_baysale_docitems.id and contr1.owner_id = @cur_partner and tb_baysale_docitems.character_id=@character_id and t_types.id=t_items.type_id and upper(t_types.type_code) in ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') and t_states.id=t_items.state_id and t_states.class_id = 0 and ((t_states.state_code in ('PODP', 'POLUCH', 'OPL_END', 'POST_END') and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e)) or (t_states.state_code in ('OBYAZ_END') and ((d.fact_reg_date >= d.fact_pay_date and d.fact_reg_date > @date_e and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e)) or (d.fact_reg_date < d.fact_pay_date and d.fact_pay_date > @date_e and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e) ) ) ) ) and (tb_baysale_docitems.client_contract_id=@dogovor_id or tb_baysale_docitems.client_contract_id is null) and (td_stock_emis.id = @stock_id or td_stock_emis.id between @stock_id_bb and @stock_id_ee) and tb_baysale_docitems.stock_id = td_stock_emis.id /* РТС */ insert #result( id,type_name,in_date, in_no,stock_id_s,stock_name, emiss_statereg_no,stock_price, stock_qty,summ_doc_total,sale_place, plan_reg_date,plan_pay_date ) select tb_baysale_docitems.id, type_name = case when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER' then 'Покупка' when upper(t_types.type_code) = 'DEALINGS_VIA_BROKER_SALE' then 'Продажа' else '' end, contr1.in_date, contr1.in_no, convert (varchar(30), tb_baysale_docitems.stock_id) as stock_id_s, td_stock_emis.stock_name, td_stock_emis.emiss_statereg_no, round(tb_baysale_docitems.summ_doc_total/tb_baysale_docitems.stock_qty,8), tb_baysale_docitems.stock_qty, tb_baysale_docitems.summ_doc_total, (select place_name from td_rate_places where id=tb_baysale_docitems.sale_place_id) as sale_place, d.plan_reg_date,d.plan_pay_date from t_items,t_types,t_states,td_depo_docs contr1, tb_baysale_docitems, td_stock_emis,tb_plan2fact_dates d where t_items.id=tb_baysale_docitems.id and tb_baysale_docitems.id = contr1.id and tb_baysale_docitems.portfolio_sub_id = isnull(@section_id,tb_baysale_docitems.portfolio_sub_id) and contr1.owner_id = @cur_partner and tb_baysale_docitems.character_id = @character_id and t_types.id=t_items.type_id and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER_SALE','DEALINGS_VIA_BROKER') and t_states.id=t_items.state_id and t_states.class_id = 0 and d.id=tb_baysale_docitems.id and ((t_states.state_code in ('WAIT_EXEC') and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e)) or (t_states.state_code in ('PODT') and ((d.fact_reg_date >= d.fact_pay_date and d.fact_reg_date > @date_e and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e)) or (d.fact_reg_date < d.fact_pay_date and d.fact_pay_date > @date_e and contr1.in_date >= @date_b and contr1.in_date < dateadd(dd, 1, @date_e) ) ) ) ) /* and t_states.state_code in ('WAIT_EXEC') and d.id=tb_baysale_docitems.id and contr1.in_date >=@date_b and contr1.in_date= @date_b and o.op_date < dateadd(dd, 1, @date_e) and ol.head_id = ol41.head_id and ol.id <> ol41.id and ol41.acc_id <> @acc41_id and d.id = o.item_id and i.id = o.item_id and i.type_id = t.id select @subc_type_id = .SUBCTYPE_FROM_CODE('DOC_SEC') select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@section_id,Y) end insert #result select o.id, tos.item_id, o.op_name + ' (сделка №'+ d.in_no+' от '+convert(varchar(8), d.in_date,103)+')' , a.id, a.acc_type_id, ol.subc_eff_num, o.op_date, o.sysdate, 0, ol.sum_bal, null, null, ol.id, null, b.bank_account_id, null from t_operations o, t_oper_list ol, t_accounts a,t_oper_subconto tos,t_items,t_types, tb_baysale_docitems b , td_depo_docs d where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id and a.id = @acc41_id and ol.deb_or_cred = 1 and ol.head_id = o.id and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) and tos.subconto_type_id = @subc_type_id and tos.id = ol.id and tos.item_id=t_items.id and t_items.type_id=t_types.id and upper(t_types.type_code) in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR') --- вот строчка по дате проводки -- and convert(datetime,convert(varchar(8), tdd.in_date, 103)) < convert(datetime,convert(varchar(8), ---o.op_date, 103)) and ol.subc_eff_num like '%'+@subc_str_bal+'%' and o.item_id = b.id and b.id = d.id update #result set op_name= --purpose_name = (case when (#result.acc_id = @acc41_id) or (t.type_code = 'STOCK_REDEMPTION') then isnull((select o.op_name from t_operations o where o.id = #result.oper_id),'')+ ' (' + d.in_no + ')' else isnull((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), t.type_name)+ '('+ d.in_no+' от '+convert(varchar(8), d.in_date,103)+')' end) from t_types t, t_items i, td_depo_docs d---, t_oper_subconto tos where i.id = #result.item_id and t.id = i.type_id and i.id = d.id and isnull(op_name,'')='' --select * from #result order by item_id--test update #result set --purpose_name = op_name= (case t_purposes.code when 54 then 'Платеж по сделке Прямое РЕПО' when 55 then 'Платеж по сделке Обратное РЕПО' when 56 then 'Платеж по сделке 2-ая часть прямого РЕПО' when 57 then 'Платеж по сделке 2-ая часть обратного РЕПО' when 76 then 'Платеж по сделке РПС' end) from t_paydocs,tb_baysale_docitems,tb_repo_params,t_purposes where #result.item_id=t_paydocs.id and t_paydocs.foundation_id=tb_baysale_docitems.id and tb_repo_params.id=tb_baysale_docitems.id and t_purposes.id=tb_repo_params.repo_type_id and t_purposes.code in (54,55,56,57,76) 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 .GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num, 'BANK_ACCOUNT', @m1_id) if @m1_id is not null begin select @bank_account_name=(select bank_account_no+' '+isnull(bank_account_name,'') from t_bank_accounts where id=@m1_id) end else begin select @m1_id = bank_acc_id from #result where id = @id select @bank_account_name=(select bank_account_no+' '+isnull(bank_account_name,'') from t_bank_accounts where id=@m1_id) end 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 select @bank_account_name=null, @m1_id=null, @subc_str_bal='' fetch curs into @id end close curs .DEALLOCATE curs select --distinct op_date, sum(isnull((op_sum_in), 0)) as deal_sum_in, sum(isnull((op_sum_out), 0)) as deal_sum_out, isnull(op_name,purpose_name) as purpose_name,-- isnull(purpose_name, op_name) as purpose_name, bank_account_name, 'RUR' as cur, sysdate from #result group by item_id, op_date, purpose_name, op_name, bank_account_name,acc_type_id, sysdate order by bank_account_name,acc_type_id, op_date, sysdate end $ENDTEXT(16290978) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST8) name=Клиентский отчет - операции с ЦБ class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list8 procedure=ap_client_prom_trad_rep_list8 patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290979) 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, /* ЦБ */ @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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% --.EXIT_MESSAGE('')--test declare @broker_id numeric declare @section_id numeric declare @stock_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 @accs_id numeric declare @accs_num_eff varchar(255) declare @accp_id numeric declare @accp_num_eff varchar(255) declare @dogovor_id numeric declare @accshort_id numeric declare @accshort_num_eff varchar(255) declare @m1_id numeric, @m2_id numeric, @subc_eff_num varchar(255), @depo_subacc_name varchar(255), @stock_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) select @stock_id = convert(numeric, @stock_id_s) select @cur_partner = partner_id, @cur_plan = back_securs_id from tb_broker_change where id = @broker_id if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) create table #result( id numeric IDENTITY, item_id numeric, op_name varchar(255) null, acc_last_id numeric, acc_last_type_id int, subc_eff_num varchar(255), op_date datetime, sysdate datetime, op_sum_in money, op_sum_out money, purpose_name varchar(255) null, depo_subacc_name varchar(255) null, stock_name varchar(255) null ) select @subc_str_bal='' .ACCID_FROM_CONST2PLAN(@accs_id,'BACK_ACC_STOCK_SV',@cur_plan,1,1,'Счет по ЦБ',@mess) .ACCID_FROM_CONST2PLAN(@accshort_id,'BACK_ACC_SECUR_MP_SHORT_CB',@cur_plan,1,1,'Счет КП по ЦБ',@mess) select @accs_num_eff=acc_num_eff from t_accounts where id=@accs_id select @accshort_num_eff=acc_num_eff from t_accounts where id=@accshort_id declare @plan2partner_id numeric select @plan2partner_id = .PLAN2PARTNER_ID(@cur_partner, @cur_plan) .SUBC_STR_ADD(@subc_str_bal,'INVESTORS',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO',@section_id,Y) end insert #result select o.item_id, o.op_name, a.id, a.acc_type_id, ol.subc_eff_num, o.op_date, o.sysdate, (case ol.deb_or_cred when -1 then 0 else ol.sum_bal end), (case ol.deb_or_cred when -1 then ol.sum_bal else 0 end), null,null,null from t_operations o, t_oper_list ol, t_accounts a where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id and a.acc_num_eff like @accs_num_eff+'%' and ol.head_id = o.id and ol.subc_eff_num like '%'+@subc_str_bal+'%' -- and o.op_name not in (select simple_name from t_simples where type_id=5500) and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) insert #result select o.item_id, o.op_name, a.id, a.acc_type_id, ol.subc_eff_num, o.op_date, o.sysdate, (case ol.deb_or_cred when -1 then ol.sum_bal else 0 end), (case ol.deb_or_cred when -1 then 0 else ol.sum_bal end), null,null,null from t_operations o, t_oper_list ol, t_accounts a where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id and a.acc_num_eff like @accshort_num_eff+'%' and ol.head_id = o.id and ol.subc_eff_num like '%'+@subc_str_bal+'%' -- and o.op_name not in (select simple_name from t_simples where type_id=5500) and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) update #result set purpose_name=isnull((select purp.name from t_purposes purp,td_stock_moves p where p.id=d.id and purp.id=p.is_dogovor_id),t.type_name) 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 purpose_name = (case when t.type_code in ('DEALINGS_VIA_BROKER','DOG_DEALINGS_BAY_VBR') then ('Зачисление ЦБ по сделке №'+d.in_no+' от '+ convert(varchar(8),d.in_date,3)) when t.type_code in ('DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_SALE_VBR') then ('Списание ЦБ по сделке №'+d.in_no+' от '+ convert(varchar(8),d.in_date,3)) when t.type_code in ('STOCK_REDEMPTION') then (select p.name from t_purposes p where d.perpose_id = p.id) when (t.type_code in ('D_STOCK_IN_OTHER_DEPO') and exists (select 1 from td_stock_moves m, tb_baysale_docitems b where m.id = d.id and b.id = d.foundation_id)) then ( select 'Зачисление ЦБ по сделке №'+dd.in_no+' от '+ convert(varchar(8),dd.in_date,3) from td_stock_moves m, tb_baysale_docitems b, td_depo_docs dd where m.id = d.id and b.id = d.foundation_id and dd.id = b.id) when (t.type_code in ('D_STOCK_OUT_OTHER_DEPO') and exists (select 1 from td_stock_moves m, tb_baysale_docitems b where m.id = d.id and b.id = d.foundation_id)) then ( select 'Списание ЦБ по сделке №'+dd.in_no+' от '+ convert(varchar(8),dd.in_date,3) from td_stock_moves m, tb_baysale_docitems b, td_depo_docs dd where m.id = d.id and b.id = d.foundation_id and dd.id = b.id) else isnull((select purp.name from t_purposes purp,td_stock_moves p where p.id=d.id and purp.id=p.is_dogovor_id),t.type_name) end) 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 update #result set purpose_name =p.name from t_types t,t_items i,tb_list_stock_redemption r, t_purposes p, td_depo_docs dd where #result.item_id = r.id and r.item_id = dd.id and dd.perpose_id = p.id and t.type_code in ('LIST_STOCK_REDEMPTION') and i.id=#result.item_id and t.id=i.type_id 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 .GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'STOCK_EMIS',@m1_id) if @m1_id is not null begin select @stock_name=(select stock_name from td_stock_emis where id=@m1_id) end .GET_ITEM_FROM_TYPE_SUBC_STR(@subc_eff_num,'DEPO_SUBACC',@m2_id) if @m2_id is not null begin select @depo_subacc_name=(select depo_subacc_no+isnull(depo_subacc_name,'') from td_depo_subacc where id=@m2_id) end update #result set depo_subacc_name=isnull(@depo_subacc_name,' '), stock_name=isnull(@stock_name,' ') where id=@id select @depo_subacc_name=null, @stock_name=null, @m1_id=null, @m2_id=null fetch curs into @id end close curs .DEALLOCATE curs select op_date, op_sum_in, op_sum_out, purpose_name, depo_subacc_name, stock_name from #result order by op_date,stock_name,depo_subacc_name,sysdate end $ENDTEXT(16290979) $ENDFORM $FORM(CLIENT_PROM_TRAD_REP_LIST9) name=Клиентский отчет - депозиты class=5 filter=null target_state= uo=uo_list_rep dw=d_client_prom_trad_rep_list9 procedure=ap_client_prom_trad_rep_list9 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(16290980) 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, @dogovor_id_s varchar(30) = null, @section_id_s varchar(30) = null 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 declare @section_id numeric declare @stock_id numeric declare @date_b datetime declare @date_e datetime declare @asset_place_id numeric declare @dogovor_id numeric declare @cur_plan numeric declare @cur_partner numeric declare @subc_str_bal varchar(255), @subc_str_bal2 varchar(255) declare @acc_id numeric, @acc20_id numeric, @acc252_id numeric, @acc440_id numeric, @acc444_id numeric, @acc2013_id numeric, @acc2102_id numeric declare @nkd_acc_id numeric declare @stock_rate decimal(18,8) declare @mess varchar(255) declare @bs_stock_id numeric declare @bs_stock_qty money, @nkd money declare @investor2portfolio_id numeric, @acc_num_eff varchar(255) create table #stock_rep ( -- id numeric IDENTITY not null, acc_id numeric null, stock_id numeric null, -- op_date datetime null, place_id numeric null, stock_s_b money null, obor_d money null, obor_c money null, stock_s_e money null, stock_qty_b money null, stock_qty_e money null, obor_qty_d money null, obor_qty_c money null, port_price money null, unkd money null, nkd money null ) /* create table #result ( stock_id numeric null, stock_s_e money null, stock_qty_e money null, port_price money null, unkd money null, nkd money null ) */ if @dogovor_id_s = '' select @dogovor_id_s = null select @dogovor_id = convert(numeric, @dogovor_id_s) if @section_id_s = '' select @section_id_s = null select @section_id = convert(numeric, @section_id_s) select @broker_id = convert(numeric, @broker_id_s) if @stock_id_s = '' select @stock_id_s = null select @stock_id = convert(numeric, @stock_id_s) select @cur_partner = partner_id, @cur_plan = back_money_id from tb_broker_change where id = @broker_id select @date_b = convert(datetime, @date_b_d, 3) select @date_e = convert(datetime, @date_e_d, 3) declare @plan2partner_id numeric select @plan2partner_id=.PLAN2PARTNER_ID(@cur_partner,@cur_plan) select @subc_str_bal = '' .SUBC_STR_ADD(@subc_str_bal, 'INVESTORS', @dogovor_id, Y) .ACCID_FROM_CONST2PLAN(@acc20_id, 'BACK_ACC_MONEY_PLACE', @cur_plan, 1, 1, 'Счёт по ДС', @mess) .ACCID_FROM_CONST2PLAN(@acc252_id, 'BACK_ACC_OBLIG_COMIS', @cur_plan, 3, 1, 'Счёт кредит. задолжн.', @mess) .ACCID_FROM_CONST2PLAN(@acc440_id, 'BACK_ACC_UNKD', @cur_plan, 1, 1, 'Счёт УНКД', @mess) .ACCID_FROM_CONST2PLAN(@acc444_id, 'BACK_ACC_UNKD_SHORT', @cur_plan, 1, 1, 'Счёт УНКД на короткой позиции', @mess) .ACCID_FROM_CONST2PLAN(@acc2013_id, 'BACK_ACC_DEPOZIT', @cur_plan, 1, 1, 'Депозитные счета в банках', @mess) .ACCID_FROM_CONST2PLAN(@acc2102_id, 'BACK_ACC_COUPON', @cur_plan, 2, 1, 'Задолженность по купонам', @mess) declare @money_fund money, @saldo_e money, @saldo_ras money, @obl_comiss money, @depozit money, @saldo_debit money .SALDO(@cur_partner, @cur_plan, @date_e, @acc20_id, @subc_str_bal, N, D, @money_fund) .SALDO(@cur_partner, @cur_plan, @date_e, @acc252_id, @subc_str_bal, N, K, @obl_comiss) --.SALDO(@cur_partner, @cur_plan, @date_e, @acc2013_id,@subc_str_bal, N, D, @depozit) .SALDO(@cur_partner, @cur_plan, @date_e, @acc2102_id, @subc_str_bal, N, D, @saldo_debit) -- стоимость портфеля по бумагам .ACCID_FROM_CONST2PLAN(@acc_id, 'BACK_ACC_BAL_STOCK', @cur_plan, 1, 1, 'ЦБ и вложения', @mess) select @acc_num_eff = acc_num_eff from t_accounts where id = @acc_id select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal,'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y) --.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) --.TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc_id,@subc_str_bal,L,N,S,D,O) .TOTALS_QUERY(@cur_partner,@cur_plan,@date_b,@date_e,@acc_id,@subc_str_bal,L,N,Y,D,P) --select * from #tmp_osv delete #tmp_osv where (saldo_de = 0) and (saldo_ce = 0) and (q_saldo_de = 0) and (q_saldo_ce = 0) insert into #stock_rep( stock_id, -- op_date, stock_s_b, stock_s_e, obor_d, obor_c, stock_qty_b, stock_qty_e, obor_qty_d, obor_qty_c ) /* select s1_id, op_date, (saldo_db - saldo_cb), (saldo_de - saldo_ce), obor_d, obor_c, (q_saldo_db - q_saldo_cb), (q_saldo_de - q_saldo_ce), q_obor_d, q_obor_c from #tmp_osv */ select s1_id, sum(saldo_db) - sum(saldo_cb), sum(saldo_de) - sum(saldo_ce), sum(obor_d), sum(obor_c), sum(q_saldo_db) - sum(q_saldo_cb), sum(q_saldo_de) - sum(q_saldo_ce), sum(q_obor_d), sum(q_obor_c) from #tmp_osv group by s1_id declare curs1 cursor for select stock_id, (stock_s_e/stock_qty_e) from #stock_rep declare @price money, @rur_id numeric, @const varchar(30), @unkd_sum money, @stock_s_e money, @stock_type numeric, @nominal money, @op_id numeric, @canc_date datetime, @dd1 int, @dd2 int select @rur_id = id from t_currencies where cur_code = 'RUR' and .ITEMS_EXISTS(t_currencies.id) open curs1 fetch curs1 into @stock_id, @stock_s_e -- @op_id, while .CURSOR_STATE = 0 begin select @price = 0 /* select @const = (.GET_CONST('STOCK_RATE_TYPE')) if (isnull(@const, '-1') <> '-1') and (@const = 'PROM_TRAD') .GET_STOCK_RATE_MP_COUPON(@stock_id, @date_e, @rur_id, @price) else */ select @stock_type = tst.stock_type_id, @nominal = tse.stock_nominal, @canc_date = tse.stock_cancel_date from t_stock_types tst, td_stock_emis tse where tse.id = @stock_id and tst.id = tse.stock_type_id -- select @op_date = op_date from #stock_rep where id = @op_id if @stock_type = 3 begin -- Векселя: (Номинал – Балансовая стоимость) / (Дата погашения – Дата постановки на баланс) * (Дата переоценки - Дата постановки на баланс) + Балансовая стоимость. select @subc_str_bal2 = '' .SUBC_STR_ADD(@subc_str_bal2, 'INVESTORS', @dogovor_id, Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal2, 'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal2, 'STOCK_EMIS', @stock_id, Y) select @op_date = max(o.op_date) from t_operations o, t_oper_list ol, t_accounts a where ol.partner2plan_id = @plan2partner_id and ol.acc_id = a.id and a.acc_num_eff like @acc_num_eff + '.%' and ol.head_id = o.id and ol.subc_eff_num like '%' + @subc_str_bal2 + '%' and o.op_date >= @date_b and o.op_date < dateadd(dd, 1, @date_e) select @dd1 = datediff(dd, @op_date, @canc_date), @dd2 = datediff(dd, @op_date, @date_e) select @price = (((@nominal - @stock_s_e) / @dd1) * @dd2) + @stock_s_e -- select @nominal as nom, @stock_s_e as stock_s_e, @dd1 as dd1, @dd2 as dd2, @op_date as op_date, -- @canc_date as canc_date, @date_e as date_e end else begin .GET_STOCK_RATE_MP(@stock_id, @date_e, @rur_id, @price) end .GET_STOCK_RATE_COUPON(@stock_id, @date_e, @rur_id, @nkd) select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal,'investors',@dogovor_id,Y) .SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',@stock_id,Y) select @unkd_sum = sum(ol.deb_or_cred * ol.sum_bal) from t_operations o, t_oper_list ol where ol.partner2plan_id = @plan2partner_id and ((ol.acc_id = @acc440_id) or (ol.acc_id = @acc444_id)) 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) update #stock_rep set port_price = isnull(@price, 0) * stock_qty_e, unkd = @unkd_sum, nkd = @nkd * stock_qty_e where stock_id = @stock_id -- and id = @op_id fetch curs1 into @stock_id, @stock_s_e -- @op_id, end close curs1 deallocate curs1 delete #stock_rep where stock_s_b = 0 and stock_s_e = 0 and obor_d = 0 and obor_c = 0 /* insert into #result( stock_id, stock_s_e, stock_qty_e, port_price, unkd, nkd ) select #stock_rep.stock_id, sum(#stock_rep.stock_s_e), sum(#stock_rep.stock_qty_e), sum(#stock_rep.port_price), sum(#stock_rep.unkd), sum(#stock_rep.nkd) from #stock_rep group by #stock_rep.stock_id */ declare @bal_stoim money, @ocen_stoim money, @nkd_sum money, @unkd money select @bal_stoim = sum(isnull(#stock_rep.stock_s_e,0)), @ocen_stoim = sum(isnull(#stock_rep.port_price,0)), @nkd_sum = sum(isnull(#stock_rep.nkd,0)), @unkd = sum(isnull(#stock_rep.unkd,0)) from #stock_rep -- депозитные счета select @subc_str_bal='' .SUBC_STR_ADD(@subc_str_bal, 'investors', @dogovor_id, Y) if @section_id is not null begin .SUBC_STR_ADD(@subc_str_bal, 'INVESTOR2PORTFOLIO', @section_id, Y) end .SUBC_STR_ADD(@subc_str_bal, 'ASSET_PLACES', null, Y) .SUBC_STR_ADD(@subc_str_bal, 'BANK_ACCOUNT', null, Y) .TOTALS_QUERY(@cur_partner, @cur_plan, @date_b, @date_e, @acc2013_id, @subc_str_bal, L,N,S,D,P) --insert into #depozit (place_id, bal_sum) --select s1_id, sum(saldo_de) - sum(saldo_ce) from #tmp_osv order by s1_id declare @place_id numeric, @op_date2 datetime, @depo_bal_sum money, @procent money, @days_qty int, @depo_pere_sum money, @bank_acc_id numeric create table #depozit ( place_id numeric null, bank_acc_id numeric null, bal_sum money null, pere_sum money null) if @section_id is not null begin declare curs2 cursor for select s4_id, s3_id, op_date, saldo_de from #tmp_osv order by s4_id, s3_id end else begin declare curs2 cursor for select s3_id, s2_id, op_date, saldo_de from #tmp_osv order by s3_id, s2_id end open curs2 fetch curs2 into @place_id, @bank_acc_id, @op_date2, @depo_bal_sum while .CURSOR_STATE = 0 begin select @procent = tsc.comission_percent, @days_qty = tbc.qty_day_yy, @op_date2=dogexec_begin_date from tb_contracts tbc, tb_settings_comis tsc, tb_broker2exchange tbe where tbe.id = @place_id and tbe.ext_broker_deal = tbc.id and .ITEMS_EXISTS_BY_TYPE_STATE(tbc.id,'ORGANIZATION_CONTRACTS','SIGN') and tbc.dog_type_id = 8 and tsc.settings_id = tbc.id and .ITEMS_EXISTS_BY_TYPE( tsc.id,'revard') -- (Сумма вклада * % / 365 (366) ) * (Дата переоценки – Дата постановки на баланс) + Сумма вклада. select @depo_pere_sum = (((@depo_bal_sum * @procent) / 100) / @days_qty) * datediff(dd, @op_date2, @date_e) + @depo_bal_sum insert into #depozit (place_id, bank_acc_id, bal_sum, pere_sum) select @place_id, @bank_acc_id, @depo_bal_sum, @depo_pere_sum fetch curs2 into @place_id, @bank_acc_id, @op_date2, @depo_bal_sum end close curs2 deallocate curs2 if (select count(*) from #depozit) = 0 insert into #depozit(bal_sum, pere_sum) values(0,0) -- select @ocen_stoim select /*-- trp.place_name as place_name, t_items.item_name as place_name, tba.bank_account_no as acc_no, sum(#depozit.bal_sum) as depo_bal, sum(#depozit.pere_sum) as depo_pere,*/ (isnull(@bal_stoim,0) + isnull(@money_fund,0) - isnull(@obl_comiss,0) + isnull(@unkd,0) + isnull(@saldo_debit, 0)) as bal_stoim, -- (@ocen_stoim + @money_fund- @obl_comiss + @nkd_sum + isnull(@saldo_debit, 0)) as ocen_stoim, (isnull(@ocen_stoim,0) + (isnull(@money_fund,0) - isnull(sum(#depozit.bal_sum),0) + isnull(sum(#depozit.pere_sum),0)) - isnull(@obl_comiss,0) + isnull(@nkd_sum,0) + isnull(@saldo_debit, 0)) as ocen_stoim /* (isnull(@money_fund,0) - isnull(sum(#depozit.bal_sum),0)) as money_fund -- (@money_fund - @depozit) as money_fund -- @depozit as depozit */ from #depozit, tb_broker2exchange tbe, t_items, t_bank_accounts tba -- td_rate_places trp, where tbe.id =* #depozit.place_id -- and trp.id = tbe.exchange_id and tba.id =* #depozit.bank_acc_id and t_items.id =* #depozit.place_id group by t_items.item_name, /* trp.place_name, */ tba.bank_account_no ----------заплатка на закрытые депозитные счета---------------------------------------------------------- having ((isnull(@ocen_stoim,0) + (isnull(@money_fund,0) - isnull(sum(#depozit.bal_sum),0) + isnull(sum(#depozit.pere_sum),0)) - isnull(@obl_comiss,0) + isnull(@nkd_sum,0) + isnull(@saldo_debit, 0)))>0 and ((isnull(@bal_stoim,0) + isnull(@money_fund,0) - isnull(@obl_comiss,0) + isnull(@unkd,0) + isnull(@saldo_debit, 0)))>0 end $ENDTEXT(16290980) $ENDFORM $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REPO) state=NEW action=CLIENT_PROM_TRAD_REPO is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_FILTER) state=NEW action=CLIENT_PROM_TRAD_REP_FILTER is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_HEAD) state=NEW action=CLIENT_PROM_TRAD_REP_HEAD is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST1) state=NEW action=CLIENT_PROM_TRAD_REP_LIST1 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST2) state=NEW action=CLIENT_PROM_TRAD_REP_LIST2 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST3) state=NEW action=CLIENT_PROM_TRAD_REP_LIST3 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST4) state=NEW action=CLIENT_PROM_TRAD_REP_LIST4 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST5) state=NEW action=CLIENT_PROM_TRAD_REP_LIST5 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST6) state=NEW action=CLIENT_PROM_TRAD_REP_LIST6 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST7) state=NEW action=CLIENT_PROM_TRAD_REP_LIST7 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST8) state=NEW action=CLIENT_PROM_TRAD_REP_LIST8 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(NEW.CLIENT_PROM_TRAD_REP_LIST9) state=NEW action=CLIENT_PROM_TRAD_REP_LIST9 is_available=1 $ENDSTATE2ACTION $ENDDOC