create procedure dbo.%PROC%
 @stock_id numeric,
 @fund_id numeric,
 @place_id numeric,
 @check_date datetime,
 @check_type int,
 @stock_qty money,

 @rate_date datetime out,
 @is_rate int out,
 @stock_rate decimal(18,8) out,
 @coupon_rate decimal(18,8) out,
 @stock_sum money out,
 @coupon_sum money out,
 @out_cur_id numeric out

as

set datefirst 1

declare
 @class_value_code varchar(255),
 @class_comment varchar(255),
 @stock_rate_cur decimal(18,8),
 @acknowledged_price decimal(18,8),
 @coupon_rate_cur decimal(18,8),
 @stock_rate_rur decimal(36,12),
 @coupon_rate_rur decimal(36,12),
 @rur_cur_id numeric,
 @rate_cur_id numeric,
 @place_cur_id numeric,
 @convert_value decimal(18,8),
 @convert_date datetime,
 @class_round int

declare
 @d int,
 @trades_qty money,
 @trades_qty_all money,
 @rates_qty int,
 @stock_rate_rur_all decimal(36,12),
 @find_date datetime,
 @money_volume decimal(36,12),
 @volume decimal(36,12),
 @volume_cur_id numeric,
 @stock_rate_cur_avg decimal(36,12)

declare
 @stock_place_id numeric,
 @place_code varchar(50),
 @mmvb_code varchar(50),
 @not_mmvb_code varchar(50),
 @rts_date datetime,
 @mmvb_date datetime,
 @place_date datetime,
 @places tinyint,
 @max_places tinyint

select @is_rate=0, @coupon_sum=0

-- площадка
if @place_id is null
begin
 -- площадка из классификатора для ЦБ
 exec ap_getclassvalue_code @stock_id, 'STOCK_RATE_PLACE', null, @fund_id, @class_value_code out, @class_comment out

 if @class_value_code is not null
 begin
  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 isnull(@class_comment,'')<>'' select @class_round=convert(int,@class_comment)
 end
end
else
 select @class_value_code=place_code from td_rate_places where id=@place_id

if @place_id is null return

select
 @rur_cur_id=.CUR('RUR'),
 @stock_place_id=@place_id,
 @mmvb_code='MICEX',
 @not_mmvb_code='RTS', -- код площадки, с которой берем, если не нашли на ММВБ (сейчас подразумевается РТС)
 @place_cur_id=(select td_rate_places.cur_id from td_rate_places where id=@place_id),
 @place_code=(select td_rate_places.place_code from td_rate_places where id=@place_id),
 -- даты, до которых признаваемая котировка считалась в Аладдине
 @rts_date=convert(datetime,'27/01/04',3),
 @mmvb_date=convert(datetime,'11/05/04',3),
 @places=0, @max_places=1

if .GET_CLASS_VALUE_CODE_FOR_ITEM(@fund_id,'PLACES_IN_RATE_CHECK','ONE')='TWO'
 select @max_places=2

-- КОТИРОВКА

while @places < @max_places -- по площадкам
begin

 select @stock_rate_cur=null, @is_rate=null, @rate_date=null, @rate_cur_id=null,
        @trades_qty=null, @acknowledged_price=null
 select @place_date=(case @place_code when @mmvb_code then @mmvb_date else @rts_date end)

 if @check_type=0 -- ближайшая котировка
  select @stock_rate_cur=tb_stock_rates.stock_rate,
         @is_rate=tb_stock_rates.rate_type,
         @rate_date=tb_stock_rates.rate_date,
         @rate_cur_id=tb_stock_rates.rate_cur_id
    from tb_stock_rates
   where tb_stock_rates.rate_place_id = @place_id
     and tb_stock_rates.stock_id = @stock_id
     and tb_stock_rates.rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates
                                      where tb_stock_rates.rate_place_id = @place_id
                                        and tb_stock_rates.rate_date <= @check_date
                                        and tb_stock_rates.stock_rate<>0
                                        and tb_stock_rates.stock_id = @stock_id
                                        and .ITEMS_EXISTS(tb_stock_rates.id))
     and .ITEMS_EXISTS(tb_stock_rates.id)

 if @check_type=1 -- котировка на дату оценки
  select @stock_rate_cur=/*null,--*/tb_stock_rates.stock_rate,
         @acknowledged_price = tb_stock_rates.acknowledged_price,
         @is_rate=tb_stock_rates.rate_type,
         @rate_date=tb_stock_rates.rate_date,
         @rate_cur_id=tb_stock_rates.rate_cur_id,
         @trades_qty=tb_stock_rates.trades_num
    from tb_stock_rates
   where tb_stock_rates.rate_place_id = @place_id
     and tb_stock_rates.stock_id = @stock_id
     and tb_stock_rates.rate_date = @check_date
     and .ITEMS_EXISTS(tb_stock_rates.id)

 -- (1) 42ps (сделок >= 10 или есть признаваемая котировка) или старый вариант
--- if ((isnull(@trades_qty,0)>=10 and isnull(@acknowledged_price,0)<>0) and @check_type=1) --or @check_type=0
--- begin
  --- select @stock_rate_cur=isnull(@acknowledged_price,0)
--- end

  if isnull(@acknowledged_price,0)<>0    select @stock_rate_cur=isnull(@acknowledged_price,0)

 if isnull(@stock_rate_cur,0)<>0
 begin

   if @class_round is not null select @stock_rate_cur = round(@stock_rate_cur,@class_round)
   select @rate_cur_id=isnull(@rate_cur_id,@place_cur_id)
   select @rate_cur_id=isnull(@rate_cur_id,@rur_cur_id)
   if @rur_cur_id=@rate_cur_id
    select @stock_rate_rur=@stock_rate_cur
   else begin
    .CONVERT_SUM_DEC(@rate_cur_id, @rur_cur_id, @check_date, 0, @stock_rate_cur, @stock_rate_rur, @convert_value, @convert_date)
   end
   if .GET_CLASS_VALUE_CODE_FOR_ITEM(@fund_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'
   begin
    .PIF_ROUND_RATE(@fund_id, @stock_rate_rur, @stock_rate_rur)
   end
   select @stock_sum=convert(money,round((convert(decimal(36,12),@stock_qty) * @stock_rate_rur),2))
   .PIF_ROUND_RATE(@fund_id, @stock_rate_rur, @stock_rate_rur)
   if isnull(@is_rate,0) not in (0,1) select @is_rate=1
   select @stock_rate=convert(decimal(18,8),@stock_rate_rur), @out_cur_id=@rate_cur_id
  end
  else select @stock_rate=null

-- end -- (1)

 -- (2) 42ps (сделок < 10) - расчет по оборотам за 10 дней, если котировка не найдена
 -- (включая день оценки, в RUR переводится результат деления оборотов)

/*

 select @find_date=dateadd(dd,1,@check_date)
 if isnull(@trades_qty,0)<10 and @check_type=1 and @stock_rate is null
    and ((@check_date < @place_date -- дата меньше чем начало расчета признаваемой котировки на площадке
          and @place_date is not null) or @place_date is null)
 begin

  select @d=0, @trades_qty_all=0, @money_volume=0, @volume=0

  while @d<10
  begin -- дни

   select @find_date=dateadd(dd,-1,@find_date)

   if not exists(select 1 from tb_stock_rates
                  where tb_stock_rates.rate_place_id = @place_id
                    and tb_stock_rates.stock_id = @stock_id
                    and tb_stock_rates.rate_date <= @find_date
                    and .ITEMS_EXISTS(tb_stock_rates.id))
    break

   -- рабочие дни
   if (datepart(dw,@find_date) not in (6,7))
      and not exists(select 1 from tp_pif_calendar where dmy=@find_date and isnull(include,0)<>1)
      -- на ММВБ 31.12.03 - выходной
      and ((@find_date<>convert(datetime,'31/12/03',3) and (upper(@class_value_code) like '%'+@mmvb_code+'%'))
           or not(upper(@class_value_code) like '%'+@mmvb_code+'%'))
      -- BACK: and not exists(select 1 from t_dcalendar where cal_date=@find_date and isnull(dtype,0)<>1)
   begin
    select @d=@d+1
    select @money_volume=@money_volume+isnull(tb_stock_rates.money_volume,0),
           @volume=@volume+isnull(tb_stock_rates.volume,0),
           @volume_cur_id=(case when @volume_cur_id is null then tb_stock_rates.volume_cur_id
                                else @volume_cur_id
                           end),
           @trades_qty_all=@trades_qty_all+isnull(tb_stock_rates.trades_num,0)
      from tb_stock_rates
     where tb_stock_rates.rate_place_id = @place_id
       and tb_stock_rates.stock_id = @stock_id
       and tb_stock_rates.rate_date = @find_date
       and .ITEMS_EXISTS(tb_stock_rates.id)
   end
  end -- end по дням

  select @volume_cur_id=isnull(@volume_cur_id,@place_cur_id)
  select @volume_cur_id=isnull(@volume_cur_id,@rur_cur_id)

  -- суммарное кол-во сделок > 10
  if @trades_qty_all>=10
  begin

   -- средняя за 10 дней
   if @volume=0
    select @stock_rate_cur_avg=0
   else
    select @stock_rate_cur_avg=convert(decimal(36,12),@money_volume/@volume)

   -- округление расчета по правилам площадок
   if upper(@class_value_code) like '%'+@not_mmvb_code+'%'
    select @stock_rate_cur_avg=round(@stock_rate_cur_avg,5)
   if upper(@class_value_code) like '%'+@mmvb_code+'%'
    select @stock_rate_cur_avg=round(@stock_rate_cur_avg,4)

   if @class_round is not null select @stock_rate_cur_avg = round(@stock_rate_cur_avg,@class_round)

   -- рублевая за 10 дней
   if @rur_cur_id=@volume_cur_id
    select @stock_rate_rur=@stock_rate_cur_avg
   else begin
    -- по курсу на дату оценки
    .CONVERT_SUM_DEC(@volume_cur_id, @rur_cur_id, @check_date, 0, @stock_rate_cur_avg, @stock_rate_rur, @convert_value, @convert_date)
   end

   if .GET_CLASS_VALUE_CODE_FOR_ITEM(@fund_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'
   begin
    .PIF_ROUND_RATE(@fund_id, @stock_rate_rur, @stock_rate_rur)
   end
   select @stock_sum=convert(money,round((convert(decimal(36,12),@stock_qty) * @stock_rate_rur),2))
   .PIF_ROUND_RATE(@fund_id, @stock_rate_rur, @stock_rate_rur)
   select @stock_rate=convert(decimal(18,8),@stock_rate_rur), @rate_date=@check_date, @is_rate=1,
          @out_cur_id=@volume_cur_id
  end
  else select @stock_rate=null

 end -- (2)

*/

 -- след.площадка
 if @stock_rate is null
 begin
  select @place_id = (select max(td_rate_places.id) from td_rate_places
   where upper(td_rate_places.place_code) = (case @place_code when @mmvb_code then @not_mmvb_code else @mmvb_code end)
     and .ITEMS_EXISTS(td_rate_places.id))
  select @place_cur_id=(select td_rate_places.cur_id from td_rate_places where id=@stock_place_id)
  if @place_id is null select @places=@max_places else select @places=@places+1
 end else
  select @places=@max_places, @stock_place_id=@place_id

end -- end по площадкам


-- НКД

select @rate_cur_id=null
select @coupon_rate_cur=tb_stock_rates.coupon_rate,
       @rate_cur_id=tb_stock_rates.rate_cur_id
  from tb_stock_rates
 where tb_stock_rates.rate_place_id = @stock_place_id -- id 1-й площадки или той, на которой нашли котировку
   and tb_stock_rates.stock_id = @stock_id
   and tb_stock_rates.rate_date = @check_date
   and .ITEMS_EXISTS(tb_stock_rates.id)

if @coupon_rate_cur is not null
begin
 select @place_cur_id=(select td_rate_places.cur_id from td_rate_places where id=@stock_place_id)
 select @rate_cur_id=isnull(@rate_cur_id,@place_cur_id)
 select @rate_cur_id=isnull(@rate_cur_id,@rur_cur_id)
 if @rur_cur_id=@rate_cur_id
  select @coupon_rate_rur=@coupon_rate_cur
 else begin
  .CONVERT_SUM_DEC(@rate_cur_id, @rur_cur_id, @check_date, 0, @coupon_rate_cur, @coupon_rate_rur, @convert_value, @convert_date)
 end
 if .GET_CLASS_VALUE_CODE_FOR_ITEM(@fund_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'
 begin
  .PIF_ROUND_RATE(@fund_id, @coupon_rate_rur, @coupon_rate_rur)
 end
 select @coupon_sum=convert(money,round((convert(decimal(36,12),@stock_qty) * @coupon_rate_rur),2))
 .PIF_ROUND_RATE(@fund_id, @coupon_rate_rur, @coupon_rate_rur)
 select @coupon_rate=convert(decimal(18,8),@coupon_rate_rur)
 if @out_cur_id is null select @out_cur_id=@rate_cur_id
end
select @coupon_sum=isnull(@coupon_sum,0)

if @stock_rate is null select @stock_sum=null, @is_rate=0, @rate_date=null, @out_cur_id=null