Use MainDataBase

if not Exists(Select c.id from SysColumns c inner join SysObjects o on c.Id=o.Id
      where c.Name='ESCFPriznakBuh' and o.Name='tbGrOper') 

ALTER TABLE dbo.tbGrOper ADD
	ESCFPriznakBuh bit not null default(0) ,
	ESCFNDS bit NULL

Use Buh
if not Exists(Select c.id from SysColumns c inner join SysObjects o on c.Id=o.Id
      where c.Name='Seria' and o.Name='tbTovOt') 

ALTER TABLE dbo.tbTovOt ADD
	Seria varchar(10) NULL


if not Exists(Select c.id from SysColumns c inner join SysObjects o on c.Id=o.Id
      where c.Name='SumNDS' and o.Name='tbDocTovOt') 

ALTER TABLE dbo.tbDocTovOt ADD
	SumNDS decimal(18,2) NULL,
	SumNDSR decimal(18,2) NULL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mrfCalcSumNDS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[mrfCalcSumNDS]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION dbo.mrfCalcSumNDS (@IdDoc int,@IdStr int,@PrUch tinyint)  
--PrUch=0 ---опт, PrUch=1 - розница
RETURNS decimal(18,3)
 AS  
BEGIN 
Declare @SumNDS decimal(18,4)
Declare
  @NDS decimal(6,4),
  @IdPrizLog tinyint,
  @PrLogBKS tinyint,
  @IdPredprOb int


Declare @NameGrPredpr varchar(50),
                  @IdPredprPar int,
                 @PrCenPriobr bit,
                 @PrCenPriobrPar bit,
                 @IsCenPriobr bit,
                 @KodGr tinyint,
                 @IdTovOt int,  
                 @CenPriobr decimal(15,5),
                 @IdTipDoc tinyint



Declare @PrSkidOv bit,
                 @PrSkidOvPar bit

------------------
Select @IdTovOt=IdTovOt,@IdPredprPar=IdPredprPar,@IdTipDoc=IdTipDoc  from tbTovOt where IdDoc=@IdDoc

Select @IdPredprOb=IdPredprOb from tbTovOtSh where IdTovOt=@IdTovOt


Select @PrCenPriobr=TypeFieldN from MainDataBase..tbOsnNastr where IdPredpr=@IdPredprOb and NameFieldN='Pr_SchCenPriobr'
Select @PrCenPriobrPar=TypeFieldN from MainDataBase..tbOsnNastr where IdPredpr=@IdPredprPar and NameFieldN='Pr_SchCenPriobr'
set @PrCenPriobr= IsNull(@PrCenPriobr,0)
set @PrCenPriobrPar= IsNull(@PrCenPriobrPar,0)


Select @PrSkidOv=TypeFieldN from MainDataBase..tbOsnNastr where IdPredpr=@IdPredprOb and NameFieldN='Pr_SkidOv'
Select @PrSkidOvPar=TypeFieldN from MainDataBase..tbOsnNastr where IdPredpr=@IdPredprPar and NameFieldN='Pr_SkidOv'
set @PrSkidOv= IsNull(@PrSkidOv,0)
set @PrSkidOvPar= IsNull(@PrSkidOvPar,0)

Select @NameGrPredpr=NameGrPredpr from MainDataBase..tbGrPredpr tbGrPredpr 
        inner join MainDataBase..tbPredpr tbPredpr on tbGrPredpr.IdGrPredpr=tbPredpr.IdGrPredpr 
        where IdPredpr=@IdPredprPar 


Select 
            @KodGr=KodGr,@NDS=NDS,@CenPriobr=CenPriobr,
             @IdPrizLog=IdPrizLog,@PrLogBKS=PrLogBKS  from tbDocTovOt where IdStr=@IdStr 

if ((@PrCenPriobr=1 or @PrCenPriobrPar=1))  and IsNull(@CenPriobr,0)<>0
   set @IsCenPriobr=1
else
   set @IsCenPriobr=0

if @PrUch=1 -- считаем розничный  НДС
 begin
  if @IsCenPriobr=0  --от оптовой цены
    begin 
     if  @NameGrPredpr='Школа' 
        set @SumNDS=IsNull((Select (Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*NDS/100*KolF,0),2)) from tbDocTovOt
                                        where IdStr=@IdStr and (NDS-Floor( NDS)=0)),0)
    else
    begin  --не школа


    if @PrSkidOv=1 or @PrSkidOvPar=1    --отличие с овощами и без - в округлении, так почему-то сделано в товароведе
      begin
       if  (((@NDS-Floor( @NDS)=0) and @IdPrizLog=5) or (@IdPrizLog=41) or (@NDS in (9.09,16.67)))

          set @SumNDS=IsNull((Select (Round(Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0),2)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+
                                   IsNull((Select (Round(Round(ISNULL((dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0),2)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)

      if  (@NDS in (10,20) and @IdPrizLog=6)

         set @SumNDS=IsNull((Select (Round(Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0),2)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+

                                   IsNull((Select (Round(Round(ISNULL((dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0),2)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and 
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)
     end  --овощи
    else
   begin   --не овощи
     if  (((@NDS-Floor( @NDS)=0) and @IdPrizLog=5) or (@IdPrizLog=41)or (@NDS in (9.09,16.67)))

       set @SumNDS=IsNull((Select (Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+
                                   IsNull((Select (Round(ISNULL((dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)

     if  (@NDS in (10,20) and @IdPrizLog=6)
       set @SumNDS=IsNull((Select (Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+

                                   IsNull((Select (Round(ISNULL((dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and 
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)

    end --не овощи
  end  --не школа
 end -- от оптовой цены


 else  --от оптово-отпускной цены
 begin
     if  @NameGrPredpr='Школа' 
   set @SumNDS= IsNull((Select (Round(ISNULL((case when IsNull(CenPriobr,0)=0 then dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost) else CenPriobr end)*NDS/100*KolF,0),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and NDS-Floor( NDS)=0 and IdPrizLog=5),0)
  else

  begin
    if  (((@NDS-Floor( @NDS)=0) and @IdPrizLog=5) or (@IdPrizLog=41) or (@NDS in (9.09,16.67)))

      set @SumNDS=IsNull((Select (Round(Round(ISNULL(CenPriobr*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0),2)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+
                                   IsNull((Select (Round(Round(ISNULL((CenPriobr*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0),2)*NDS/100*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)

   if  (@NDS in (10,20) and @IdPrizLog=6)

      set @SumNDS=IsNull((Select (Round(Round(ISNULL(CenPriobr*(1+NalTr/100)*(1+NacRozn/100)*(1+NacObpit/100),0),2)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt 
                                        where IdStr=@IdStr and 
                                        (NacRozn>1 or (NacRozn<=1 and not Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)))),0)+

                                   IsNull((Select (Round(Round(ISNULL((CenPriobr*(1+NalTr/100)+NacRozn)*(1+NacObpit/100),0),2)*NDS/(100+NDS)*(KolF-IsNull(KolOv,0)),2)) from tbDocTovOt  
                                        where IdStr=@IdStr and 
                                        NacRozn<=1 and Exists(Select * from MainDataBase..tbZagotovki where KodGr=tbDocTovOt.KodGr and KodPgr=tbDocTovOt.KodPgr and KodVid=tbDocTovOt.KodVid)),0)
   end --не школа

 end -- от CenPriobr

end --розничный НДС


if @PrUch=0  -- считаем оптовый НДС
begin
if @IdTipDoc=10
begin
--от оптовой цены
if @IsCenPriobr=0
set @SumNDS=(Select ISNULL((Round(round(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1+NalTr/100)*(1+NacOpt/100)*(1-SkidPokup/100),2)*(Case
                                                        when ((nds in (10,20) and idPrizlog=6) ) then nds/(nds+100)
                                                        else 
                                                        NDS/100
                                                        end)*(KolF-IsNull(KolOv,0)),2)),0) from tbDocTovOt 
where IdStr=@IdStr 
and KodGr<>99 and IsNull(PrLogBKS,0)<>1 )
else
--от оптово-отпускной цены   ??надо с учетом транспортных ??
set @SumNDS=IsNull((Select (Round(Round((case when IsNull(CenPriobr,0)=0 then dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost) else CenPriobr end)*(1+NalTr/100)*(1+NacOpt/100)*(1-SkidPokup/100),2)*KolF*NDS/100,2)) from tbDocTovOt
                          where IdStr=@IdStr and (((NDS-Floor( NDS)=0) and IdPrizLog=5)or IdPrizLog in (41,6))  and KodGr<>99 ),0)


end

else   -- @IdTipDoc=9 
begin
if @PrLogBKS=0
 set @SumNDS=(Select Round(ISNULL(Round(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*(1-SkidPost/100),2),0)*(Case
                                                        when ((nds in (10,20)  and idPrizlog=6) ) then nds/(nds+100)
                                                        else 
                                                        NDS/100
                                                        end)*(KolF-IsNull(KolOv,0)),2) from tbDocTovOt
      where  IdStr=@IdStr )
else
 set @SumNDS=(Select Round((case when IsNull(CenPriobr,0)=0 then dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost) else CenPriobr end)*NDS/100*KolF,2)
    from tbDocTovOt
      where  IdStr=@IdStr ) 
end

end


--НДС тары
if @kodgr=99 and @PrUch=0
begin
 if @IsCenPriobr=0  --от оптовой цены

 set @SumNDS=(Select Round(ISNULL(dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost)*KolF*NDS/100,0),2) from tbDocTovOt 
                          where   IdStr=@IdStr and IdPrizLog=5 )
 else

 set @SumNDS=(Select Round(ISNULL((case when IsNull(CenPriobr,0)=0 then dbo.mrfCalcCenPost(CenO,NacPosr,SkidPost,NalTrPost) else CenPriobr end)*KolF*NDS/100,0),2) from tbDocTovOt 
                          where   IdStr=@IdStr and IdPrizLog=5 )
end


Return IsNull(@SumNDS,0)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
