TRIGGER CDN.TwrCeny_SEDKOMP_UaktualnijCeneDetallubMarze

USE [CDNXL_szkola]
GO
/****** Object:  Trigger [CDN].[TwrCeny_SEDKOMP_UaktualnijCeneDetallubMarze]    Script Date: 07/23/2012 13:17:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Piotr Osłowski, SEDKOMP
-- Create date: 2012-06-29
-- Description: dla zmienionej ceny DETALICZNEJ zmienia marżę
--              dla zmienionej ceny FABRYCZNEJ oblicza cenę DETALICZNĄ
-- =============================================
ALTER TRIGGER [CDN].[TwrCeny_SEDKOMP_UaktualnijCeneDetal]
--ALTER TRIGGER [CDN].[TwrCeny_SEDKOMP_UaktualnijCeneDetallubMarze]
   ON  [CDN].[TwrCeny]
   AFTER UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @lp int
    Declare @typ int
    Declare @numer int
    Declare @wartosc decimal(15,4)
    DECLARE @wartosc2 decimal(15,4)
    DECLARE @narzut decimal(15,4)
    declare @OpeCeny int --id operatora automatu aktualizujacego cene
    declare @cenaold decimal(12,2)
    set @OpeCeny=536
    SELECT @lp = TwC_TwrLp, @typ = TwC_TwrTyp, @numer = TwC_TwrNumer FROM Inserted

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


        --bierzemy marżę z dodatkowej tablicy
        SELECT @narzut = TwCSED_Narzut FROM CDN.SED_TwrCeny WHERE TwCSED_TwrLp = 3 AND TwCSED_TwrNumer = @numer
        SET @narzut = IsNull(@narzut,0)


        set @cenaold= isnull((select twc_wartosc from cdn.twrceny where twc_twrnumer=@numer and twc_twrlp=3),0)
        --nabicie cen hist
        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,@cenaold,128,twc_twrfirma,@OpeCeny,1,datediff(s,'1990-01-01',getdate()),1,0,0,'Zmiana ceny fabrycznej'
        from cdn.twrceny
        where twc_twrnumer=@numer and twc_twrlp=3



        --uaktualnianie ceny detal na podstawie ceny fabrycznej (lp=1)
        SELECT @wartosc = TwC_Wartosc FROM CDN.TwrCeny WHERE TwC_TwrNumer = @numer AND TwC_TwrLp = 1
        SET @wartosc = IsNull(@wartosc,0)
        set @narzut = @narzut/100
        SET @wartosc = @wartosc + (@wartosc * @narzut)
        UPDATE CDN.TwrCeny SET TwC_Wartosc = @wartosc WHERE TwC_TwrNumer = @numer AND TwC_TwrLp = 3
    End



    if @lp = 3
    Begin

        --cena FABRYCZNA
        SELECT @wartosc = TwC_Wartosc FROM CDN.TwrCeny WHERE TwC_TwrNumer = @numer AND TwC_TwrLp = 1
        SET @wartosc = IsNull(@wartosc,0)
        --cena DETALICZNA

        select @wartosc2 = TwC_Wartosc FROM Inserted
        SET @wartosc2 = IsNull(@wartosc2, 0)

        SET @narzut = 0



        if @wartosc2 <> 0
        BEGIN

            SET @narzut = (@wartosc2 - @wartosc) / (@wartosc2 * 0.01)


        END
        --uaktualnianie ceny detal na podstawie ceny fabrycznej (lp=1)


        --uaktualnienie marzy

        UPDATE cdn.sed_TwrCeny SET TwCSED_Narzut = @narzut WHERE
        TwCSED_TwrTyp = @typ AND  TwCSED_TwrNumer = @numer AND TwCSED_TwrLp = @lp

        declare @ile int

        Select @ile = @@ROWCOUNT

        if @ile = 0
                Begin

                INSERT INTO [CDN].[SED_TwrCeny]
                                       ([TwCSED_TwrTyp]
                                       ,[TwCSED_TwrNumer]
                                       ,[TwCSED_TwrLp]
                                       ,[TwCSED_Narzut])
                                 VALUES
           (@typ
           ,@numer
           ,@lp
           ,@narzut)


                End

    End

END