TRIGGER CDN.TwrCenyHist_SEDKOMP_UaktualnijCeny

/****** Object:  Trigger [CDN].[TwrCenyHist_SEDKOMP_UaktualnijCeny]    Script Date: 07/02/2012 09:30:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Piotr Osłowski, SEDKOMP
-- Create date: 2011-03-31
-- Description: Tworzy bliźniaczy zapis w tablicy CDN.SED_TwrCeny dla każdej ceny detalicznej
-- =============================================
ALTER TRIGGER [CDN].[TwrCenyHist_SEDKOMP_UaktualnijCeny]
   ON  [CDN].[TwrCenyHist]
   AFTER INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    --Nabycia_procent
    DECLARE @atkID int
    SET @atkID = 71

    DECLARE @atkMetodaNabycia int
    SET @atkMetodaNabycia = 70

    --cena nabycia
    DECLARE @lpCenaNabycia int
    SET @lpCenaNabycia = 4


    Declare @lp int
    Declare @typ int
    Declare @numer int
    Declare @wartosc decimal(15,4)
    DECLARE @narzut decimal(15,4)

    declare @nabycia_procent decimal(15,2)

    DECLARE @cenaFabryczna decimal(15,2)
    declare @cenaNabyciaOld decimal(15,2)
    declare @cenaNabyciaNew decimal(15,2)

    DECLARE @cenaDetalOld decimal(15,2)
    DECLARE @cenaDetalNew decimal(15,2)

    declare @opeCeny int --id operatora automatu aktualizujacego cene

    declare @metodaNabycia varchar(25)


    SELECT @lp = TCH_TwrLp, @typ = TCH_TwrTyp, @numer = TCH_TwrNumer FROM Inserted

    --tylko dla cen fabrycznych
    if @lp = 1
    Begin

        SELECT @narzut = TwCSED_Narzut FROM CDN.SED_TwrCeny WHERE TwCSED_TwrLp = 3 AND TwCSED_TwrNumer = @numer
        SET @narzut = IsNull(@narzut,0)


        SELECT  @nabycia_procent =  Cast (  Case Atr_Wartosc WHEN '' THEN '0' ELSE Atr_Wartosc END as  decimal(15,2))
        from cdn.Atrybuty
        WHERE Atr_AtkId = @atkID
        AND Atr_ObiTyp = @typ AND Atr_ObiNumer = @numer
        SET @nabycia_procent = IsNull(@nabycia_procent,0)


        SELECT  @metodaNabycia =  Atr_Wartosc
        from cdn.Atrybuty
        WHERE Atr_AtkId = @atkMetodaNabycia
        AND Atr_ObiTyp = @typ AND Atr_ObiNumer = @numer
        SET @metodaNabycia = IsNull(@metodaNabycia,'')



        --odczytujemy kto zmienił cenę fabryczną...
        SELECT TOP 1 @opeCeny = TCH_OpeNumer FROM CDN.TwrCenyHist
        WHERE TCH_TwrTyp = @typ AND TCH_TwrNumer = @numer AND TCH_TwrLp = @lp
        ORDER BY TCH_TSArc DESC





        ------------------------------------------------------------------------------------------
        --uaktualnianie ceny detal na podstawie ceny fabrycznej (lp=1)
        SELECT @cenaFabryczna = TwC_Wartosc FROM CDN.TwrCeny WHERE TwC_TwrNumer = @numer AND TwC_TwrLp = 1
        SET @cenaFabryczna = IsNull(@cenaFabryczna,0)

        SELECT @cenaDetalOld = TwC_Wartosc FROM CDN.TwrCeny
        where twc_twrnumer=@numer and twc_twrlp = 3

        SET @cenaDetalNew = Round(@cenaFabryczna * (1 + @narzut * 0.01),2)

        SET @cenaDetalOld = IsNull(@cenaDetalOld,0)
        SET @cenaDetalNew = IsNull(@cenaDetalNew,0)

        if (@cenaDetalOld <> @cenaDetalNew)
            Begin
                --UPDATE CDN.TwrCeny SET TwC_Wartosc = @cenaDetalNew, twc_czasmodyfikacji=datediff(s,'1990-01-01',getdate()) WHERE TwC_TwrNumer = @numer AND TwC_TwrLp = 3

                insert into cdn.twrcenyhist
                        (TCH_TwrTyp, TCH_TwrFirma, TCH_TwrNumer, TCH_TwrLp, TCH_Waluta, TCH_NrKursu,
                        TCH_Wartosc, TCH_OpeTyp, TCH_OpeFirma, TCH_OpeNumer, TCH_OpeLp, TCH_TSArc, TCH_Domyslna, TCH_DokNumer, TCH_DokTyp, TCH_Zrodlo)
                          select twc_twrtyp,twc_twrfirma,twc_twrnumer,twc_twrlp,twc_waluta,twc_nrkursu,@cenaDetalNew,128,twc_twrfirma,@OpeCeny,1,datediff(s,'1990-01-01',getdate()),1,0,0,'trigger: TwrCeny_SEDKOMP_UaktualnijCeneDetal'
                      from cdn.twrceny
                      where twc_twrnumer=@numer and twc_twrlp=3
            End



        -------------------------------------------------------------------------------------------
        -- cena nabycia
        SELECT @cenaNabyciaOld = TwC_Wartosc FROM CDN.TwrCeny
        where twc_twrnumer=@numer and twc_twrlp=@lpCenaNabycia

        SET @cenaNabyciaNew = round(@cenaFabryczna*(1+@nabycia_procent/100),2)

        SET @cenaNabyciaOld = IsNull(@cenaNabyciaOld,0)
        SET @cenaNabyciaNew = IsNull(@cenaNabyciaNew,0)

        if ((@cenaNabyciaOld <> @cenaNabyciaNew) AND ( LTrim(Rtrim( @metodaNabycia )) = 'fabryczna'))
            Begin

                    update cdn.twrceny
                    set  twc_wartosc=@cenaNabyciaNew,
                    twc_czasmodyfikacji=datediff(s,'1990-01-01',getdate())
                    from cdn.twrceny
                    where twc_twrnumer=@numer and twc_twrlp=@lpCenaNabycia

                        insert into cdn.twrcenyhist
                        (TCH_TwrTyp, TCH_TwrFirma, TCH_TwrNumer, TCH_TwrLp, TCH_Waluta, TCH_NrKursu,
                        TCH_Wartosc, TCH_OpeTyp, TCH_OpeFirma, TCH_OpeNumer, TCH_OpeLp, TCH_TSArc, TCH_Domyslna, TCH_DokNumer, TCH_DokTyp, TCH_Zrodlo)
                          select twc_twrtyp,twc_twrfirma,twc_twrnumer,twc_twrlp,twc_waluta,twc_nrkursu,@cenaNabyciaNew,128,twc_twrfirma,@OpeCeny,1,datediff(s,'1990-01-01',getdate()),1,0,0,'trigger: TwrCeny_SEDKOMP_UaktualnijCeneDetal'
                      from cdn.twrceny
                      where twc_twrnumer=@numer and twc_twrlp=@lpCenaNabycia

            End

    END
END