create procedure dbo.%PROC% @calc_date datetime,
                            @tax_type_code varchar(255),
                            @profit_loss_for_tax money,
                            @client_id numeric,
                            @city_id numeric,
                            @tax_value money out,
                            @tax_percent money out

as

declare
@tax_category_id numeric,
@nalog_id numeric,
@fin_const_id numeric,
@discount_fin_const_id numeric,
@discount_fin_const_values money,
@fin_const_values money,
@sum1 money,
@tax_value_minus money,
@tax_value_id numeric,
@percent money,
@max_date_fin datetime,
@max_date_discount_fin datetime,
@max_tax_date datetime,
@min_tax_null_level money,
@is_federal int,
@is_double_tax int



if convert(datetime, @calc_date ) is null
begin
  if @@trancount<>0 rollback tran
  raiserror 40000 'Не задана дата расчета'
  return
end

select @tax_category_id=tax_category_id
    from t_partners
      where id=@client_id

select @is_double_tax = t_countries.double_tax
  from t_countries,
       t_cities
 where t_countries.id = t_cities.country_id
   and t_cities.id = @city_id


select @nalog_id=t_taxes.id,
       @fin_const_id=t_taxes.fin_const_id,
       @discount_fin_const_id=t_taxes.discount_fin_const_id,
       @is_federal=t_taxes.is_federal
   from t_taxes,t_items,t_states
     where t_taxes.tax_code=@tax_type_code
       and t_items.id=t_taxes.id
       and t_states.id=t_items.state_id
       and t_states.class_id=0

select @max_date_fin=(select max(t_fin_const_values.from_date)
      from t_fin_const_values, t_items,t_states
where t_fin_const_values.from_date <= @calc_date
      and t_fin_const_values.fin_const_id=@fin_const_id and t_fin_const_values.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0)

select @fin_const_values=t_fin_const_values.const_value
   from t_fin_const_values
     where t_fin_const_values.fin_const_id=@fin_const_id
       and t_fin_const_values.from_date=@max_date_fin


select @max_date_discount_fin=(select max(t_fin_const_values.from_date) from t_fin_const_values, t_items,t_states
where t_fin_const_values.from_date <= @calc_date
      and t_fin_const_values.fin_const_id=@discount_fin_const_id and t_fin_const_values.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0)

select @discount_fin_const_values=t_fin_const_values.const_value
   from t_fin_const_values
     where t_fin_const_values.fin_const_id=@discount_fin_const_id
       and t_fin_const_values.from_date=@max_date_discount_fin

if @is_federal=1 begin

   select @max_tax_date=(select max(t_tax_val_dates.tax_value_date) from t_tax_val_dates, t_items,t_states
   where t_tax_val_dates.tax_value_date <= @calc_date
    and t_tax_val_dates.tax_id=@nalog_id and t_tax_val_dates.tax_categor_id=@tax_category_id and t_tax_val_dates.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0)

   select @tax_value_minus=t_tax_val_dates.tax_value_minus,
       @tax_value_id=t_tax_val_dates.id
   from t_tax_val_dates
    where t_tax_val_dates.tax_id=@nalog_id
      and t_tax_val_dates.tax_categor_id=@tax_category_id
      and t_tax_val_dates.tax_value_date=@max_tax_date

end
else begin

   if convert(numeric, @city_id ) is null begin
      select @city_id=t_cities.id from t_cities where city_code='101'
   end

   select @max_tax_date=(select max(t_tax_val_dates.tax_value_date) from t_tax_val_dates, t_items,t_states
    where t_tax_val_dates.tax_value_date <= @calc_date
     and t_tax_val_dates.tax_id=@nalog_id and t_tax_val_dates.tax_categor_id=@tax_category_id
     and t_tax_val_dates.city_id=@city_id and t_tax_val_dates.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0)

   if @max_tax_date is null
      begin
  if @@trancount<>0 rollback tran
  raiserror 40000 'Ставки налога не определены'
  return
end

   select @tax_value_minus=t_tax_val_dates.tax_value_minus,
       @tax_value_id=t_tax_val_dates.id
    from t_tax_val_dates
     where t_tax_val_dates.tax_id=@nalog_id
       and t_tax_val_dates.tax_categor_id=@tax_category_id
       and t_tax_val_dates.tax_value_date=@max_tax_date
       and t_tax_val_dates.city_id= @city_id

   if @tax_value_minus is null begin
    select @tax_value_minus=0
   end

end

select @sum1=@profit_loss_for_tax -(@tax_value_minus*@discount_fin_const_values)

if @sum1>0 begin
   select @min_tax_null_level=(select max(t_tax_values.tax_null_level*@fin_const_values)
   from t_tax_values, t_items,t_states
    where (t_tax_values.tax_null_level*@fin_const_values)<=@sum1
     and t_tax_values.id = t_items.id
and t_items.state_id = t_states.id
and t_states.class_id = 0 and t_tax_values.tax_val_date_id=@tax_value_id)

   if @min_tax_null_level is null
      begin
  if @@trancount<>0 rollback tran
  raiserror 40000 'Порог для данной суммы не определен'
  return
end

   select @percent=t_tax_values.tax_percent,
          @tax_value=t_tax_values.tax_value
      from t_tax_values
       where t_tax_values.tax_val_date_id=@tax_value_id
        and t_tax_values.tax_null_level*@fin_const_values=@min_tax_null_level

   if (@percent is null) select @percent=0

   if (@tax_value is null) select @tax_value=0

   select @tax_value=(@tax_value*@fin_const_values)+(((@sum1-@min_tax_null_level*@fin_const_values)*@percent)/100)
   select @tax_percent=@percent

   if (@tax_value is null or @tax_value = 0)
    begin
     select @tax_value = @profit_loss_for_tax * (@percent/100)
    end
end

if @is_double_tax = 1
   begin
     select @tax_value = 0,
            @tax_percent = 0
   end

select @tax_value = isnull(@tax_value,0),
       @tax_percent = isnull(@tax_percent,0)