# Date: 25/04/06 # Version: 105 $DOC(TB_STOCK_RATES) name=Котировки ЦБ class=1 product=ALD_SQL name_formula=null remark_formula=null $STATE(CRT) name=Создан class=0 $ENDSTATE $STATE(DEL) name=Удален class=2 $ENDSTATE $STATE(NEW) name=Начальный class=1 $ENDSTATE $PROP(STOCK_ID) name=Код ЦБ field_name=stock_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=1 init_class=0 init_formula=null unique_index=1 numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(RATE_PLACE_ID) name=Код площадки field_name=rate_place_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=2 init_class=0 init_formula=null unique_index=1 numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(RATE_DATE) name=Дата котировки field_name=rate_date table_name=tb_stock_rates field_type=datetime field_length=8 field_scale=3 field_prec=23 page=1 order=3 init_class=1 init_formula=.OPERDAY unique_index=1 numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(STOCK_RATE) name= Рыночная цена (1) (Market Price) field_name=stock_rate table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=6 init_class=0 init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(COUPON_RATE) name=НКД field_name=coupon_rate table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=15 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(AVERAGE) name=Средневзвешенная цена field_name=average table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=7 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(BID) name=Котировка на покупку на момент окончания торговой сессии field_name=bid table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=19 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(ASK) name=Котировка на продажу на момент окончания торговой сессии field_name=ask table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=18 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(LAST_PRICE) name=Цена последней сделки field_name=last_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=20 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(VOLUME) name=Дневной объём торгов в штуках field_name=volume table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=10 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(AVERAGE_BID10) name=Средневзвешенная цена 10 лучших предложений на покупку field_name=average_bid10 table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=12 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(AVERAGE_BID) name=Средневзвешенная цена покупки, рассчитанная по котировкам всех участников field_name=average_bid table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=13 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(AVERAGE_BID_PD) name=Средневзвешенная цена покупки, рассчитанная по котировкам первичных дилеров field_name=average_bid_pd table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=14 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(RATE_CUR_ID) name=Валюта котировки field_name=rate_cur_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=4 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(VOLUME_CUR_ID) name=Валюта объема field_name=volume_cur_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=9 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(LIST_ID) name=Категория листинга field_name=list_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=5 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(MAX_PRICE) name=Максимальная цена сделки field_name=max_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=16 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(MIN_PRICE) name=Минимальная цена сделки field_name=min_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=17 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(TRADES_NUM) name=Дневное количество сделок field_name=trades_num table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=11 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(ACKNOWLEDGED_PRICE) name=Признаваемая котировка (по ФКЦБ) field_name=acknowledged_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=21 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(RATE_TYPE) name=Признаваемая для ПИФ field_name=rate_type table_name=tb_stock_rates field_type=int field_length=4 field_scale=0 field_prec=10 page=1 order=22 init_class=1 init_formula=1 unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(MONEY_VOLUME) name=Дневной объём торгов в валюте field_name=money_volume table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=8 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CALC_PRICE_POINTS) name=Расчетная цена фьючерса в пунктах field_name=calc_price_points table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=23 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CALC_PRICE_CUR) name=Расчетная цена фьючерса в валюте field_name=calc_price_cur table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=24 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CUR_CALC_PRICE) name=Валюта расчетной цены field_name=cur_calc_price table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=25 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(STEP_PRICE_POINTS) name=Шаг цены в пуктах field_name=step_price_points table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=26 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(STEP_PRICE_CUR) name=Стоимость шага цены в валюте расч. цены field_name=step_price_cur table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=27 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(YIELD_CLOSE) name=Доходность по цене последней сделки, % годовых field_name=yield_close table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=29 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(BOARD_CODE) name=Идентификатор режима торгов field_name=board_code table_name=tb_stock_rates field_type=varchar field_length=255 field_scale=null field_prec=255 page=1 order=30 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(BOARD_NAME) name=Наименование режима торгов field_name=board_name table_name=tb_stock_rates field_type=varchar field_length=255 field_scale=null field_prec=255 page=1 order=31 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OPEN_PERIOD) name=Цена предторгового периода field_name=open_period table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=32 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OPEN1) name=Цена первой сделки field_name=open1 table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=33 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CLOSE_PERIOD) name=Цена послеторгового периода field_name=close_period table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=34 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(LOW_OFFER) name=Минимальная котировка на продажу field_name=low_offer table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=35 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(HIGH_BID) name=Максимальная котировка на покупку field_name=high_bid table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=36 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(ISSUE_SIZE) name=Объем обращения в ЦБ field_name=issue_size table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=37 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OPEN_PRICE) name=Цена открытия field_name=open_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=38 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CLOSE_PRICE) name=Цена закрытия field_name=close_price table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=39 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OPEN_VALUE) name=Объем первой сделки field_name=open_value table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=40 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(CLOSE_VALUE) name=Объем последней сделки field_name=close_value table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=41 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(MARKET_PRICE2) name=Рыночная цена(2) field_name=market_price2 table_name=tb_stock_rates field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=42 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(YIELD_WAP) name=Доходность по средневзв. цене, % годовых field_name=yield_wap table_name=tb_stock_rates field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=28 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(LAST_PRICE_CHANGED_DATE) name=Дата последней сделки field_name=last_price_changed_date table_name=tb_stock_rates field_type=datetime field_length=8 field_scale=3 field_prec=23 page=1 order=43 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(M_PRICE_CUR_ID) name=Валюта рыночных цен и признаваемой котировки field_name=m_price_cur_id table_name=tb_stock_rates field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=44 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(COMMENT_1) name=Комментарий field_name=comment table_name=tb_stock_rates field_type=varchar field_length=255 field_scale=null field_prec=null page=1 order=45 init_class=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $RELATION(REL3) class=0 type=CUR form=null prop=RATE_CUR_ID idname=null order=null $ENDRELATION $RELATION(REL4) class=0 type=CUR form=null prop=VOLUME_CUR_ID idname=null order=null $ENDRELATION $RELATION(REL6) class=0 type=CUR form=null prop=CUR_CALC_PRICE idname=null order=null $ENDRELATION $RELATION(REL7) class=0 type=CUR form=null prop=M_PRICE_CUR_ID idname=null order=null $ENDRELATION $RELATION(REL5) class=0 type=SIMPLES form=null prop=LIST_ID idname=null order=null $ENDRELATION $RELATION(REL1) class=0 type=TD_STOCK_EMIS form=null prop=STOCK_ID idname=null order=null $ENDRELATION $RELATION(REL2) class=0 type=RATE_PLACES form=null prop=RATE_PLACE_ID idname=null order=null $ENDRELATION $FORM(D_STOCK_RATES_EDIT) name=Просмотр class=5 filter=null target_state= uo=uo_edit dw=d_stock_rates_edit procedure=ap_10000000000017037 patterns=GENERIC_VIEW_ITEM user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122912) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_VIEW_ITEM if @id = -1 begin begin tran .GET_PROPS .CHECK_INSERT .INIT_VARS(B) .INSERT_ITEMS .INSERT commit tran end .VIEW_ITEM .END $ENDTEXT(13122912) $ACTION2RELATION(REL2.PLACE_NAME) relation=REL2 where=null prop=PLACE_NAME order=6 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PLACE_CODE) relation=REL2 where=null prop=PLACE_CODE order=5 alias=place_code $ENDACTION2RELATION $ACTION2RELATION(REL1.STOCK_CODE) relation=REL1 where=null prop=STOCK_CODE order=38 alias=stock_code $ENDACTION2RELATION $ACTION2RELATION(REL1.STOCK_NAME) relation=REL1 where=null prop=STOCK_NAME order=18 alias=stock_name $ENDACTION2RELATION $ACTION2RELATION(REL6.CUR_CODE) relation=REL6 where=null prop=CUR_CODE order=15 alias=cur_code $ENDACTION2RELATION $ENDFORM $FORM(D_STOCK_RATES_FILTER) name=Фильтр class=6 filter=null target_state= uo=uo_filter dw=d_stock_rates_filter procedure=ap_10000000000017042 patterns=null user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=1970 height=524 proc_text=$TEXT(13122914) create procedure dbo.%PROC% as begin declare @oper_date datetime select @oper_date = .OPERDAY select convert(varchar(30),null), convert(varchar(30),null), @oper_date, @oper_date, convert(varchar(255), null), convert(varchar(255), null) end $ENDTEXT(13122914) $ACTION2RELATION(REL2.PLACE_NAME) relation=REL2 where=null prop=PLACE_NAME order=1 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL1.STOCK_CODE) relation=REL1 where=null prop=STOCK_CODE order=3 alias=stock_code $ENDACTION2RELATION $FILTER(RATE_DATE) type=2 label=Дата prop=RATE_DATE arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(RATE_PLACE_ID) type=1 label=Наименование площадки prop=RATE_PLACE_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(STOCK_ID) type=1 label=Ценная бумага prop=STOCK_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(D_STOCK_RATES_LIST) name=Список class=4 filter=D_STOCK_RATES_FILTER target_state= uo=uo_list dw=d_stock_rates_list procedure=ap_10000000000017048 patterns=GENERIC_VIEW_LIST user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=4073 height=2068 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as .BEGIN('N') .CHECK_USER .CHECK_VIEW_LIST .ARGCONVERT_FILTER .VIEW_LIST .END $ENDTEXT(13122914) $ACTION2RELATION(REL2.PLACE_NAME) relation=REL2 where=null prop=PLACE_NAME order=6 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PLACE_CODE) relation=REL2 where=null prop=PLACE_CODE order=5 alias=place_code $ENDACTION2RELATION $ACTION2RELATION(REL1.STOCK_CODE) relation=REL1 where=null prop=STOCK_CODE order=40 alias=stock_code $ENDACTION2RELATION $ACTION2RELATION(REL1.STOCK_NAME) relation=REL1 where=null prop=STOCK_NAME order=39 alias=stock_name $ENDACTION2RELATION $ACTION2RELATION(REL3.CUR_CODE) relation=REL3 where=null prop=CUR_CODE order=15 alias=cur_code $ENDACTION2RELATION $ACTION2RELATION(REL4.CUR_CODE) relation=REL4 where=null prop=CUR_CODE order=15 alias=volume_cur_code $ENDACTION2RELATION $ACTION2RELATION(REL5.SIMPLE_NAME) relation=REL5 where=type_id=3045 prop=SIMPLE_NAME order=2 alias=list_name $ENDACTION2RELATION $ACTION2RELATION(REL6.CUR_CODE) relation=REL6 where=null prop=CUR_CODE order=15 alias=calc_cur_code $ENDACTION2RELATION $ACTION2RELATION(REL7.CUR_CODE) relation=REL7 where=null prop=CUR_CODE order=15 alias=m_price_cur_code $ENDACTION2RELATION $ENDFORM $FORM(FORM_VIEW) name=Список для импорта СПВБ (ТХТ) class=5 filter=null target_state= uo=null dw=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(IMPORT_THCB_LIST) name=Список котировок для ТХБ class=5 filter=null target_state= uo=uo_tchb_rates_import dw=d_tchb_rates_import procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(RATE_TEST_LIST) name=Тестовый импорт (список) class=5 filter=null target_state= uo=uo_import_filter2 dw=d_csv_import procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(STOCK_RATES_EXCEL) name=Список для импорта котировок СПФБ(excel)[csv] class=5 filter=null target_state= uo=uo_csv_import dw=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(STOCK_RATES_IMPORT_LIST) name=Список импорта class=5 filter=null target_state= uo=uo_excel_rts dw=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(STOCK_RATES_IMPORT_LIST_NEW) name=Список импорта новый class=5 filter=null target_state= uo=uo_excel_rts_new dw=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(MMVB_CSV_FORM) name=Импорт ММВБ (csv) class=5 filter=null target_state= uo=uo_csv_import dw=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $ACTION(ACTION1) name=Добавить class=1 form=D_STOCK_RATES_EDIT target_state=CRT procedure=ap_10000000000017055 patterns=GENERIC_INSERT user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=4663 height=2692 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .ARGCONVERT .INIT_VARS .TEST_UNIQUE begin tran .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran select sid = convert(varchar, @id) .END $ENDTEXT(13122914) $ENDACTION $ACTION(ACTION2) name=Изменить class=2 form=D_STOCK_RATES_EDIT target_state=null procedure=ap_10000000000017056 patterns=GENERIC_UPDATE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=4247 height=2692 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .ARGCONVERT .TEST_UNIQUE begin tran .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran .END $ENDTEXT(13122914) $ENDACTION $ACTION(ACTION_DEL) name=Удалить class=2 form=D_STOCK_RATES_EDIT target_state=DEL procedure=ap_10000000000017057 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=2510 height=2168 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE begin tran .TYPE_DELETE(@id) if @@error <> 0 begin rollback tran return end commit tran .END $ENDTEXT(13122914) $ENDACTION $ACTION(ACTION_INSERT) name=Импорт СПВБ (ТХТ) class=1 form=FORM_VIEW target_state=null procedure=ap_21535000000857680 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @moment varchar(30) /* Дата */, @issue varchar(30) /* код ЦБ */, @quote_list varchar(30) /* Листинг*/, @par decimal(18,8) /* Номинал */, @par_currency varchar(30) /* Валюта номинала */, @price_currency varchar(30) /* Валюта */, @volume_currency varchar(30) /* Валюта для объемов торгов */, @money_volume money /* Объем в валюте */, @volume money /* Объем в шт */, @trades_num money /* Количество сделок */, @average decimal(18,8) /* Средневзвешенная */, @market_price decimal(18,8) /* Рыночная */, @average_bid10 decimal(18,8) /* Лучшие 10 предложений */, @average_bid decimal(18,8) /* Лучшие покупки */, @average_bid_pd decimal(18,8) /* Лучшие покупки маркет-мейкеров*/, @rate_place_id_s varchar(30) /* Код площадки */, @max_price decimal(18,8) /* Максимальная цена */, @min_price decimal(18,8) /* Минимальная цена */, -- Добавлено @ask decimal(18,8) = null /*Цена лучшей заявки на продажу*/, @bid decimal(18,8) = null /*Цена лучшей заявки на покупку*/, @last_price decimal(18,8) = null /*Цена лучшей заявки на покупку*/, @acknowledged_price decimal(18,8) = null /*Признаваемая котировка по ФКЦБ*/, -- @nkd decimal(18,8) = null /* НКД */ /* Issue Min Max Open OpenVol Close CloseVol CalcPrice MrktPrice -- MrktPrice2 -- Quotation Best_buy Best_sell Trades Volume Vol, 1000 RUB */ as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% .CHECK_USER .CHECK_INSERT declare @rate_date datetime declare @stock_code varchar(30) declare @rate_place_id numeric declare @classvalue_id numeric declare @stock_id numeric declare @plase_cur_id numeric declare @par_cur_id numeric declare @price_cur_id numeric declare @rate_sum money declare @rate_value decimal(18, 8) declare @temp_date datetime declare @volume_cur_id numeric declare @list_id numeric --если все значимые поля не заполнены - сразу нафиг --для облигации может быть все не заполнено, а вот НКД быть не пустым. добавил НКД -- Добавлена признаваемая котировка и количество сделок (если есть сделки то есть ask,last,max... и пр) if (isnull(@average,0) = 0 and isnull(@market_price,0) = 0 and isnull(@average_bid10,0) = 0 and isnull(@average_bid,0) = 0 and isnull(@average_bid_pd,0) = 0 and isnull(@nkd,0) = 0 and isnull(@trades_num,0) = 0 and isnull(@acknowledged_price,0) = 0 and ((isnull(@ask ,0) = 0) or (isnull(@bid ,0) = 0)) ) return select @rate_date = convert(datetime, substring(@moment, 1, 8), 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') select @stock_code = rtrim(ltrim(UPPER(@issue))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') -- Площадка select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') -- Объем торгов select @volume_cur_id = .CUR(@volume_currency) if @volume_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта объема торгов с кодом', @volume_currency) -- Листинг select @list_id = 0 select @quote_list = isnull(upper(@quote_list), 'NOT_DEFINED') if @quote_list in ('A1','EQBR','EQOB') select @list_id = 1 else if @quote_list in ('A2','EQBS','EQOS') select @list_id = 2 else if @quote_list in ('B','EQNL','EQNO') select @list_id = 3 else if @quote_list in ('PSAU') select @list_id = 4 select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') create table #stockrate( stock_id numeric ) insert into #stockrate(stock_id) select td_stock_emis.id from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@stock_code) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id from #stockrate for read only open cstock begin tran fetch cstock into @stock_id while (.CURSOR_STATE = 0) -- LOOP begin -- Обработка данных if upper(@price_currency) = 'PCT' begin -- Котировка в процентах select @price_cur_id = .CUR(@par_currency) if @price_cur_id is null select @price_cur_id = (select td_stock_emis.stock_curr_id from td_stock_emis where td_stock_emis.id = @stock_id) if @price_cur_id is null .EXIT_MESSAGE('Для ЦБ не указана валюта номинала') select @plase_cur_id = @par_cur_id if @par is null select @par = (select td_stock_emis.stock_nominal from td_stock_emis where td_stock_emis.id = @stock_id) if @par is null .EXIT_MESSAGE('Для ЦБ не указан номинал') select @average = round((@par * @average / 100.0), 8) select @market_price = round((@par * @market_price / 100.0), 8) select @average_bid10 = round((@par * @average_bid10 / 100.0), 8) select @average_bid = round((@par * @average_bid / 100.0), 8) select @average_bid_pd = round((@par * @average_bid_pd / 100.0), 8) select @max_price = round((@par * @max_price / 100.0), 8) select @min_price = round((@par * @min_price / 100.0), 8) select @ask = round((@par * @ask / 100.0), 8) select @bid = round((@par * @bid / 100.0), 8) select @last_price = round((@par * @last_price / 100.0), 8) select @acknowledged_price = round((@par * @acknowledged_price / 100.0), 8) end else begin -- Котировка в валюте select @price_cur_id = .CUR(@price_currency) if @price_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта значений с кодом', @price_currency) end select @id = null select @id = tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_date = @rate_date and .ITEMS_EXISTS(tb_stock_rates.id) if @id is null begin -- New Record --.EXIT_MESSAGE('OK') .TYPE_INIT('TB_STOCK_RATES') .TYPE_ASSIGN(rate_place_id, @rate_place_id) .TYPE_ASSIGN(stock_id, @stock_id) .TYPE_ASSIGN(rate_date, @rate_date) .TYPE_ASSIGN(rate_cur_id, @price_cur_id) .TYPE_ASSIGN(list_id, @list_id) .TYPE_ASSIGN(stock_rate, @market_price) .TYPE_ASSIGN(average, @average) .TYPE_ASSIGN(average_bid10, @average_bid10) .TYPE_ASSIGN(average_bid, @average_bid) .TYPE_ASSIGN(average_bid_pd, @average_bid_pd) .TYPE_ASSIGN(max_price, @max_price) .TYPE_ASSIGN(min_price, @min_price) .TYPE_ASSIGN(trades_num, @trades_num) .TYPE_ASSIGN(volume, @volume) .TYPE_ASSIGN(volume_cur_id, @volume_cur_id) .TYPE_ASSIGN(money_volume, @money_volume) .TYPE_ASSIGN(coupon_rate, @nkd) -- Добавлено .TYPE_ASSIGN(ask, @ask) .TYPE_ASSIGN(bid, @bid) .TYPE_ASSIGN(last_price, @last_price) .TYPE_ASSIGN(acknowledged_price, @acknowledged_price) -- Для ПИФ .TYPE_ASSIGN(rate_type, 1) .TYPE_INSERT('TB_STOCK_RATES', 'CRT') end else begin -- Update Record update tb_stock_rates set stock_rate = @market_price, average = @average, rate_cur_id = @price_cur_id, list_id = @list_id, average_bid10 = @average_bid10, average_bid = @average_bid, average_bid_pd = @average_bid_pd, volume = @volume, volume_cur_id = @volume_cur_id, money_volume = @money_volume, trades_num = @trades_num, max_price = @max_price, min_price = @min_price, coupon_rate = @nkd, ask = @ask, bid = @bid, last_price = @last_price, acknowledged_price = @acknowledged_price where id = @id end fetch cstock into @stock_id end -- End LOOP commit tran close cstock .DEALLOCATE cstock drop table #stockrate end $ENDTEXT(13122914) $ENDACTION $ACTION(GET_STOCK_MARKET_PRICE) name=#Расчёт рыночной стоимости на дату class=9 form=null target_state=null procedure=ap_get_stock_market_price patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out as begin -- ДЛЯ ПРОСПЕРИТИ declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @place_id numeric, @rts_id numeric, @board_id numeric --declare @result numeric(18,8), @bid numeric(18,8), @ask numeric(18,8), @last_price numeric(18,8) declare @result decimal(18,8), @bid decimal(18,8), @ask decimal(18,8), @last_price decimal(18,8) .NAME_VAR(@mess) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return declare @broker_id numeric, @owner_id numeric, @plan_money_id numeric, @plan_secur_id numeric, @out_met_id int, @is_realiz int, @is_p2l_com int, @is_com_in int, @is_allow_short4stocks int, @is_allow_short4money int, @asset4pereoc_type_id int if @cur_id is null select @cur_id = .CUR('USD') select @rts_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') select @board_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS_BOARD' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') declare @mmvb_id numeric select @mmvb_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'MICEX_FOND' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') -------------------------Если это Просперити и тип бумаги - облигация, то котировку берем с ММВБ-------indi declare @const varchar(50) select @const = (.GET_CONST('CLIENT_CODE')) ---------Находим тип. Базовый тип ЦБ [простой справочник 3060] declare @stock_type numeric select @stock_type = (select t.stock_type_id from dbo.td_stock_emis s,t_stock_types t,t_simples where s.id = @stock_id and s.stock_type_id = t.id and t_simples.id = t.stock_type_id and t_simples.type_id = 3060) if (isnull(@const, '-1') <> '-1') and (@const = 'PROSPERITY') and isnull(@stock_type,0) = 2 begin select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id = @mmvb_id end else begin ------------------------------------------------- /* select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) select * --@last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) */ select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id in (@rts_id, @board_id) -- = @place_id end if (@last_price < 0) or (@last_price > 9000000000) begin select @mess = 'Ошибка в котировке LAST_PRICE = ' + .TO_STR(@last_price) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@bid < 0) or (@bid > 9000000000) begin select @mess = 'Ошибка в котировке BID = ' + .TO_STR(@bid) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@ask < 0) or (@ask > 9000000000) begin select @mess = 'Ошибка в котировке ASK = ' + .TO_STR(@ask) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end --select @last_price, @bid, @ask, @rate_cur_id, @rate_place_id, @cur_id if @rate_cur_id is null -- Исходная валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id = @place_id if @rate_cur_id is null select @rate_cur_id = .CUR('USD') if isnull(@last_price, -1) <> -1 begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin if @last_price between @bid and @ask select @result = @last_price else select @result = (@bid + @ask)/2 end else return end else begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin select @result = (@bid + @ask)/2 end else return end --select @last_price, @result if @result is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @result else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @result, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122914) $ENDACTION $ACTION(GET_STOCK_PRICE_ONLINE) name=#Расчёт рыночной стоимости для онлайн-мониторинга class=9 form=null target_state=null procedure=ap_get_stock_price_online patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out as begin -- ДЛЯ ПРОСПЕРИТИ declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @place_id numeric, @rts_id numeric, @online_id numeric --declare @result numeric(18,8), @bid numeric(18,8), @ask numeric(18,8), @last_price numeric(18,8) declare @result decimal(18,8), @bid decimal(18,8), @ask decimal(18,8), @last_price decimal(18,8) .NAME_VAR(@mess) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return declare @broker_id numeric, @owner_id numeric, @plan_money_id numeric, @plan_secur_id numeric, @out_met_id int, @is_realiz int, @is_p2l_com int, @is_com_in int, @is_allow_short4stocks int, @is_allow_short4money int, @asset4pereoc_type_id int declare @place_const varchar(50) select @place_const = (.GET_CONST('STOCK_RATE_PLACE')) if @cur_id is null select @cur_id = .CUR('USD') declare @const varchar(50) select @const = (.GET_CONST('CLIENT_CODE')) /*-------------------------Если это Просперити и тип бумаги - облигация, то котировку берем с ММВБ-------indi ---------Находим тип. Базовый тип ЦБ [простой справочник 3060] declare @stock_type numeric select @stock_type = (select t.stock_type_id from dbo.td_stock_emis s,t_stock_types t,t_simples where s.id = @stock_id and s.stock_type_id = t.id and t_simples.id = t.stock_type_id and t_simples.type_id = 3060) if (isnull(@const, '-1') <> '-1') and (@const = 'PROSPERITY') and isnull(@stock_type,0) = 2 begin select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id = @mmvb_id end else begin*/ ---------------------------------площадка---------------------------------------------- declare @board_id numeric select @board_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS_BOARD' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') select @rts_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') if (isnull(@place_const ,'') <> '') and (@place_const = 'RTS_ONLINE') select @online_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS_ONLINE' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') ----------------------------расчет котировки----------------------------------------------------------------- select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id = isnull(@online_id,0) IF (isnull(@ask,-1) = -1) and (isnull(@bid,-1) = -1) select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id in (@board_id,@rts_id) if (@last_price < 0) or (@last_price > 9000000000) begin select @mess = 'Ошибка в котировке LAST_PRICE = ' + .TO_STR(@last_price) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@bid < 0) or (@bid > 9000000000) begin select @mess = 'Ошибка в котировке BID = ' + .TO_STR(@bid) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@ask < 0) or (@ask > 9000000000) begin select @mess = 'Ошибка в котировке ASK = ' + .TO_STR(@ask) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if @rate_cur_id is null -- Исходная валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id = @place_id if @rate_cur_id is null select @rate_cur_id = .CUR('USD') if isnull(@last_price, -1) <> -1 begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin if @last_price between @bid and @ask select @result = @last_price else select @result = (@bid + @ask)/2 end else return end else begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin select @result = (@bid + @ask)/2 end else return end --select @last_price, @result if @result is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @result else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @result, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122914) $ENDACTION $ACTION(GET_STOCK_RATE2PLACE) name=#Расчет котировки по площадке class=9 form=null target_state=null procedure=ap_get_stock_rate2place patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122914) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @org_id numeric, /*Организация*/ @cur_id numeric, /*Валюта*/ @rate_place_id numeric, /*Площадка*/ @stock_rate numeric(18,8) out, @type_kotir NUMERIC = 1 as begin declare @rate_id numeric declare @rate_cur_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @stock_rate_val numeric(18,8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') if @rate_place_id is null return -- Дата котировки select @cur_rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates where tb_stock_rates.rate_date <= @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @cur_rate_date is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id=@rate_place_id IF ISNULL(@type_kotir, 1) = 1 SELECT @stock_rate_val = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id ELSE BEGIN SELECT @stock_rate_val = tb_stock_rates.market_price2, @stock_rate = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id IF ISNULL(@stock_rate_val, 0) = 0 SELECT @stock_rate_val = @stock_rate END if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122914) $ENDACTION $ACTION(GET_STOCK_RATE2PLACE2TYPE) name=#Расчет котировки по площадке (тек) class=9 form=null target_state=null procedure=ap_get_stock_rate2type patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @org_id numeric, /*Организация*/ @cur_id numeric, /*Валюта*/ @rate_place_id numeric, /*Площадка*/ @rate_type numeric, /*Тип котировки*/ @stock_rate numeric(18,8) out as declare @rate_id numeric declare @rate_cur_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @stock_rate_val numeric(18,8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') if @rate_place_id is null return -- Дата котировки select @cur_rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates where tb_stock_rates.rate_date <= @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @cur_rate_date is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id=@rate_place_id -- letsko_av -- 12.04.2005 -- Используем различные котировки IF @rate_type IN (0, 1, 2) -- 0,1 и 2 - Рыночная котировка (Значение равное 2 применяется в подсчете маржи) SELECT @stock_rate_val = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id ELSE IF @rate_type = 3 -- Признаваемая котировка (например для проводок по ПИФу) SELECT @stock_rate_val = tb_stock_rates.acknowledged_price FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id -- letsko_av (end) if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_CLASS) name=#Расчет котировки по классификатору class=9 form=null target_state=null procedure=ap_get_stock_rate_class patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @org_id numeric, /*Организация*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out, @type_kotir NUMERIC = 1 as begin declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @stock_rate_val numeric(18,8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @org_id is null select @org_id = .ORGANIZATION if @cur_id is null select @cur_id = .CUR('RUR') -- Площадка exec ap_getclassvalue_code @stock_id, 'STOCK_RATE_PLACE', @rate_date, @org_id, @class_value_code out, @class_comment out if @class_value_code is not null select @rate_place_id = max(td_rate_places.id) from td_rate_places where upper(td_rate_places.place_code) = upper(@class_value_code) and .ITEMS_EXISTS(td_rate_places.id) -- изменил чикунов 28.02.05 -- в результате мы будем смотреть на значение расширенного классификатора, -- и если его нет, то смотрим на дефолтное значение в бумаге if @rate_place_id is null select @rate_place_id = base_place_id from td_stock_emis where id = @stock_id if @rate_place_id is null return -- Дата котировки select @cur_rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates where tb_stock_rates.rate_date <= @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @cur_rate_date is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id=@rate_place_id IF ISNULL(@type_kotir, 1) = 1 SELECT @stock_rate_val = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id ELSE BEGIN SELECT @stock_rate_val = tb_stock_rates.market_price2, @stock_rate = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id IF ISNULL(@stock_rate_val, 0) = 0 SELECT @stock_rate_val = @stock_rate END if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_COUPON) name=#Получить купонный доход class=9 form=null target_state=null procedure=ap_get_stock_rate_coupon patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @coupon_rate numeric(18,8) out as begin declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric select @coupon_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') -- Площадка select @rate_place_id = (select max(tb_stock_rates.rate_place_id) from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.coupon_rate is not null and tb_stock_rates.coupon_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @rate_place_id is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.coupon_rate is not null and tb_stock_rates.coupon_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Котировка купона select @coupon_rate= tb_stock_rates.coupon_rate from tb_stock_rates where id=@rate_id -- Пока валюту не считаем -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id = @rate_place_id if (isnull(@coupon_rate, 0) <> 0) and (@rate_cur_id <> @cur_id) begin declare @cur_rate_value money, @cur_rate_date datetime .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @coupon_rate, @coupon_rate, @cur_rate_value, @cur_rate_date) end /* -- Котировка select @stock_rate_val= tb_stock_rates.stock_rate from tb_stock_rates where id=@rate_id if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end */ end $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_MP) name=#Получить значение Рыночночной цены class=9 form=null target_state=null procedure=ap_get_stock_rate_mp patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out as begin declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @stock_rate_val numeric(18,8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') -- Дата котировки select @cur_rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates where tb_stock_rates.rate_date <= @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @cur_rate_date is null return -- Площадка select @rate_place_id = (select max(tb_stock_rates.rate_place_id) from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @rate_place_id is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id=@rate_place_id -- Котировка select @stock_rate_val= tb_stock_rates.stock_rate from tb_stock_rates where id=@rate_id if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_MP_NKD) name=#Получить значение Рыночночной цены class=9 form=null target_state=null procedure=ap_get_stock_rate_mp_nkd patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out as begin declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18, 8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @stock_rate_val numeric(18, 8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') -- Дата котировки select @cur_rate_date = ( select max(tb_stock_rates.rate_date) from tb_stock_rates where tb_stock_rates.rate_date <= @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id) ) if @cur_rate_date is null return -- Площадка select @rate_place_id = ( select max(tb_stock_rates.rate_place_id) from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id) ) if @rate_place_id is null return -- Котировка select @rate_id = ( select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @cur_rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id) ) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id = @rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id = @rate_place_id -- Котировка if .GET_CONST('RATE_SUM_DIF') = 1 select @stock_rate_val = tb_stock_rates.stock_rate + tb_stock_rates.coupon_rate from tb_stock_rates where id = @rate_id else select @stock_rate_val = tb_stock_rates.stock_rate from tb_stock_rates where id = @rate_id if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_PROSP_REP) name=#Расчёт рыночной стоимости, bid и ask на дату class=9 form=null target_state=null procedure=ap_get_stock_rate_prosp_rep patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out, @ask numeric(18,8) out, @bid numeric(18,8) out as begin -- ДЛЯ ПРОСПЕРИТИ declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @place_id numeric, @rts_id numeric, @board_id numeric --declare @result numeric(18,8), @bid numeric(18,8), @ask numeric(18,8), @last_price numeric(18,8) declare @result decimal(18,8), /*@bid decimal(18,8), @ask decimal(18,8), */ @last_price decimal(18,8) .NAME_VAR(@mess) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return declare @broker_id numeric, @owner_id numeric, @plan_money_id numeric, @plan_secur_id numeric, @out_met_id int, @is_realiz int, @is_p2l_com int, @is_com_in int, @is_allow_short4stocks int, @is_allow_short4money int, @asset4pereoc_type_id int if @cur_id is null select @cur_id = .CUR('USD') select @rts_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') select @board_id = td_rate_places.id from td_rate_places where td_rate_places.place_code = 'RTS_BOARD' and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES') /* select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) select * --@last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) */ select @last_price = last_price, @bid = bid, @ask = ask, @rate_cur_id = rate_cur_id, @place_id = rate_place_id from tb_stock_rates where convert(datetime, convert(varchar(8), tb_stock_rates.rate_date, 3),3) = convert(datetime, convert(varchar(8), @rate_date, 3),3) -- @rate_date and tb_stock_rates.stock_id = @stock_id and .ITEMS_EXISTS(tb_stock_rates.id) and tb_stock_rates.rate_place_id in (@rts_id, @board_id) -- = @place_id if (@last_price < 0) or (@last_price > 9000000000) begin select @mess = 'Ошибка в котировке LAST_PRICE = ' + .TO_STR(@last_price) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@bid < 0) or (@bid > 9000000000) begin select @mess = 'Ошибка в котировке BID = ' + .TO_STR(@bid) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end if (@ask < 0) or (@ask > 9000000000) begin select @mess = 'Ошибка в котировке ASK = ' + .TO_STR(@ask) + '. ЦБ =' + .ITEM_NAME(@stock_id) .EXIT_MESSAGE(@mess) end --select @last_price, @bid, @ask, @rate_cur_id, @rate_place_id, @cur_id if @rate_cur_id is null -- Исходная валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id = @place_id if @rate_cur_id is null select @rate_cur_id = .CUR('USD') /*if isnull(@last_price, -1) <> -1 begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin if @last_price between @bid and @ask select @result = @last_price else select @result = (@bid + @ask)/2 end else return end else begin if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) begin select @result = (@bid + @ask)/2 end else return end*/ /*сделано по письму Мельникова от 20/09/05*/ ------------------------------------------------------------------------------ if (isnull(@last_price, -1) <> -1) and (@last_price between isnull(@bid,0) and isnull(@ask,9000000000))--@bid and @ask) select @result = @last_price else if (isnull(@bid, -1) <> -1) and (isnull(@ask, -1) <> -1) select @result = (@bid + @ask)/2 else return ------------------------------------------------------------------------------ --select @last_price, @result if @result is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @result else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @result, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122915) $ENDACTION $ACTION(IMPORT_DAYLY_PLAZA) name=Принудительный импорт котировок RTS Plaza class=9 form=null target_state=null procedure=ap_dayly_import_plaza patterns=GENERIC_MOVE user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% --@sid varchar(30) = null as begin /******************************************************************************************************** Создал процедуру Богаковский 10/11/2005 Первоначально процедура создается для Просперити для импорта котировок из RTS Plaza в случае, если котировка ЦБ сегодня не менялась. Необходимосоздать таблицу tb_last_date_plaza с полем last_date, если она еще не создана CREATE TABLE tb_last_date_plaza (last_date varchar(10) null) Процедура должна выполняться один раз в день. Если процедура уже выполнялась, то осуществляется выход из процедуры в самом начале выполнения. При запуске процедуры по какому-либо событию или вручную, курсор выбирает все не помеченные звездочкой в поле deleted записи таблицы Issue и для каждой записи проверяет, имеется ли котировка для данной ЦБ, на данной площадке за сегодняшнюю дату. Если такая котировка отсутствует, то котировка из Issue добавляется в Аладдин (она может быть за вчера или за более раннюю дату). Полное имя таблицы Issue в Просперити: [PROSPERITY\Rts].Issue. Эта процедура работает в том случае, если у клиента установлен продукт ALD_RTS_PLAZA. Если потребуется использование данной процедуры для других продуктов интернет-трейдинга, то следует создать соответствующие конструкции, обрамленные #ifdef для этих продуктов. *********************************************************************************************************/ declare @today varchar(10) declare @count_row int select @today = null select @count_row = (select count(last_date) from tb_last_date_plaza) -- Если в таблице, где хранится дата последнего выполнения процедуры, имеется единственная запись, -- то присвоить эту запись (в таблице единственное поле) переменной if @count_row = 1 select @today = (select last_date from tb_last_date_plaza) -- Если в таблице, где хранится дата последнего выполнения процедуры: -- несколько записей или -- записи отсутствуют или -- запись не является датой, -- то очистить таблицу и вставить запись с прошедшей датой if isdate(@today)=0 or @count_row <> 1 begin truncate table tb_last_date_plaza insert tb_last_date_plaza (last_date) values ('01/01/2005') select @today = '01/01/2005' end if @today is null select @today = '01/01/2005' -- Если сегодня эта процедура уже срабатывала, то выйти из процедуры if @today = convert(varchar(10), getdate(), 103) return begin tran declare @moment_d datetime, @last_price_n decimal(18,8), @ask_price_n decimal(18,8), @bid_price_n decimal(18,8), @ask_price_s varchar(255), @bid_price_s varchar(255), @moment varchar(30), @issue varchar(255), @full_name varchar(255), @last_price_s varchar(255), @price_currency varchar(50), @rate_place_code varchar(50), @stock_id numeric, @count_stock int -- Начало Принудительный импорт всех котировок из RTS Plaza #ifdef ALD_RTS_PLAZA -- Курсор по всем записям из таблицы Issue, кроме помеченных звездочкой в поле deleted declare cur_rates cursor for select [name], full_name, bid_inside_price, ask_inside_price, trade_price, price_currency from /*[PROSPERITY\Rts].*/ Issue where deleted <> '*' or isnull(rtrim(ltrim(deleted)), '') = '' FOR READ ONLY open cur_rates fetch cur_rates into @issue, @full_name, @bid_price_n, @ask_price_n, @last_price_n, @price_currency while(@@fetch_status=0) begin -- Торговая площадка ------------------------------------------- if upper(ltrim(rtrim(@price_currency))) = 'RUR' select @rate_place_code = 'RTS_ONLINE_G' else if upper(ltrim(rtrim(@price_currency))) = 'USD' select @rate_place_code = 'RTS_ONLINE' else select @rate_place_code = 'UNKNOWN PLACE' ------------------------------------------------------------------------------- select @last_price_s = convert(varchar(255), @last_price_n), @bid_price_s = convert(varchar(255), @bid_price_n), @ask_price_s = convert(varchar(255), @ask_price_n) /*** Начало Вычисление ID ЦБ по коду на площадке *************************************************/ select @count_stock=(select count(distinct t_item2class.item_id) from t_item2class,t_classes,t_classvalues where upper(ltrim(rtrim(t_item2class.code)))=upper(ltrim(rtrim(@issue))) and upper(ltrim(rtrim(t_classes.code)))='SALE_STOCKS' and t_classvalues.class_id=t_classes.id and t_item2class.value_id=t_classvalues.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id,'item2classvalues') and .ITEMS_EXISTS(t_item2class.item_id)) if @count_stock=1 begin select @stock_id=t_item2class.item_id from t_item2class,t_classes,t_classvalues where upper(ltrim(rtrim(t_item2class.code)))=upper(ltrim(rtrim(@issue))) and upper(ltrim(rtrim(t_classes.code)))='SALE_STOCKS' and t_classvalues.class_id=t_classes.id and t_item2class.value_id=t_classvalues.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id,'item2classvalues') and .ITEMS_EXISTS(t_item2class.item_id) end /*** Конец Вычисление ID ЦБ по коду на площадке ****************************************************/ -- Если нет котировки за сегодня для данной ЦБ на данной площадки, то -- импортировать в Аладдин ту котировку, которая -- на данный момент находится в таблице Issue (т.е. последнюю котировку). if not exists(select 1 from tb_stock_rates, td_rate_places where tb_stock_rates.rate_place_id = td_rate_places.id and upper(rtrim(ltrim(td_rate_places.place_code))) = upper(rtrim(ltrim(@rate_place_code))) and convert(varchar(10), tb_stock_rates.rate_date, 103) = convert(varchar(10), getdate(), 103) and tb_stock_rates.stock_id=@stock_id and .ITEMS_EXISTS_BY_TYPE(tb_stock_rates.id,'TB_STOCK_RATES') and .ITEMS_EXISTS_BY_TYPE(td_rate_places.id,'RATE_PLACES')) begin exec ap_plaza_msg @rate_place_code, null, @issue, @full_name, @last_price_s, @bid_price_s, @ask_price_s, @price_currency end fetch cur_rates into @issue, @full_name, @bid_price_n, @ask_price_n, @last_price_n, @price_currency end close cur_rates .DEALLOCATE cur_rates -- Пометить, что процедура уже выполнялась сегодня update tb_last_date_plaza set last_date = convert(varchar(10), getdate(), 103) #endif -- Конец Принудительный импорт всех котировок из RTS Plaza commit tran end $ENDTEXT(13122915) $ENDACTION $ACTION(RATE_TEST_IMPORT) name=Тестовый импорт class=1 form=RATE_TEST_LIST target_state=null procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDACTION $ACTION(STOCKS_SPFB_IMPORT_EXCEL) name=Импорт котировок СПФБ(excel)[csv] class=1 form=mmvb_csv_form target_state=CRT procedure=ap_21535000000996816 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @f1 varchar(255) = null, @f2 varchar(255) = null, --Наименование ЦБ @f3 varchar(255) = null, @f4 varchar(255) = null, @f5 varchar(255) = null, --/*Цена лучшей заявки на покупку*/ @f6 varchar(255) = null, --/*Цена лучшей заявки на продажу*/ @f7 varchar(255) = null, --/* Минимальная цена */ @f8 varchar(255) = null, --/* Максимальная цена */ @f9 varchar(255) = null, @f10 varchar(255) = null, --/*Цена последней сделки*/ @f11 varchar(255) = null, --/* Лучшие покупки */ @f12 varchar(255) = null, --/* Рыночная */, /* Средневзвешенная */, /*Признаваемая котировка по ФКЦБ*/ @f13 varchar(255) = null, @f14 varchar(255) = null, @f15 varchar(255) = null, --/* Количество сделок */ @f16 varchar(255) = null, --/* Объем в шт */ @f17 varchar(255) = null, --/* Объем в валюте */ @f18 varchar(255) = null, @f19 varchar(255) = null, @f20 varchar(255) = null, @f21 varchar(255) = null, @f22 varchar(255) = null, @f23 varchar(255) = null, @f24 varchar(255) = null, @f25 varchar(255) = null, @f26 varchar(255) = null, @f27 varchar(255) = null, @f28 varchar(255) = null, @f29 varchar(255) = null, @f30 varchar(255) = null, @f31 varchar(255) = null, @f32 varchar(255) = null, @f33 varchar(255) = null, @f34 varchar(255) = null, @f35 varchar(255) = null, @f36 varchar(255) = null, @f37 varchar(255) = null, @f38 varchar(255) = null, @f39 varchar(255) = null, @f40 varchar(255) = null, @f41 varchar(255) = null, @f42 varchar(255) = null, @f43 varchar(255) = null, @f44 varchar(255) = null, @f45 varchar(255) = null, @f46 varchar(255) = null, @f47 varchar(255) = null, @f48 varchar(255) = null, @f49 varchar(255) = null, @f50 varchar(255) = null as begin declare @nachalo int if substring(@f1,1,1) in ('1','2','3','4','5','6','7','8','9','0') ---строка начинается с номера котировки select @nachalo=1 if @nachalo=1 begin select @f1= ltrim(rtrim(@f1)) select @f2= ltrim(rtrim(@f2)) select @f3= ltrim(rtrim(@f3)) select @f4= ltrim(rtrim(@f4)) select @f5= ltrim(rtrim(@f5)) select @f6= ltrim(rtrim(@f6)) select @f7= ltrim(rtrim(@f7)) select @f8= ltrim(rtrim(@f8)) select @f9= ltrim(rtrim(@f9)) select @f10= ltrim(rtrim(@f10)) select @f11= ltrim(rtrim(@f11)) select @f12= ltrim(rtrim(@f12)) select @f13= ltrim(rtrim(@f13)) select @f14= ltrim(rtrim(@f14)) select @f15= ltrim(rtrim(@f15)) select @f16= REPLACE((ltrim(rtrim(@f16))),' ','') select @f17= REPLACE((ltrim(rtrim(@f17))),' ','') declare @moment varchar(30) /* Дата */ declare @issue varchar(30) /* код ЦБ */ declare @nkd decimal(18,8) /* НКД */ declare @quote_list varchar(30) /* Листинг*/-- declare @par decimal(18,8) /* Номинал */-- declare @average_bid10 decimal(18,8) /* Лучшие 10 предложений */ -- declare @average_bid_pd decimal(18,8) /* Лучшие покупки маркет-мейкеров*/ -- declare @rate_place_id_s varchar(30) /* Код площадки */ -- ??? declare @par_currency varchar(30) /* Валюта номинала */ declare @price_currency varchar(30) /* Валюта */ declare @volume_currency varchar(30) /* Валюта для объемов торгов */ declare @money_volume money /* Объем в валюте */ declare @volume money /* Объем в шт */ declare @trades_num money /* Количество сделок */ declare @average decimal(18,8) /* Средневзвешенная */ declare @market_price decimal(18,8) /* Рыночная */ declare @average_bid decimal(18,8) /* Лучшие покупки */ declare @max_price decimal(18,8) /* Максимальная цена */ declare @min_price decimal(18,8) /* Минимальная цена */ declare @ask decimal(18,8) /*Цена лучшей заявки на продажу*/ declare @bid decimal(18,8) /*Цена лучшей заявки на покупку*/ declare @last_price decimal(18,8) /*Цена последней сделки*/ declare @acknowledged_price decimal(18,8) /*Признаваемая котировка по ФКЦБ*/ declare @rate_place_id numeric declare @classvalue_id numeric declare @cnt int declare @stock_id numeric .REPLACE(@f17,',','.',@f17) select @money_volume=convert(money,@f17) .REPLACE(@f16,',','.',@f16) select @volume=convert(money,@f16) .REPLACE(@f15,',','.',@f15) select @trades_num=convert(money,@f15) .REPLACE(@f12,',','.',@f12) select @average=convert(decimal(18,8),@f12) select @market_price=convert(decimal(18,8),@f12) select @acknowledged_price=convert(decimal(18,8),@f12) .REPLACE(@f11,',','.',@f11) select @average_bid=convert(decimal(18,8),@f11) .REPLACE(@f8,',','.',@f8) select @max_price=convert(decimal(18,8),@f8) .REPLACE(@f7,',','.',@f7) select @min_price=convert(decimal(18,8),@f7) .REPLACE(@f6,',','.',@f6) select @ask=convert(decimal(18,8),@f6) .REPLACE(@f5,',','.',@f5) select @bid=convert(decimal(18,8),@f5) .REPLACE(@f10,',','.',@f10) select @last_price=convert(decimal(18,8),@f10) --select @issue= @f2 select @f2=@f2+' '+@f3 select @par_currency='RUR' select @price_currency='RUR' select @volume_currency='RUR' select @moment=(select convert(char(12),.OPERDAY,110)) select @moment=substring(@moment,4,2)+'/'+substring(@moment,1,2)+'/'+substring(@moment,9,2) select @rate_place_id_s = convert(varchar,(select td_rate_places.id from td_rate_places where td_rate_places.place_code='SPFB') ) select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') select @issue= ( select td_stock_emis.stock_code from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@f2) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') ) select @stock_id= ( select td_stock_emis.id from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@f2) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') ) select @cnt = (select count (distinct tb_stock_rates.id) from tb_stock_rates where tb_stock_rates.rate_place_id= (select td_rate_places.id from td_rate_places where td_rate_places.place_code='SPFB') and tb_stock_rates.stock_id=@stock_id and tb_stock_rates.rate_date=.OPERDAY ) if @cnt>0 .EXIT_MESSAGE('Котировка на этот день уже есть') exec ap_stock_rates_imp @moment, @issue, @quote_list , @par, @par_currency, @price_currency, @volume_currency, @money_volume, @volume, @trades_num, @average, @market_price, @average_bid10, @average_bid, @average_bid_pd, @rate_place_id_s, @max_price, @min_price, @ask, @bid, @last_price, @acknowledged_price, @nkd end end $ENDTEXT(13122915) $ENDACTION $ACTION(STOCK_RATES_IMPORT) name=Импорт (РТС, ММВБ,СПВБ) class=1 form=STOCK_RATES_IMPORT_LIST target_state=CRT procedure=ap_stock_rates_imp patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @moment varchar(30) /* Дата */, @issue varchar(30) /* код ЦБ */, @quote_list varchar(30) /* Листинг*/, @par decimal(18,8) /* Номинал */, @par_currency varchar(30) /* Валюта номинала */, @price_currency varchar(30) /* Валюта */, @volume_currency varchar(30) /* Валюта для объемов торгов */, @money_volume money /* Объем в валюте */, @volume money /* Объем в шт */, @trades_num money /* Количество сделок */, @average decimal(18,8) /* Средневзвешенная */, @market_price decimal(18,8) /* Рыночная */, @average_bid10 decimal(18,8) /* Лучшие 10 предложений */, @average_bid decimal(18,8) /* Лучшие покупки */, @average_bid_pd decimal(18,8) /* Лучшие покупки маркет-мейкеров*/, @rate_place_id_s varchar(30) /* Код площадки */, @max_price decimal(18,8) /* Максимальная цена */, @min_price decimal(18,8) /* Минимальная цена */, -- Добавлено @ask decimal(18,8) = null /*Цена лучшей заявки на продажу*/, @bid decimal(18,8) = null /*Цена лучшей заявки на покупку*/, @last_price decimal(18,8) = null /*Цена последней сделки*/, @acknowledged_price decimal(18,8) = null /*Признаваемая котировка по ФКЦБ*/, -- @nkd decimal(18,8) = null /* НКД */ as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% .CHECK_USER .CHECK_INSERT declare @rate_date datetime declare @stock_code varchar(30) declare @rate_place_id numeric declare @classvalue_id numeric declare @stock_id numeric declare @plase_cur_id numeric declare @par_cur_id numeric declare @price_cur_id numeric declare @rate_sum money declare @rate_value decimal(18, 8) declare @temp_date datetime declare @volume_cur_id numeric declare @list_id numeric --если все значимые поля не заполнены - сразу нафиг --для облигации может быть все не заполнено, а вот НКД быть не пустым. добавил НКД -- Добавлена признаваемая котировка и количество сделок (если есть сделки то есть ask,last,max... и пр) if (isnull(@average,0) = 0 and isnull(@market_price,0) = 0 and isnull(@average_bid10,0) = 0 and isnull(@average_bid,0) = 0 and isnull(@average_bid_pd,0) = 0 and isnull(@nkd,0) = 0 and isnull(@trades_num,0) = 0 and isnull(@acknowledged_price,0) = 0 and ((isnull(@ask ,0) = 0) or (isnull(@bid ,0) = 0)) ) return select @rate_date = convert(datetime, substring(@moment, 1, 8), 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') select @stock_code = rtrim(ltrim(UPPER(@issue))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') -- Площадка select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') -- Объем торгов select @volume_cur_id = .CUR(@volume_currency) if @volume_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта объема торгов с кодом', @volume_currency) -- Листинг select @list_id = 0 select @quote_list = isnull(upper(@quote_list), 'NOT_DEFINED') if @quote_list in ('A1','EQBR','EQOB') select @list_id = 1 else if @quote_list in ('A2','EQBS','EQOS') select @list_id = 2 else if @quote_list in ('B','EQNL','EQNO') select @list_id = 3 else if @quote_list in ('PSAU') select @list_id = 4 select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') create table #stockrate( stock_id numeric ) insert into #stockrate(stock_id) select td_stock_emis.id from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@stock_code) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id from #stockrate for read only open cstock begin tran fetch cstock into @stock_id while (.CURSOR_STATE = 0) -- LOOP begin -- Обработка данных if upper(@price_currency) = 'PCT' begin -- Котировка в процентах select @price_cur_id = .CUR(@par_currency) if @price_cur_id is null select @price_cur_id = (select td_stock_emis.stock_curr_id from td_stock_emis where td_stock_emis.id = @stock_id) if @price_cur_id is null .EXIT_MESSAGE('Для ЦБ не указана валюта номинала') select @plase_cur_id = @par_cur_id if @par is null select @par = (select td_stock_emis.stock_nominal from td_stock_emis where td_stock_emis.id = @stock_id) if @par is null .EXIT_MESSAGE('Для ЦБ не указан номинал') select @average = round((@par * @average / 100.0), 8) select @market_price = round((@par * @market_price / 100.0), 8) select @average_bid10 = round((@par * @average_bid10 / 100.0), 8) select @average_bid = round((@par * @average_bid / 100.0), 8) select @average_bid_pd = round((@par * @average_bid_pd / 100.0), 8) select @max_price = round((@par * @max_price / 100.0), 8) select @min_price = round((@par * @min_price / 100.0), 8) select @ask = round((@par * @ask / 100.0), 8) select @bid = round((@par * @bid / 100.0), 8) select @last_price = round((@par * @last_price / 100.0), 8) select @acknowledged_price = round((@par * @acknowledged_price / 100.0), 8) end else begin -- Котировка в валюте select @price_cur_id = .CUR(@price_currency) if @price_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта значений с кодом', @price_currency) end select @id = null select @id = tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_date = @rate_date and .ITEMS_EXISTS(tb_stock_rates.id) if @id is null begin -- New Record --.EXIT_MESSAGE('OK') .TYPE_INIT('TB_STOCK_RATES') .TYPE_ASSIGN(rate_place_id, @rate_place_id) .TYPE_ASSIGN(stock_id, @stock_id) .TYPE_ASSIGN(rate_date, @rate_date) .TYPE_ASSIGN(rate_cur_id, @price_cur_id) .TYPE_ASSIGN(list_id, @list_id) .TYPE_ASSIGN(stock_rate, @market_price) .TYPE_ASSIGN(average, @average) .TYPE_ASSIGN(average_bid10, @average_bid10) .TYPE_ASSIGN(average_bid, @average_bid) .TYPE_ASSIGN(average_bid_pd, @average_bid_pd) .TYPE_ASSIGN(max_price, @max_price) .TYPE_ASSIGN(min_price, @min_price) .TYPE_ASSIGN(trades_num, @trades_num) .TYPE_ASSIGN(volume, @volume) .TYPE_ASSIGN(volume_cur_id, @volume_cur_id) .TYPE_ASSIGN(money_volume, @money_volume) .TYPE_ASSIGN(coupon_rate, @nkd) -- Добавлено .TYPE_ASSIGN(ask, @ask) .TYPE_ASSIGN(bid, @bid) .TYPE_ASSIGN(last_price, @last_price) .TYPE_ASSIGN(acknowledged_price, @acknowledged_price) -- Для ПИФ .TYPE_ASSIGN(rate_type, 1) .TYPE_INSERT('TB_STOCK_RATES', 'CRT') end else begin -- Update Record update tb_stock_rates set stock_rate = @market_price, average = @average, rate_cur_id = @price_cur_id, list_id = @list_id, average_bid10 = @average_bid10, average_bid = @average_bid, average_bid_pd = @average_bid_pd, volume = @volume, volume_cur_id = @volume_cur_id, money_volume = @money_volume, trades_num = @trades_num, max_price = @max_price, min_price = @min_price, coupon_rate = @nkd, ask = @ask, bid = @bid, last_price = @last_price, acknowledged_price = @acknowledged_price where id = @id end fetch cstock into @stock_id end -- End LOOP commit tran close cstock .DEALLOCATE cstock drop table #stockrate end $ENDTEXT(13122915) $ENDACTION $ACTION(STOCK_RATES_IMPORT_NEW) name=Импорт (РТС, ММВБ,СПВБ) новый class=1 form=STOCK_RATES_IMPORT_LIST_NEW target_state=CRT procedure=ap_stock_rates_imp_new patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=3351 height=2020 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @rate_place_id_s varchar(30) /* Код площадки */, @moment varchar(30) /* Дата */, @quote_list varchar(255) /* Наименование котировального листа*/, @issue varchar(255) /* код ЦБ */, @type varchar(255) /*Тип ЦБ*/, @par varchar(255) /* Номинал */, @par_currency varchar(255) /* Валюта номинала */, @average varchar(255) /* Средневзвешенная цена */, @price_currency varchar(255) /* Валюта всех цен */, @market_price varchar(255) /* Рыночная цена (1) */, @market_price2 varchar(255) /* Рыночная цена (2) */, @acknowledged_price varchar(255) = null /* Признаваемая котировка по ФКЦБ*/, @nkd varchar(255) = null /* НКД */, @money_volume varchar(255) /* Объем в валюте */, @volume_currency varchar(255) /* Валюта для объемов торгов */, @m_price_currency varchar(255) /* Валюта рыночных цен и признаваемой котировки */, @volume varchar(255) /* Объем в шт */, @trades_num varchar(255) /* Количество сделок */, @short_name varchar(255) /* Краткое наименование ЦБ*/, @max_price varchar(255) /* Максимальная цена */, @min_price varchar(255) /* Минимальная цена */, @ask varchar(255) = null /* Цена предложения (котировка на продажу) на момент окончания торговой сессии */, @bid varchar(255) = null /* Цена спроса (котировка на покупку) на момент окончания торговой сессии */, --------------------------------------------------------------------------------нет в файле @average_bid10 varchar(255)=null /* Лучшие 10 предложений */, @average_bid varchar(255)=null /* Лучшие покупки */, @average_bid_pd varchar(255)=null /* Лучшие покупки маркет-мейкеров*/, -------------------------------------------------------------------------------- @last_price varchar(255)= null /* Цена последней сделки*/, @board_code varchar(255)= null /* Код режима торгов */, @board_name varchar(255)= null /* Наименование режима торгов */, @open_period varchar(255)= null /* Цена предторгового периода */, @open varchar(255)= null /* Цена первой сделки */, @close_period varchar(255)= null /* Цена послеторгового периода */, @low_offer varchar(255)= null /* Наименьшая цена предложения (минимальная котировка на продажу) */, @high_bid varchar(255)= null /* Наибольшая цена спроса (максимальная котировка на покупку) */, @yield_close varchar(255)= null /* Доходность по цене последней сделки, % годовых */, @yield_wap varchar(255)= null /* Доходность по средневзвешенной цене, % годовых */, @issue_size varchar(255)= null /* Объем обращения, в ценных бумагах */, @open_price varchar(255)= null /* Цена открытия */, @close_price varchar(255)= null /* Цена закрытия */, @open_value varchar(255)= null /* Объем первой сделки */, @close_value varchar(255)= null /* Объем последней сделки */, @chek1 integer=1 /* Нужно ли закачивать (значение флажка напротив каждой записи) */ -- @redemption_date varchar(30) = null /* Дата погашения */, -- @market_price2 money = null /* Рыночная цена (2) */, -- @prev_close_price money = null /* Цена закрытия предыдущего торгового дня рублей */, -- @trend_close money = null /* Изменение цены закрытия к цене закрытия предыдущего торгового дня */, -- @trend_wap money = null /* Изменение средневзвешенной цены к средневзвешенной цене предыдущего торгового дня */, -- @prev_last_price money = null /* Цена последней сделки предыдущего торгового дня */, -- @trend_cls_price money = null /* Изменение цены закрытия к цене закрытия предыдущего торгового дня, % */, -- @trend_wap_price money = null /* Изменение средневзвешенной цены к средневзвешенной цене предыдущего торгового дня, % */, as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin if @chek1=0 return ----------------------------------------декларим числовые DECLARE @par1 decimal(18,8), @par_currency1 decimal(18,8), @average1 decimal(18,8), @market_price1 decimal(18,8), @market_price21 decimal(18,8), @acknowledged_price1 decimal(18,8), @nkd1 decimal(18,8), @money_volume1 money, @volume1 money, @trades_num1 money, @max_price1 decimal(18,8), @min_price1 decimal(18,8), @ask1 decimal(18,8), @bid1 decimal(18,8), @last_price1 decimal(18,8), @open_period1 decimal(18,8), @open1 decimal(18,8), @close_period1 decimal(18,8), @low_offer1 decimal(18,8), @high_bid1 decimal(18,8), @yield_close1 money, @yield_wap1 money, @issue_size1 money, @open_price1 decimal(18,8), @close_price1 decimal(18,8), @open_value1 money, @close_value1 money, @average_bid101 decimal(18,8), @average_bid1 decimal(18,8), @average_bid_pd1 decimal(18,8) ------------------------------------------------------------ If (UPPER(Rtrim(ltrim( @par )))) ='-' SELECT @par = null If (UPPER(Rtrim(ltrim( @average )))) ='-' SELECT @average = null If (UPPER(Rtrim(ltrim( @market_price)))) ='-' SELECT @market_price= null If (UPPER(Rtrim(ltrim( @market_price2)))) ='-' SELECT @market_price2= null If (UPPER(Rtrim(ltrim( @acknowledged_price)))) ='-' SELECT @acknowledged_price= null If (UPPER(Rtrim(ltrim( @nkd)))) ='-' SELECT @nkd= null If (UPPER(Rtrim(ltrim( @money_volume)))) ='-' SELECT @money_volume= null If (UPPER(Rtrim(ltrim( @volume)))) ='-' SELECT @volume= null If (UPPER(Rtrim(ltrim( @trades_num)))) ='-' SELECT @trades_num= null If (UPPER(Rtrim(ltrim( @max_price)))) ='-' SELECT @max_price= null If (UPPER(Rtrim(ltrim( @min_price)))) ='-' SELECT @min_price= null If (UPPER(Rtrim(ltrim( @ask)))) ='-' SELECT @ask= null If (UPPER(Rtrim(ltrim( @bid)))) ='-' SELECT @bid= null If (UPPER(Rtrim(ltrim( @last_price)))) ='-' SELECT @last_price= null If (UPPER(Rtrim(ltrim( @open_period)))) ='-' SELECT @open_period= null If (UPPER(Rtrim(ltrim( @open)))) ='-' SELECT @open= null If (UPPER(Rtrim(ltrim( @close_period)))) ='-' SELECT @close_period= null If (UPPER(Rtrim(ltrim( @low_offer)))) ='-' SELECT @low_offer= null If (UPPER(Rtrim(ltrim( @high_bid)))) ='-' SELECT @high_bid= null If (UPPER(Rtrim(ltrim( @yield_close)))) ='-' SELECT @yield_close= null If (UPPER(Rtrim(ltrim( @yield_wap)))) ='-' SELECT @yield_wap= null If (UPPER(Rtrim(ltrim( @issue_size)))) ='-' SELECT @issue_size= null If (UPPER(Rtrim(ltrim( @open_price)))) ='-' SELECT @open_price= null If (UPPER(Rtrim(ltrim( @close_price)))) ='-' SELECT @close_price= null If (UPPER(Rtrim(ltrim( @open_value)))) ='-' SELECT @open_value= null If (UPPER(Rtrim(ltrim( @close_value)))) ='-' SELECT @close_value= null If (UPPER(Rtrim(ltrim( @average_bid10)))) ='-' SELECT @average_bid10= null If (UPPER(Rtrim(ltrim( @average_bid)))) ='-' SELECT @average_bid= null If (UPPER(Rtrim(ltrim( @average_bid_pd)))) ='-' SELECT @average_bid_pd= null ------------------------------------------------------------фигачим SELECT @par1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @par )),',','.'))), --@par_currency1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @par_currency)),',','.'))), @average1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @average)),',','.'))), @market_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @market_price)),',','.'))), @market_price21 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @market_price2)),',','.'))), @acknowledged_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @acknowledged_price)),',','.'))), @nkd1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @nkd)),',','.'))), @money_volume1 = Convert(money,(replace(Rtrim(ltrim( @money_volume)),',','.'))), @volume1 = Convert(money,(replace(Rtrim(ltrim( @volume)),',','.'))), @trades_num1 = Convert(money,(replace(Rtrim(ltrim( @trades_num)),',','.'))), @max_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @max_price)),',','.'))), @min_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @min_price)),',','.'))), @ask1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @ask)),',','.'))), @bid1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @bid)),',','.'))), @last_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @last_price)),',','.'))), @open_period1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @open_period)),',','.'))), @open1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @open)),',','.'))), @close_period1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @close_period)),',','.'))), @low_offer1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @low_offer)),',','.'))), @high_bid1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @high_bid)),',','.'))), @yield_close1 = Convert(money,(replace(Rtrim(ltrim( @yield_close)),',','.'))), @yield_wap1 = Convert(money,(replace(Rtrim(ltrim( @yield_wap)),',','.'))), @issue_size1 = Convert(decimal(18,2),(replace(Rtrim(ltrim( @issue_size)),',','.'))), @open_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @open_price)),',','.'))), @close_price1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @close_price)),',','.'))), @open_value1 = Convert(money,(replace(Rtrim(ltrim( @open_value)),',','.'))), @close_value1 = Convert(money,(replace(Rtrim(ltrim( @close_value)),',','.'))), @average_bid101 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @average_bid10 )),',','.'))), @average_bid1 = Convert(decimal(18,8),(replace(Rtrim(ltrim(@average_bid)),',','.'))), @average_bid_pd1 = Convert(decimal(18,8),(replace(Rtrim(ltrim( @average_bid_pd)),',','.'))) ------------------------------------------------------------ select @action_id = %ACTION_ID% .CHECK_USER .CHECK_INSERT declare @rate_date datetime declare @stock_code varchar(30) declare @rate_place_id numeric declare @classvalue_id numeric declare @stock_id numeric declare @plase_cur_id numeric declare @par_cur_id numeric declare @price_cur_id numeric declare @rate_sum money declare @rate_value decimal(18, 8) declare @temp_date datetime declare @volume_cur_id numeric declare @list_id numeric declare @m_price_cur_id numeric -- Для импорта СПВБ (из файла DBF) -- Если тип ЦБ не задан, а валюта имеет код 'PCT' (проценты от номинала), то считать эту ЦБ облигацией if (isnull(@type,'')='' and (@price_currency = 'PCT')) select @type = 'ОБ' -- Для импорта РТС (из файла Excel) или ММВБ (DBF) (латинская 'B' или 'об' строчными буквами) if upper(rtrim(ltrim(@type))) = 'B' or upper(rtrim(ltrim(@type))) = 'ОБ' select @type = 'ОБ' --если все значимые поля не заполнены - сразу нафиг --для облигации может быть все не заполнено, а вот НКД быть не пустым. добавил НКД -- Добавлена признаваемая котировка и количество сделок (если есть сделки то есть ask,last,max... и пр) if (isnull(@average1,0) = 0 and isnull(@market_price1,0) = 0 -- and isnull(@average_bid10,0) = 0 -- and isnull(@average_bid,0) = 0 -- and isnull(@average_bid_pd,0) = 0 and ((@nkd1 is null) and upper(Rtrim(Ltrim(@type)))='ОБ') and isnull(@trades_num1,0) = 0 and isnull(@acknowledged_price1,0) = 0 and ((isnull(@ask1 ,0) = 0) or (isnull(@bid1 ,0) = 0)) ) .EXIT_MESSAGE('Не заданы основные параметры') --return select @rate_date = convert(datetime, substring(@moment, 1, 8), 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') select @stock_code = rtrim(ltrim(UPPER(@issue))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') -- Площадка select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') --временная затычка --if Rtrim(Ltrim(@volume_currency))='SUR' SELECT @volume_currency='RUR' -- Объем торгов select @volume_cur_id = .CUR(@volume_currency) if @volume_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта объема торгов с кодом', @volume_currency) -- Листинг select @list_id = 0 select @quote_list = isnull(upper(@quote_list), 'NOT_DEFINED') if @quote_list in ('A1', 'А1', 'EQBR','EQOB') select @list_id = 1 else if @quote_list in ('A2', 'А2', 'EQBS','EQOS') select @list_id = 2 else if @quote_list in ('B', 'Б', 'EQNL','EQNO') select @list_id = 3 else if @quote_list in ('PSAU') select @list_id = 4 select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') create table #stockrate( stock_id numeric ) insert into #stockrate(stock_id) select td_stock_emis.id from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@stock_code) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id from #stockrate for read only open cstock begin tran fetch cstock into @stock_id while (.CURSOR_STATE = 0) -- LOOP begin if upper(Ltrim(Rtrim(@type))) = 'ОБ' begin -- Котировка в процентах select @price_cur_id = .CUR((Ltrim(Rtrim(@par_currency)))) if @price_cur_id is null select @price_cur_id = (select td_stock_emis.stock_curr_id from td_stock_emis where td_stock_emis.id = @stock_id) if @price_cur_id is null .EXIT_MESSAGE('Для ЦБ не указана валюта номинала') select @plase_cur_id = @par_cur_id if @par1 is null select @par1 = (select td_stock_emis.stock_nominal from td_stock_emis where td_stock_emis.id = @stock_id) if @par1 is null .EXIT_MESSAGE('Для ЦБ не указан номинал') -- Обработка данных -----------------------------------округление select @average1 = round((@par1 * @average1 / 100.0), 8) select @market_price1 = round((@par1 * @market_price1 / 100.0), 8) select @average_bid101 = round((@par * @average_bid101 / 100.0), 8) select @average_bid1 = round((@par * @average_bid1 / 100.0), 8) select @average_bid_pd1 = round((@par * @average_bid_pd1 / 100.0), 8) select @max_price1 = round((@par1 * @max_price1 / 100.0), 8) select @min_price1 = round((@par1 * @min_price1 / 100.0), 8) select @ask1 = round((@par1 * @ask1 / 100.0), 8) select @bid1 = round((@par1 * @bid1 / 100.0), 8) select @last_price1 = round((@par1 * @last_price1 / 100.0), 8) select @acknowledged_price1 = round((@par1 * @acknowledged_price1 / 100.0), 8) -------------------------------новый select @open_period1=round((@par1 * @open_period1 / 100.0), 8) select @open1=round((@par1 * @open1 / 100.0), 8) select @close_period1=round((@par1 * @close_period1 / 100.0), 8) select @low_offer1=round((@par1 * @low_offer1 / 100.0), 8) select @high_bid1=round((@par1 * @high_bid1 / 100.0), 8) select @yield_close1=round((@par1 * @yield_close1 / 100.0), 8) select @yield_wap1=round((@par1 * @yield_wap1 / 100.0), 8) select @issue_size1=round((@par1 * @issue_size1 / 100.0), 8) select @open_price1=round((@par1 * @open_price1 / 100.0), 8) select @close_price1=round((@par1 * @close_price1 / 100.0), 8) select @market_price21 =round((@par1 * @market_price21 / 100.0), 8) ----------------------------------- end else begin -- Котировка в валюте select @price_cur_id = .CUR((Ltrim(Rtrim(UPPER(@price_currency))))) if @price_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта значений с кодом', @price_currency) end -- Валюта рыночных цен и признаваемой котировки select @m_price_cur_id = .CUR((Ltrim(Rtrim(UPPER(@m_price_currency))))) if @m_price_cur_id is null select @m_price_cur_id = @price_cur_id select @id = null select @id = tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_date = @rate_date and .ITEMS_EXISTS(tb_stock_rates.id) if @id is null begin -- New Record --.EXIT_MESSAGE('OK') .TYPE_INIT('TB_STOCK_RATES') .TYPE_ASSIGN(rate_place_id, @rate_place_id) .TYPE_ASSIGN(stock_id, @stock_id) .TYPE_ASSIGN(rate_date, @rate_date) .TYPE_ASSIGN(rate_cur_id, @price_cur_id) .TYPE_ASSIGN(list_id, @list_id) .TYPE_ASSIGN(stock_rate, @market_price1) .TYPE_ASSIGN(average, @average1) .TYPE_ASSIGN(average_bid10, @average_bid101) .TYPE_ASSIGN(average_bid, @average_bid1) .TYPE_ASSIGN(average_bid_pd, @average_bid_pd1) .TYPE_ASSIGN(max_price, @max_price1) .TYPE_ASSIGN(min_price, @min_price1) .TYPE_ASSIGN(trades_num, @trades_num1) .TYPE_ASSIGN(volume, @volume1) .TYPE_ASSIGN(volume_cur_id, @volume_cur_id) .TYPE_ASSIGN(money_volume, @money_volume1) .TYPE_ASSIGN(coupon_rate, @nkd1) -- Добавлено .TYPE_ASSIGN(ask, @ask1) .TYPE_ASSIGN(bid, @bid1) .TYPE_ASSIGN(last_price, @last_price1) .TYPE_ASSIGN(acknowledged_price, @acknowledged_price1) -- Для ПИФ .TYPE_ASSIGN(rate_type, 1) -----------------------------------------новый кусок .TYPE_ASSIGN(open_period,@open_period1) .TYPE_ASSIGN(open1,@open1) .TYPE_ASSIGN(close_period,@close_period1) .TYPE_ASSIGN(low_offer,@low_offer1) .TYPE_ASSIGN(high_bid,@high_bid1) .TYPE_ASSIGN(yield_close,@yield_close1) .TYPE_ASSIGN(yield_wap,@yield_wap1) .TYPE_ASSIGN(issue_size,@issue_size1) .TYPE_ASSIGN(open_price,@open_price1) .TYPE_ASSIGN(close_price,@close_price1) .TYPE_ASSIGN(open_value,@open_value1) .TYPE_ASSIGN(close_value,@close_value1) .TYPE_ASSIGN(market_price2,@market_price21) .TYPE_ASSIGN(board_code,@board_code) .TYPE_ASSIGN(board_name,@board_name) .TYPE_ASSIGN(m_price_cur_id, @m_price_cur_id) ----------------------------------------- .TYPE_INSERT('TB_STOCK_RATES', 'CRT') end else begin -- Update Record update tb_stock_rates set stock_rate = @market_price1, average = @average1, rate_cur_id = @price_cur_id, list_id = @list_id, average_bid10 = null,--@average_bid10, average_bid = null,--@average_bid, average_bid_pd = null,--@average_bid_pd, volume = @volume1, volume_cur_id = @volume_cur_id, money_volume = @money_volume1, trades_num = @trades_num1, max_price = @max_price1, min_price = @min_price1, coupon_rate = @nkd1, ask = @ask1, bid = @bid1, last_price = @last_price1, acknowledged_price = @acknowledged_price1, open_period=@open_period1 , open1=@open1 , close_period=@close_period1 , low_offer=@low_offer1 , high_bid=@high_bid1 , yield_close=@yield_close1 , yield_wap=@yield_wap1 , issue_size=@issue_size1 , open_price=@open_price1 , close_price=@close_price1 , open_value=@open_value1 , close_value=@close_value1, market_price2 =@market_price21, board_code=@board_code, board_name=@board_name, m_price_cur_id=@m_price_cur_id where id = @id end fetch cstock into @stock_id end -- End LOOP commit tran close cstock .DEALLOCATE cstock drop table #stockrate end $ENDTEXT(13122915) $ENDACTION $ACTION(STOCK_RATES_IMPORT_TLT) name=Импорт (ММВБ ТХБ) class=1 form=IMPORT_THCB_LIST target_state=CRT procedure=ap_21535000000603511 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @moment varchar(30) /* Дата */, @issue varchar(30) /* код ЦБ */, @quote_list varchar(30) /* Листинг*/, @par decimal(18,8) /* Номинал */, @par_currency varchar(30) /* Валюта номинала */, @price_currency varchar(30) /* Валюта */, @volume_currency varchar(30) /* Валюта для объемов торгов */, @money_volume money /* Объем в валюте */, @volume money /* Объем в шт */, @trades_num money /* Количество сделок */, @average decimal(18,8) /* Средневзвешенная */, @market_price decimal(18,8) /* Рыночная */, @average_bid10 decimal(18,8) /* Лучшие 10 предложений */, @average_bid decimal(18,8) /* Лучшие покупки */, @average_bid_pd decimal(18,8) /* Лучшие покупки маркет-мейкеров*/, @rate_place_id_s varchar(30) /* Код площадки */, @max_price decimal(18,8) /* Максимальная цена */, @min_price decimal(18,8) /* Минимальная цена */, -- Добавлено @ask decimal(18,8) = null /*Цена лучшей заявки на продажу*/, @bid decimal(18,8) = null /*Цена лучшей заявки на покупку*/, @last_price decimal(18,8) = null /*Цена лучшей заявки на покупку*/, @acknowledged_price decimal(18,8) = null /*Признаваемая котировка по ФКЦБ*/, -- @nkd decimal(18,8) = null /* НКД */ as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% .CHECK_USER .CHECK_INSERT declare @rate_date datetime declare @stock_code varchar(30) declare @rate_place_id numeric declare @classvalue_id numeric declare @stock_id numeric declare @plase_cur_id numeric declare @par_cur_id numeric declare @price_cur_id numeric declare @rate_sum money declare @rate_value decimal(18, 8) declare @temp_date datetime declare @volume_cur_id numeric declare @list_id numeric --если все значимые поля не заполнены - сразу нафиг --для облигации может быть все не заполнено, а вот НКД быть не пустым. добавил НКД -- Добавлена признаваемая котировка и количество сделок (если есть сделки то есть ask,last,max... и пр) if (isnull(@average,0) = 0 and isnull(@market_price,0) = 0 and isnull(@average_bid10,0) = 0 and isnull(@average_bid,0) = 0 and isnull(@average_bid_pd,0) = 0 and isnull(@nkd,0) = 0 and isnull(@trades_num,0) = 0 and isnull(@acknowledged_price,0) = 0 ) return select @rate_date = convert(datetime, substring(@moment, 1, 8), 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') select @stock_code = rtrim(ltrim(UPPER(@issue))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') -- Площадка select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') -- Объем торгов select @volume_cur_id = .CUR(@volume_currency) if @volume_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта объема торгов с кодом', @volume_currency) -- Листинг if @quote_list is null or upper(@quote_list) in ('EQNB', 'EQNE') select @list_id = 0 else select @list_id = 1 select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') create table #stockrate( stock_id numeric ) insert into #stockrate(stock_id) select td_stock_emis.id from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and upper(t_item2class.code) = upper(@stock_code) and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id from #stockrate open cstock begin tran fetch cstock into @stock_id while (.CURSOR_STATE = 0) -- LOOP begin -- Обработка данных if upper(@price_currency) = 'PCT' begin -- Котировка в процентах select @price_cur_id = .CUR(@par_currency) if @price_cur_id is null select @price_cur_id = (select td_stock_emis.stock_curr_id from td_stock_emis where td_stock_emis.id = @stock_id) if @price_cur_id is null .EXIT_MESSAGE('Для ЦБ не указана валюта номинала') select @plase_cur_id = @par_cur_id if @par is null select @par = (select td_stock_emis.stock_nominal from td_stock_emis where td_stock_emis.id = @stock_id) if @par is null .EXIT_MESSAGE('Для ЦБ не указан номинал') select @average = round((@par * @average / 100.0), 8) select @market_price = round((@par * @market_price / 100.0), 8) select @average_bid10 = round((@par * @average_bid10 / 100.0), 8) select @average_bid = round((@par * @average_bid / 100.0), 8) select @average_bid_pd = round((@par * @average_bid_pd / 100.0), 8) select @max_price = round((@par * @max_price / 100.0), 8) select @min_price = round((@par * @min_price / 100.0), 8) select @ask = round((@par * @ask / 100.0), 8) select @bid = round((@par * @bid / 100.0), 8) select @last_price = round((@par * @last_price / 100.0), 8) select @acknowledged_price = round((@par * @acknowledged_price / 100.0), 8) end else begin -- Котировка в валюте select @price_cur_id = .CUR(@price_currency) if @price_cur_id is null .EXIT_MESSAGE_PARM('Не найдена валюта значений с кодом', @price_currency) end select @id = null select @id = tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.rate_date = @rate_date and .ITEMS_EXISTS(tb_stock_rates.id) if @id is null begin -- New Record --.EXIT_MESSAGE('OK') .TYPE_INIT('TB_STOCK_RATES') .TYPE_ASSIGN(rate_place_id, @rate_place_id) .TYPE_ASSIGN(stock_id, @stock_id) .TYPE_ASSIGN(rate_date, @rate_date) .TYPE_ASSIGN(rate_cur_id, @price_cur_id) .TYPE_ASSIGN(list_id, @list_id) .TYPE_ASSIGN(stock_rate, @market_price) .TYPE_ASSIGN(average, @average) .TYPE_ASSIGN(average_bid10, @average_bid10) .TYPE_ASSIGN(average_bid, @average_bid) .TYPE_ASSIGN(average_bid_pd, @average_bid_pd) .TYPE_ASSIGN(max_price, @max_price) .TYPE_ASSIGN(min_price, @min_price) .TYPE_ASSIGN(trades_num, @trades_num) .TYPE_ASSIGN(volume, @volume) .TYPE_ASSIGN(volume_cur_id, @volume_cur_id) .TYPE_ASSIGN(money_volume, @money_volume) .TYPE_ASSIGN(coupon_rate, @nkd) -- Добавлено .TYPE_ASSIGN(ask, @ask) .TYPE_ASSIGN(bid, @bid) .TYPE_ASSIGN(last_price, @last_price) .TYPE_ASSIGN(acknowledged_price, @acknowledged_price) -- Для ПИФ .TYPE_ASSIGN(rate_type, 1) .TYPE_INSERT('TB_STOCK_RATES', 'CRT') end else begin -- Update Record update tb_stock_rates set stock_rate = @market_price, average = @average, rate_cur_id = @price_cur_id, list_id = @list_id, average_bid10 = @average_bid10, average_bid = @average_bid, average_bid_pd = @average_bid_pd, volume = @volume, volume_cur_id = @volume_cur_id, money_volume = @money_volume, trades_num = @trades_num, max_price = @max_price, min_price = @min_price, coupon_rate = @nkd, ask = @ask, bid = @bid, last_price = @last_price, acknowledged_price = @acknowledged_price where id = @id end fetch cstock into @stock_id end -- End LOOP commit tran close cstock .DEALLOCATE cstock drop table #stockrate end $ENDTEXT(13122915) $ENDACTION $ACTION(STOCK_RATE_COPY) name=Скопировать class=9 form=null target_state=CRT procedure=ap_stock_rate_copy patterns=GENERIC_MOVE user_define=1 isvisible=0 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(13122915) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .GET_PROPS begin tran .ACTION_HISTORY .INSERT_ITEMS .INSERT update tb_stock_rates set comment='Копия' where id=@id .UPDATE_STATE commit tran .END $ENDTEXT(13122915) $ENDACTION $ACTION(AP_GET_STOCK_RATE_FOR_NPF) name=#Расчет котировки для НПФ class=9 form=null target_state=null procedure=ap_get_stock_rate_for_npf patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) CREATE PROCEDURE dbo.%PROC% (@stock_id NUMERIC, /*ЦБ*/ @rate_date DATETIME, /*Дата*/ @org_id NUMERIC, /*Организация*/ @cur_id NUMERIC, /*Валюта*/ @place_id NUMERIC, /*Площадка*/ @stock_rate NUMERIC(18, 8) OUT) AS DECLARE @rate_id NUMERIC DECLARE @rate_cur_id NUMERIC DECLARE @cur_rate_value DECIMAL(18, 8) DECLARE @cur_rate_date DATETIME DECLARE @class_value_code VARCHAR(255) DECLARE @class_comment VARCHAR(255) DECLARE @stock_rate_val NUMERIC(18, 8) SELECT @stock_rate = 0 IF @stock_id IS NULL RETURN IF @rate_date IS NULL RETURN IF @org_id IS NULL SELECT @org_id = .ORGANIZATION IF @cur_id IS NULL SELECT @cur_id = .CUR('RUR') -- Площадка IF @place_id IS NULL BEGIN EXEC ap_getclassvalue_code @stock_id, 'STOCK_RATE_PLACE', @rate_date, @org_id, @class_value_code OUT, @class_comment OUT IF @class_value_code IS NOT NULL SELECT @place_id = MAX(td_rate_places.id) FROM td_rate_places WHERE UPPER(td_rate_places.place_code) = UPPER(@class_value_code) AND .ITEMS_EXISTS(td_rate_places.id) IF @place_id IS NULL SELECT @place_id = base_place_id FROM td_stock_emis WHERE id = @stock_id IF @place_id IS NULL RETURN END -- Дата котировки SELECT @cur_rate_date = ( SELECT MAX(tb_stock_rates.rate_date) FROM tb_stock_rates WHERE tb_stock_rates.rate_date <= @rate_date AND tb_stock_rates.stock_id = @stock_id AND tb_stock_rates.rate_place_id = @place_id AND tb_stock_rates.stock_rate IS NOT NULL AND tb_stock_rates.stock_rate <> 0 AND .ITEMS_EXISTS(tb_stock_rates.id) ) IF @cur_rate_date IS NULL RETURN -- Котировка SELECT @rate_id = ( SELECT tb_stock_rates.id FROM tb_stock_rates WHERE tb_stock_rates.rate_date = @cur_rate_date AND tb_stock_rates.rate_place_id = @place_id AND tb_stock_rates.stock_id = @stock_id AND tb_stock_rates.stock_rate IS NOT NULL AND tb_stock_rates.stock_rate <> 0 AND .ITEMS_EXISTS(tb_stock_rates.id) ) -- Валюта котировки SELECT @rate_cur_id = tb_stock_rates.rate_cur_id FROM tb_stock_rates WHERE id = @rate_id IF @rate_cur_id IS NULL -- Валюта по умолчанию SELECT @rate_cur_id = td_rate_places.cur_id FROM td_rate_places WHERE id = @place_id SELECT @stock_rate_val = tb_stock_rates.market_price2 FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id -- Если нет Рыночная цена(2) берем Рыночная цена IF @stock_rate IS NULL SELECT @stock_rate_val = tb_stock_rates.stock_rate FROM tb_stock_rates WHERE tb_stock_rates.id = @rate_id IF @stock_rate IS NULL SELECT @stock_rate = 0 ELSE IF @cur_id = @rate_cur_id SELECT @stock_rate = @stock_rate_val ELSE BEGIN .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) END $ENDTEXT(13122915) $ENDACTION $ACTION(GET_STOCK_RATE_MP_COUPON) name=#Получить значение Рыночночной цены с НКД class=9 form=null target_state=null procedure=ap_get_stock_rate_mp_coupon patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @stock_id numeric, /*ЦБ*/ @rate_date datetime, /*Дата*/ @cur_id numeric, /*Валюта*/ @stock_rate numeric(18,8) out as begin declare @rate_id numeric declare @rate_cur_id numeric declare @rate_place_id numeric declare @cur_rate_value decimal(18,8) declare @cur_rate_date datetime declare @class_value_code varchar(255) declare @class_comment varchar(255) declare @stock_rate_val numeric(18,8) select @stock_rate = 0 if @stock_id is null return if @rate_date is null return if @cur_id is null select @cur_id = .CUR('RUR') -- Площадка select @rate_place_id = (select max(tb_stock_rates.rate_place_id) from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) if @rate_place_id is null return -- Котировка select @rate_id = (select tb_stock_rates.id from tb_stock_rates where tb_stock_rates.rate_date = @rate_date and tb_stock_rates.rate_place_id = @rate_place_id and tb_stock_rates.stock_id = @stock_id and tb_stock_rates.stock_rate is not null and tb_stock_rates.stock_rate <> 0 and .ITEMS_EXISTS(tb_stock_rates.id)) -- Валюта котировки select @rate_cur_id = tb_stock_rates.rate_cur_id from tb_stock_rates where id=@rate_id if @rate_cur_id is null -- Валюта по умолчанию select @rate_cur_id = td_rate_places.cur_id from td_rate_places where id=@rate_place_id -- Котировка declare @coupon_rate money select @stock_rate_val= tb_stock_rates.stock_rate, @coupon_rate = tb_stock_rates.coupon_rate from tb_stock_rates where id=@rate_id if @coupon_rate is not null select @stock_rate_val = isnull(@stock_rate_val, 0) + @coupon_rate if @stock_rate is null select @stock_rate = 0 else if @cur_id = @rate_cur_id select @stock_rate = @stock_rate_val else begin .CONVERT_SUM_DEC(@rate_cur_id, @cur_id, @rate_date, 0, @stock_rate_val, @stock_rate, @cur_rate_value, @cur_rate_date) end end $ENDTEXT(13122915) $ENDACTION $ACTION(MMVB_CORP_CSV_ACTION) name=Импорт ММВБ (csv) - корп.бумаги class=1 form=mmvb_csv_form target_state=CRT procedure=ap_21535000001006537 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @f1 varchar(255) = null, @f2 varchar(255) = null, -- boardid @f3 varchar(255) = null, @f4 varchar(255) = null, -- tradedate @f5 varchar(255) = null, @f6 varchar(255) = null, -- securityid @f7 varchar(255) = null, @f8 varchar(255) = null, @f9 varchar(255) = null, @f10 varchar(255) = null, -- volume @f11 varchar(255) = null, -- value @f12 varchar(255) = null, @f13 varchar(255) = null, @f14 varchar(255) = null, @f15 varchar(255) = null, @f16 varchar(255) = null, @f17 varchar(255) = null, @f18 varchar(255) = null, @f19 varchar(255) = null, @f20 varchar(255) = null, -- waprice @f21 varchar(255) = null, @f22 varchar(255) = null, @f23 varchar(255) = null, @f24 varchar(255) = null, @f25 varchar(255) = null, @f26 varchar(255) = null, @f27 varchar(255) = null, @f28 varchar(255) = null, @f29 varchar(255) = null, -- accint @f30 varchar(255) = null, @f31 varchar(255) = null, -- numtrades @f32 varchar(255) = null, @f33 varchar(255) = null, @f34 varchar(255) = null, @f35 varchar(255) = null, @f36 varchar(255) = null, @f37 varchar(255) = null, @f38 varchar(255) = null, @f39 varchar(255) = null, @f40 varchar(255) = null, @f41 varchar(255) = null, @f42 varchar(255) = null, @f43 varchar(255) = null, -- admquote @f44 varchar(255) = null, @f45 varchar(255) = null, @f46 varchar(255) = null, @f47 varchar(255) = null, @f48 varchar(255) = null, @f49 varchar(255) = null, @f50 varchar(255) = null as begin declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric select @action_id=%ACTION_ID% .CHECK_USER .CHECK_INSERT if isnull(@f6,'')='' or upper(@f6)='SECURITYID' return if @f2='PSAU' return declare @securityid varchar(30), @tradedate varchar(30), @waprice decimal(18,8), @accint decimal(18,8), @numtrades decimal(18,0), --@volume decimal(18,4), @value decimal(18,4), @admquote decimal(18,8), @rate_place_id_s varchar(30) .GET_PROPS if @f20='' or @f20='-' select @f20=null if @f29='' or @f29='-' select @f29=null if @f31='' or @f31='-' select @f31=null if @f10='' or @f10='-' select @f10=null if @f11='' or @f11='-' select @f11=null if @f43='' or @f43='-' select @f43=null select @securityid=rtrim(ltrim(@f6)), @tradedate=rtrim(ltrim(@f4)), @waprice=convert(decimal(18,8),@f20), @accint=convert(decimal(18,8),@f29), @numtrades=convert(decimal(18,0),@f31), @volume=convert(decimal(18,4),@f10), @value=convert(decimal(18,4),@f11), @admquote=convert(decimal(18,8),@f43) select @rate_place_id_s = (select convert(varchar(30),td_rate_places.id) from td_rate_places where upper(td_rate_places.place_code) = 'MICEX' and .ITEMS_EXISTS(td_rate_places.id)) -- из обычного импорта --declare @stock_id numeric declare @stock_nominal decimal(18,8) declare @stock_code varchar(30) --declare @rate_date datetime --declare @stock_rate decimal(18,8) --declare @coupon_rate decimal(18,8) --declare @rate_type int --declare @rate_place_id numeric --declare @rate_cur_id numeric declare @classvalue_id numeric declare @stock_type_id numeric declare @st_basetype_id numeric --declare @average decimal(18, 8) --declare @bid decimal(18, 8) --declare @ask decimal(18, 8) --declare @last_price decimal(18, 8) --declare @average_bid10 decimal(18, 8) --declare @average_ask10 decimal(18, 8) --declare @average_bid decimal(18, 8) --declare @average_bid_pd decimal(18, 8) --declare @trades_num money --declare @money_volume money --declare @volume_cur_id numeric --declare @acknowledged_price decimal(18, 8) -- ADD --declare @list_id numeric(18,0) -- Категория листинга --declare @max_price decimal(18, 8) --declare @min_price decimal(18, 8) create table #stockrate( stock_id numeric, stock_nominal decimal(18,8), stock_type_id numeric null, st_basetype_id numeric null ) select @rate_type=1 select @rate_cur_id=.CUR('RUR') select @volume_cur_id = @rate_cur_id select @trades_num = convert(money, isnull(@numtrades,0)) select @money_volume = @value select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки котировок') select @rate_date = convert(datetime, @tradedate, 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') select @coupon_rate = @accint if @coupon_rate is null select @coupon_rate = 0 if @waprice=0 select @waprice=null if @admquote=0 select @admquote=null select @stock_code = rtrim(ltrim(UPPER(@securityid))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') insert into #stockrate(stock_id,stock_nominal,stock_type_id,st_basetype_id) select td_stock_emis.id,isnull(td_stock_emis.stock_nominal,0),td_stock_emis.stock_type_id, (select stock_type_id from t_stock_types where id=td_stock_emis.stock_type_id) from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and t_item2class.code = @stock_code and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id, stock_nominal,stock_type_id,st_basetype_id from #stockrate open cstock begin tran fetch cstock into @stock_id,@stock_nominal,@stock_type_id,@st_basetype_id while .CURSOR_STATE=0 begin select @id = (select tb_stock_rates.id from tb_stock_rates where stock_id = @stock_id and rate_date = @rate_date and rate_place_id = @rate_place_id and .ITEMS_EXISTS_BY_TYPE(tb_stock_rates.id, 'tb_stock_rates')) if @stock_type_id=7 or @st_basetype_id=2 select @stock_rate = @waprice*@stock_nominal/100.0, @acknowledged_price=@admquote*@stock_nominal/100.0 else select @stock_rate = @waprice, @acknowledged_price=@admquote /*@calc_price_points, @calc_price_cur, @cur_calc_price, @step_price_points, @step_price_cur*/ if @id is null begin .INSERT_ITEMS .INSERT .UPDATE_STATE .UPDATE_ITEMS end else begin .UPDATE_ITEMS .UPDATE end fetch cstock into @stock_id,@stock_nominal,@stock_type_id,@st_basetype_id /* End LOOP */ end commit tran close cstock .DEALLOCATE cstock drop table #stockrate -- end из обычного импорта select sid = convert(varchar, @id) end $ENDTEXT(13122915) $ENDACTION $ACTION(MMVB_GOS_CSV_ACTION) name=Импорт ММВБ (csv) - гос.бумаги class=1 form=mmvb_csv_form target_state=CRT procedure=ap_21535000001006536 patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13122915) create procedure dbo.%PROC% @f1 varchar(255) = null, @f2 varchar(255) = null, -- boardid @f3 varchar(255) = null, @f4 varchar(255) = null, -- tradedate @f5 varchar(255) = null, @f6 varchar(255) = null, -- securityid @f7 varchar(255) = null, @f8 varchar(255) = null, @f9 varchar(255) = null, @f10 varchar(255) = null, -- volume @f11 varchar(255) = null, -- value @f12 varchar(255) = null, @f13 varchar(255) = null, @f14 varchar(255) = null, @f15 varchar(255) = null, @f16 varchar(255) = null, @f17 varchar(255) = null, @f18 varchar(255) = null, @f19 varchar(255) = null, @f20 varchar(255) = null, -- waprice @f21 varchar(255) = null, @f22 varchar(255) = null, @f23 varchar(255) = null, @f24 varchar(255) = null, @f25 varchar(255) = null, @f26 varchar(255) = null, @f27 varchar(255) = null, @f28 varchar(255) = null, @f29 varchar(255) = null, -- accint @f30 varchar(255) = null, @f31 varchar(255) = null, -- numtrades @f32 varchar(255) = null, @f33 varchar(255) = null, @f34 varchar(255) = null, @f35 varchar(255) = null, @f36 varchar(255) = null, @f37 varchar(255) = null, @f38 varchar(255) = null, -- admitquote @f39 varchar(255) = null, @f40 varchar(255) = null, @f41 varchar(255) = null, @f42 varchar(255) = null, @f43 varchar(255) = null, @f44 varchar(255) = null, @f45 varchar(255) = null, @f46 varchar(255) = null, @f47 varchar(255) = null, @f48 varchar(255) = null, @f49 varchar(255) = null, @f50 varchar(255) = null as begin declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric select @action_id=%ACTION_ID% .CHECK_USER .CHECK_INSERT if isnull(@f6,'')='' or upper(@f6)='SECURITYID' return if @f2='PSAU' return declare @securityid varchar(30), @tradedate varchar(30), @waprice decimal(18,8), @accint varchar(20), @numtrades decimal(18,0), -- @volume decimal(18,4), @value decimal(18,4), @admitquote decimal(18,8), @rate_place_id_s varchar(30) .get_props if @f20='' or @f20='-' select @f20=null if @f29='' or @f29='-' select @f29=null if @f31='' or @f31='-' select @f31=null if @f10='' or @f10='-' select @f10=null if @f11='' or @f11='-' select @f11=null if @f38='' or @f38='-' select @f38=null select @securityid=rtrim(ltrim(@f6)), @tradedate=rtrim(ltrim(@f4)), @waprice=convert(decimal(18,8),@f20), @accint=@f29, @numtrades=convert(decimal(18,0),@f31), @volume=convert(decimal(18,4),@f10), @value=convert(decimal(18,4),@f11), @admitquote=convert(decimal(18,8),@f38) select @rate_place_id_s = (select convert(varchar(30),td_rate_places.id) from td_rate_places where upper(td_rate_places.place_code) = 'MICEX' and .ITEMS_EXISTS(td_rate_places.id)) -- из обычного импорта --declare @stock_id numeric declare @stock_nominal decimal(18,8) declare @stock_code varchar(30) --declare @rate_date datetime --declare @stock_rate decimal(18,8) --declare @coupon_rate decimal(18,8) --declare @rate_type int --declare @rate_place_id numeric --declare @rate_cur_id numeric declare @classvalue_id numeric --declare @average decimal(18, 8) --declare @bid decimal(18, 8) --declare @ask decimal(18, 8) --declare @last_price decimal(18, 8) --declare @average_bid10 decimal(18, 8) --declare @average_ask10 decimal(18, 8) --declare @average_bid decimal(18, 8) --declare @average_bid_pd decimal(18, 8) --declare @trades_num money --declare @money_volume money --declare @volume_cur_id numeric --declare @acknowledged_price decimal(18, 8) -- -- ADD --declare @list_id numeric(18,0) -- Категория листинга --declare @max_price decimal(18, 8) --declare @min_price decimal(18, 8) create table #stockrate( stock_id numeric, stock_nominal decimal(18,8) ) select @rate_type=1 select @rate_cur_id=.CUR('RUR') select @volume_cur_id = @rate_cur_id select @trades_num = convert(money, isnull(@numtrades,0)) select @money_volume=@value select @rate_place_id = convert(numeric, rtrim(@rate_place_id_s)) if (@rate_place_id is null) .EXIT_MESSAGE('Не указана площадка для закачки курса') select @rate_date = convert(datetime, @tradedate, 3) if @rate_date is null .EXIT_MESSAGE('Не задана дата котировки') if rtrim(ltrim(@accint))<>'' select @coupon_rate = convert(decimal(18,8),rtrim(ltrim(@accint))) if @coupon_rate is null select @coupon_rate = 0 if @waprice=0 select @waprice=null if @admitquote=0 select @admitquote=null select @stock_code = rtrim(ltrim(UPPER(@securityid))) if @stock_code is null .EXIT_MESSAGE('Не задан код ЦБ') select @classvalue_id = (select classvalue_rate_id from td_rate_places where id = @rate_place_id) if (@classvalue_id is null) .EXIT_MESSAGE('У площадки не указано значение классификатора кодов ЦБ') insert into #stockrate(stock_id,stock_nominal) select td_stock_emis.id, td_stock_emis.stock_nominal from t_item2class, td_stock_emis where t_item2class.value_id = @classvalue_id and t_item2class.code is not null and t_item2class.code = @stock_code and t_item2class.item_id = td_stock_emis.id and .ITEMS_EXISTS_BY_TYPE(t_item2class.id, 'item2classvalues') and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id, 'td_stock_emis') declare cstock cursor for select stock_id, stock_nominal from #stockrate open cstock begin tran fetch cstock into @stock_id,@stock_nominal while .CURSOR_STATE=0 begin select @id = (select tb_stock_rates.id from tb_stock_rates where stock_id = @stock_id and rate_date = @rate_date and rate_place_id = @rate_place_id and .ITEMS_EXISTS_BY_TYPE(tb_stock_rates.id, 'tb_stock_rates')) select @stock_rate = @waprice*@stock_nominal/100.0, @acknowledged_price=@admitquote*@stock_nominal/100.0 if @id is null begin .INSERT_ITEMS .INSERT .UPDATE_STATE .UPDATE_ITEMS end else begin .UPDATE_ITEMS .UPDATE end fetch cstock into @stock_id,@stock_nominal /* End LOOP */ end commit tran close cstock .DEALLOCATE cstock drop table #stockrate -- end из обычного импорта select sid = convert(varchar, @id) end $ENDTEXT(13122915) $ENDACTION $STATE2ACTION(CRT.ACTION2) state=CRT action=ACTION2 $ENDSTATE2ACTION $STATE2ACTION(CRT.ACTION_DEL) state=CRT action=ACTION_DEL $ENDSTATE2ACTION $STATE2ACTION(CRT.D_STOCK_RATES_EDIT) state=CRT action=D_STOCK_RATES_EDIT $ENDSTATE2ACTION $STATE2ACTION(CRT.D_STOCK_RATES_LIST) state=CRT action=D_STOCK_RATES_LIST $ENDSTATE2ACTION $STATE2ACTION(CRT.GET_STOCK_PRICE_ONLINE) state=CRT action=GET_STOCK_PRICE_ONLINE $ENDSTATE2ACTION $STATE2ACTION(CRT.GET_STOCK_RATE_PROSP_REP) state=CRT action=GET_STOCK_RATE_PROSP_REP $ENDSTATE2ACTION $STATE2ACTION(CRT.STOCK_RATES_EXCEL) state=CRT action=STOCK_RATES_EXCEL $ENDSTATE2ACTION $STATE2ACTION(CRT.STOCK_RATE_COPY) state=CRT action=STOCK_RATE_COPY $ENDSTATE2ACTION $STATE2ACTION(NEW.ACTION1) state=NEW action=ACTION1 $ENDSTATE2ACTION $STATE2ACTION(NEW.GET_STOCK_PRICE_ONLINE) state=NEW action=GET_STOCK_PRICE_ONLINE $ENDSTATE2ACTION $STATE2ACTION(NEW.GET_STOCK_RATE_PROSP_REP) state=NEW action=GET_STOCK_RATE_PROSP_REP $ENDSTATE2ACTION $STATE2ACTION(NEW.STOCKS_SPFB_IMPORT_EXCEL) state=NEW action=STOCKS_SPFB_IMPORT_EXCEL $ENDSTATE2ACTION $STATE2ACTION(NEW.STOCK_RATES_EXCEL) state=NEW action=STOCK_RATES_EXCEL $ENDSTATE2ACTION $STATE2ACTION(NEW.STOCK_RATES_IMPORT) state=NEW action=STOCK_RATES_IMPORT $ENDSTATE2ACTION $STATE2ACTION(NEW.STOCK_RATES_IMPORT_NEW) state=NEW action=STOCK_RATES_IMPORT_NEW $ENDSTATE2ACTION $STATE2ACTION(NEW.STOCK_RATES_IMPORT_TLT) state=NEW action=STOCK_RATES_IMPORT_TLT $ENDSTATE2ACTION $STATE2ACTION(NEW.MMVB_CORP_CSV_ACTION) state=NEW action=MMVB_CORP_CSV_ACTION $ENDSTATE2ACTION $STATE2ACTION(NEW.MMVB_GOS_CSV_ACTION) state=NEW action=MMVB_GOS_CSV_ACTION $ENDSTATE2ACTION $STATE2PROP(CRT.ACKNOWLEDGED_PRICE) state=CRT prop=ACKNOWLEDGED_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.ASK) state=CRT prop=ASK enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.AVERAGE) state=CRT prop=AVERAGE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.AVERAGE_BID) state=CRT prop=AVERAGE_BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.AVERAGE_BID10) state=CRT prop=AVERAGE_BID10 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.AVERAGE_BID_PD) state=CRT prop=AVERAGE_BID_PD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.BID) state=CRT prop=BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.BOARD_CODE) state=CRT prop=BOARD_CODE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.BOARD_NAME) state=CRT prop=BOARD_NAME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CALC_PRICE_CUR) state=CRT prop=CALC_PRICE_CUR enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CALC_PRICE_POINTS) state=CRT prop=CALC_PRICE_POINTS enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CLOSE_PERIOD) state=CRT prop=CLOSE_PERIOD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CLOSE_PRICE) state=CRT prop=CLOSE_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CLOSE_VALUE) state=CRT prop=CLOSE_VALUE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.COUPON_RATE) state=CRT prop=COUPON_RATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.CUR_CALC_PRICE) state=CRT prop=CUR_CALC_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.HIGH_BID) state=CRT prop=HIGH_BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.ISSUE_SIZE) state=CRT prop=ISSUE_SIZE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.LAST_PRICE) state=CRT prop=LAST_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.LAST_PRICE_CHANGED_DATE) state=CRT prop=LAST_PRICE_CHANGED_DATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.LIST_ID) state=CRT prop=LIST_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.LOW_OFFER) state=CRT prop=LOW_OFFER enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.MARKET_PRICE2) state=CRT prop=MARKET_PRICE2 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.MAX_PRICE) state=CRT prop=MAX_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.MIN_PRICE) state=CRT prop=MIN_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.MONEY_VOLUME) state=CRT prop=MONEY_VOLUME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.M_PRICE_CUR_ID) state=CRT prop=M_PRICE_CUR_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.OPEN1) state=CRT prop=OPEN1 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.OPEN_PERIOD) state=CRT prop=OPEN_PERIOD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.OPEN_PRICE) state=CRT prop=OPEN_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.OPEN_VALUE) state=CRT prop=OPEN_VALUE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.RATE_CUR_ID) state=CRT prop=RATE_CUR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.RATE_DATE) state=CRT prop=RATE_DATE enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.RATE_PLACE_ID) state=CRT prop=RATE_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.RATE_TYPE) state=CRT prop=RATE_TYPE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.STEP_PRICE_CUR) state=CRT prop=STEP_PRICE_CUR enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.STEP_PRICE_POINTS) state=CRT prop=STEP_PRICE_POINTS enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.STOCK_ID) state=CRT prop=STOCK_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.STOCK_RATE) state=CRT prop=STOCK_RATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.TRADES_NUM) state=CRT prop=TRADES_NUM enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.VOLUME) state=CRT prop=VOLUME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.VOLUME_CUR_ID) state=CRT prop=VOLUME_CUR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.YIELD_CLOSE) state=CRT prop=YIELD_CLOSE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.YIELD_WAP) state=CRT prop=YIELD_WAP enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.ACKNOWLEDGED_PRICE) state=NEW prop=ACKNOWLEDGED_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.ASK) state=NEW prop=ASK enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.AVERAGE) state=NEW prop=AVERAGE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.AVERAGE_BID) state=NEW prop=AVERAGE_BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.AVERAGE_BID10) state=NEW prop=AVERAGE_BID10 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.AVERAGE_BID_PD) state=NEW prop=AVERAGE_BID_PD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.BID) state=NEW prop=BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.BOARD_CODE) state=NEW prop=BOARD_CODE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.BOARD_NAME) state=NEW prop=BOARD_NAME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CALC_PRICE_CUR) state=NEW prop=CALC_PRICE_CUR enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CALC_PRICE_POINTS) state=NEW prop=CALC_PRICE_POINTS enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CLOSE_PERIOD) state=NEW prop=CLOSE_PERIOD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CLOSE_PRICE) state=NEW prop=CLOSE_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CLOSE_VALUE) state=NEW prop=CLOSE_VALUE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.COUPON_RATE) state=NEW prop=COUPON_RATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.CUR_CALC_PRICE) state=NEW prop=CUR_CALC_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.HIGH_BID) state=NEW prop=HIGH_BID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.ISSUE_SIZE) state=NEW prop=ISSUE_SIZE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.LAST_PRICE) state=NEW prop=LAST_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.LAST_PRICE_CHANGED_DATE) state=NEW prop=LAST_PRICE_CHANGED_DATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.LIST_ID) state=NEW prop=LIST_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.LOW_OFFER) state=NEW prop=LOW_OFFER enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.MARKET_PRICE2) state=NEW prop=MARKET_PRICE2 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.MAX_PRICE) state=NEW prop=MAX_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.MIN_PRICE) state=NEW prop=MIN_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.MONEY_VOLUME) state=NEW prop=MONEY_VOLUME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.M_PRICE_CUR_ID) state=NEW prop=M_PRICE_CUR_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.OPEN1) state=NEW prop=OPEN1 enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.OPEN_PERIOD) state=NEW prop=OPEN_PERIOD enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.OPEN_PRICE) state=NEW prop=OPEN_PRICE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.OPEN_VALUE) state=NEW prop=OPEN_VALUE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.RATE_CUR_ID) state=NEW prop=RATE_CUR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.RATE_DATE) state=NEW prop=RATE_DATE enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.RATE_PLACE_ID) state=NEW prop=RATE_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.RATE_TYPE) state=NEW prop=RATE_TYPE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.STEP_PRICE_CUR) state=NEW prop=STEP_PRICE_CUR enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.STEP_PRICE_POINTS) state=NEW prop=STEP_PRICE_POINTS enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.STOCK_ID) state=NEW prop=STOCK_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.STOCK_RATE) state=NEW prop=STOCK_RATE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.TRADES_NUM) state=NEW prop=TRADES_NUM enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.VOLUME) state=NEW prop=VOLUME enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.VOLUME_CUR_ID) state=NEW prop=VOLUME_CUR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.YIELD_CLOSE) state=NEW prop=YIELD_CLOSE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.YIELD_WAP) state=NEW prop=YIELD_WAP enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $ENDDOC