[CDN].[SED_BilansKrok3]

/****** Object:  StoredProcedure [CDN].[SED_BilansKrok3]    Script Date: 09/07/2012 12:12:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: 2012-01-10
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [CDN].[SED_BilansKrok3]
    @operId int, @magid int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    SET ANSI_WARNINGS OFF
--atrybut operatora
    DECLARE @atrybut varchar(10)

    select @atrybut = Atr_Wartosc from cdn.atrybuty WITH (NOLOCK)
    where Atr_AtkId=73 and Atr_obinumer = @operId

----print 'Atrybut = '+@atrybut

declare @grupatow int
declare @grupatow1 int

select @grupatow=
case atr_wartosc
when '04' then 117692
else 1 end,
@grupatow1=
case atr_wartosc
when '04' then 117689
else 1 end
from cdn.atrybuty WITH (NOLOCK) where Atr_AtkId=73 and Atr_obinumer = @operId


--kod magazynu
    DECLARE @magKod varchar(10)

    SELECT @magKod = Left(Mag_Kod,1) FROM CDN.Magazyny WITH (NOLOCK) WHERE Mag_GIDNumer = @magid
--print 'MagKod = '+@magKod

--data

    DECLARE @getdate Datetime

    SET @getdate = getdate()

--print 'Data = '+Cast(@getdate as varchar(12))

    DECLARE @BsN_DataUtworzenia int

    SET @BsN_DataUtworzenia = DateDiff(day, '18001228',@getdate)

    DECLARE @BsN_Numer int

    SELECT @BsN_Numer = IsNull(Max(BsN_Numer),0)+1 FROM CDN.BstNag WITH (NOLOCK) WHERE BsN_Seria =@magKod AND BsN_Rok = DatePart(Year, @getdate)

--print 'BsNNumer = '   +Str(@BsN_Numer)

    DECLARE @BsN_RokMiesiac varchar(6)

    select @BsN_RokMiesiac = Ltrim(Str(Year(@getdate))+Right('0'+Ltrim(Str(DatePart(month,@getdate))),2))

--print 'BsN_RokMiesiac = ' + @BsN_RokMiesiac
    --koniec obliczeń
    DECLARE @bsnid int
    SELECT @bsnid = Max(BsN_ID) + 1 FROM CDN.BstNag WITH (NOLOCK)
    --INSERT


    INSERT INTO [CDN].[BstNag]
           ([BsN_ID]
           ,[BsN_DataUtworzenia]
           ,[BsN_OpeWNumer]
           ,[BsN_OpeMNumer]
           ,[BsN_OpeZNumer]
           ,[BsN_FrsId]
           ,[BsN_Rok]
           ,[BsN_Seria]
           ,[BsN_Numer]
           ,[BsN_Nazwa]
           ,[BsN_RokMiesiac]
           ,[BsN_Opis]
           ,[BsN_URL]
           ,[BsN_Stan]
           ,[BsN_DataDo]
           ,[BsN_TwGNumer]
           ,[BsN_TwGTyp]
           ,[BsN_KntNumer]
           ,[BsN_ZakresNormatywu]
           ,[BsN_RodzajNormatywu]
           ,[BsN_MagNNumer]
           ,[BsN_MagNTyp]
           ,[BsN_Aktywny]
           ,[BsN_Miesiac]
           ,[BsN_DataDst]
           ,[BsN_TwGGROTyp]
           ,[BsN_TwGGRONumer]
           ,[BsN_FrMNumer]
           ,[BsN_TSPrzeliczenia]
           ,[BsN_Harmonogram])
     VALUES
           (@bsnid
           ,@BsN_DataUtworzenia
           ,@operId
           ,@operId
           ,@operId
           ,3803
           ,DatePart(Year, @getdate)
           ,@magKod
           ,@BsN_Numer
           ,''
           ,@BsN_RokMiesiac
           ,''
           ,''
           ,4
           ,@BsN_DataUtworzenia
           ,@grupatow
           ,-16
           ,-1
           ,2
           ,1
           ,@magid
           ,208
           ,0
           ,0
           ,@BsN_DataUtworzenia
           ,-16
           ,@grupatow1
           ,3
           ,0
           ,0)

--print 'BstNag utworzony'

    INSERT INTO [CDN].[BstMagazyny]
           ([BsM_BsNID]
           ,[BsM_MagTyp]
           ,[BsM_MagNumer])
     VALUES
           (@bsnid
           ,208
           ,@magid)

    INSERT INTO [CDN].[BstMagazyny]
           ([BsM_BsNID]
           ,[BsM_MagTyp]
           ,[BsM_MagNumer])
     VALUES
           (@bsnid
           ,336
           ,0)
--print 'BstMagazyn utworzone'

    INSERT INTO [CDN].[BstSkladniki]
           ([BsK_BsNID]
           ,[BsK_Rodzaj]
           ,[BsK_UwzgledniajWBilansie]
           ,[BsK_Opis]
           ,[BsK_SQL]
           ,[BsK_Kod]
           ,[BsK_Pozycja])
     select @bsnid ,BSF_ID, 1, BSF_Nazwa, BSF_SQL, BSF_Kod, 0 from
        CDN.BstSkladnikiDefAsaj
        JOIN
        CDN.BstSkladnikiAsaj ON BSF_ID = bsfid
        --2012-03-05
        WHERE mnoznik <> 0
        ORDER BY BSF_ID
--print 'BstSkladniki utworzone'

--tablica pomocnicza do obliczeń
    CREATE TABLE #BstSElem(
    [BsS_TwrNumer] [int] NOT NULL,
    [BsS_BsNID] [int] NOT NULL,
    [BsS_Rodzaj] [smallint] NOT NULL,
    [BsS_Ilosc] [decimal](19, 4) NULL)


-- kursor po towarach BEGIN
    declare @towarid int

    declare @defrodzaj int
    declare @defmnoznik int
    declare @defSQL varchar(2048)

    declare @defWynik decimal(19,4) --wynik obliczeń
    declare @defWynikTotal decimal(19,4)
    declare kursorTowar cursor forward_only FOR

            select   Twr_GIDNumer from cdn.TwrKarty WITH (NOLOCK)
            where
            Left(Twr_Certyfikat,2) = @atrybut
            and Twr_Archiwalny = 0
            ORDER BY Twr_Kod

    open kursorTowar
    FETCH NEXT FROM kursorTowar INTO @towarid

        while @@FETCH_STATUS = 0
        begin

-- kursor po składnikach (def) BEGIN

            --CREATE TABLE #t1 (BsE_TwrNumer int, BsE_BsNID int)
            --INSERT INTO #t1 (BsE_TwrNumer, BsE_BsNID) VALUES (@towarid, @bsnid)
                --declare kursorDef cursor forward_only FOR
                    --select  BsK_Rodzaj, mnoznik, BsK_SQL from CDN.BstSkladniki  WITH (NOLOCK)
                    --JOIN CDN.BstSkladnikiAsaj WITH (NOLOCK) ON BsK_Rodzaj = bsfid
                    --WHERE BsK_BsNID = @bsnid AND IsNull(mnoznik,0) <> 0
                    --ORDER BY BsK_Rodzaj
                --open kursorDef
                --FETCH NEXT FROM kursorDef INTO @defrodzaj, @defmnoznik, @defSQL

                    --while @@FETCH_STATUS = 0
                    --begin



                    --SET @defSQL = 'SELECT ('+@defSQL+') FROM #t1 as T'
                    --CREATE TABLE #wynikTab (wynik decimal(19,4))

                    --INSERT INTO #wynikTab
                    --EXEC (@defSQL)
                    --SELECT @defWynik = @defmnoznik * wynik FROM #wynikTab

                    --DROP TABLE #wynikTab


                    --SET @defWynik = IsNull(@defWynik,0)

                    SET @defWynik = 0

                    INSERT INTO #BstSElem
                       ([BsS_TwrNumer]
                       ,[BsS_BsNID]
                       ,[BsS_Rodzaj]
                       ,[BsS_Ilosc])

                    --VALUES
                    --   (@towarid
                    --   ,@bsnid
                    --   ,@defrodzaj
                    --   ,@defWynik)

        select @towarid,@bsnid, BsK_Rodzaj, CDN.bstile_asaj(@towarid,@magid, @bsnid, BsK_Rodzaj) from CDN.BstSkladniki  WITH (NOLOCK)
        JOIN CDN.BstSkladnikiAsaj WITH (NOLOCK) ON BsK_Rodzaj = bsfid
        WHERE BsK_BsNID = @bsnid AND IsNull(mnoznik,0) <> 0
        ORDER BY BsK_Rodzaj

            --FETCH NEXT FROM kursorDef INTO @defrodzaj, @defmnoznik, @defSQL
            --End
            --close kursorDef
            --DEALLOCATE kursorDef
            --DROP TABLE #t1
            --kursor po składnikach (def)END

        --sprawdzamy czy ilosc rózna od 0

        SET @defWynikTotal = 0
        SELECT @defWynikTotal = IsNull(Sum(BsS_Ilosc),0) from #BstSElem
        set @defWynikTotal=@defWynikTotal*-1

        --set @defWynikTotal = 1
        --print 'Wynik total= '+str(@defWynikTotal)
        if @defWynikTotal > 0
            Begin
            --insertowanie do tablic
            INSERT INTO [CDN].[BstElem]
                   ([BsE_BsNID]
                   ,[BsE_TwrNumer]
                   ,[BsE_DoZamowienia]
                   ,[BsE_Flaga])
             VALUES
                   (@bsnid
                   ,@towarid
                   ,@defWynikTotal
                   ,0)

            INSERT INTO [CDN].[BstSElem]
           ([BsS_TwrNumer]
           ,[BsS_BsNID]
           ,[BsS_Rodzaj]
           ,[BsS_Ilosc])
           SELECT * FROM #BstSElem


            --2012-09-07
            UPDATE [CDN].[BstElem] SET BsE_DoZamowienia = BsE_DoZamowienia * 1
            WHERE BsE_BsNID = @bsnid
            AND BsE_TwrNumer = @towarid


            ENd;


        TRUNCATE TABLE #BstSElem

        FETCH NEXT FROM kursorTowar INTO @towarid
        End
    close kursorTowar
    DEALLOCATE kursorTowar
-- kursor po towarach END




    DROP TABLE #BstSElem

    DECLARE @filtr varchar(50)
    SET @filtr = 'BsN_ID='+Str(@bsnid)

    exec [CDN].[SED_BilansAutoZamawiaczPrc] @filtr,1,1,0,1,1,1
    --print @filtr


    SET ANSI_WARNINGS ON
END