PROCEDURE [CDN].[SED_ZamowieniaFiPo]

USE [CDNXL_szkola]
GO

/****** Object:  StoredProcedure [CDN].[SED_ZamowieniaFiPo]    Script Date: 2014-06-25 15:45:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      <Author,,Name>
-- Create date: 2014-06-23
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [CDN].[SED_ZamowieniaFiPo]
    @frsid int, @magKod varchar(10)
AS
BEGIN
    SET NOCOUNT ON;


    SET ANSI_WARNINGS OFF

    --INSERT INTO dbo.SED_log (program, klucz, wartosc)
    --VALUES ('SED_ZamowieniaFiPo','proc','start')
--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
SET @grupatow = 1
SET @grupatow1 = 1

--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

--AUTOMAT
    DEclare @operId int
    SET @operId = 536


--id magazynu

    DECLARE @magId int

    SELECT @magId = Mag_GIDNumer  FROM CDN.Magazyny WITH (NOLOCK) WHERE  Mag_kod = @magKod



--data

    DECLARE @getdate Datetime

    SET @getdate = getdate()



    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 =Left(@magKod,1) AND BsN_Rok = DatePart(Year, @getdate)



    DECLARE @BsN_RokMiesiac varchar(6)

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


    --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]
            ,[Bsn_Asajautomat])
     VALUES
           (@bsnid
           ,@BsN_DataUtworzenia
           ,@operId
           ,@operId
           ,@operId
           ,@frsid
           ,DatePart(Year, @getdate)
           ,Left(@magKod,1)
           ,@BsN_Numer
           ,''
           ,@BsN_RokMiesiac
           ,''
           ,''
           ,4
           ,@BsN_DataUtworzenia
           ,1
           ,-16
           ,-1
           ,2
           ,1
           ,@magid
           ,208
           ,0
           ,0
           ,@BsN_DataUtworzenia
           ,-16
           ,@grupatow1
           ,3
           ,0
           ,0
            ,1)

--print 'BstNag utworzony'
--INSERT INTO dbo.SED_log (program, klucz, wartosc)
--VALUES ('SED_ZamowieniaFiPo','proc','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'
--INSERT INTO dbo.SED_log (program, klucz, wartosc)
--VALUES ('SED_ZamowieniaFiPo','proc','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)


    declare @tablica TABLE (twrnumer int)
    INSERT INTO @tablica (twrnumer)
    --declare @magid int
--SET @magid = 15
    select Twr_GIDNumer
            from cdn.TwrKarty WITH (NOLOCK)
            where
            Twr_Archiwalny = 0
            AND
            (
            (Twr_GIDNumer in (SELECT Rez_TwrNumer FROM CDN.Rezerwacje WITH (NOLOCK) WHERE Rez_MagNumer = @MagId))
            OR
            ((SELECT Sum(Twz_Ilosc) FROM CDN.TwrZasoby WITH (NOLOCK) WHERE TwZ_TwrNumer = TWr_GIDNumer AND TwZ_MagNumer = @magid) <
            (SELECT Sum(Tpm_IloscMin) FROM CDN.TwrParMag WITH (NOLOCK) WHERE Tpm_TwrNumer = Twr_GIDNumer AND Tpm_MagNumer = @magid))
            )
            AND Left(Twr_Certyfikat,2)<>'04'
            ORDER BY Twr_GIDNumer

-- 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 twrnumer FROM @tablica
            ORDER BY twrnumer

    open kursorTowar
    FETCH NEXT FROM kursorTowar INTO @towarid

        while @@FETCH_STATUS = 0
        begin
--      INSERT INTO dbo.SED_log (program, klucz, wartosc)
--VALUES ('SED_ZamowieniaFiPo','proc','kursor begin @towarid='+str(@towarid))
-- 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

--          INSERT INTO dbo.SED_log (program, klucz, wartosc)
--VALUES ('SED_ZamowieniaFiPo','proc','INSERT INTO #BstSElem')
        --2013-06-27
        --dodanie powiązań ZS z ZZ i ZW
        if (1=2)
        Begin


            INSERT INTO CDN.SED_Bilans_ZS (BstZS_ZaNGIDNumer, BstZS_BsNID, BstZS_ZaEGIDLp, BstZS_TwrNumer, BstZS_Ilosc)
            SELECT ZaN_GIDNumer, BsN_ID , ZaE_GIDLp, ZaE_TwrNumer, Sum(IsNull(Rez_Ilosc - Rez_Zrealizowano - Rez_IloscMag,0)) FROM CDN.Rezerwacje (nolock)
            join cdn.zamnag on Rez_ZrdNumer=zan_gidnumer and Rez_ZrdTyp = ZaN_GIDTyp
            JOIN CDN.ZamElem on ZaN_GIDNumer = ZaE_GIDNumer AND ZaE_GIDLp = Rez_ZrdLp
            join cdn.bstnag on BsN_ID=@bsnid
            WHERE Rez_TwrTyp = 16 AND Rez_TwrNumer =@towarid AND Rez_GIDTyp = 2576
            AND ((Rez_ZrdTyp = 960 AND Rez_Zrodlo = 9) OR (Rez_Zrodlo = 10 AND Rez_Aktywna = 1))
            and right(zan_zamseria,2)<>'re' and right(zan_zamseria,3)<>'reo' and Rez_KntNumer<>59477
            and rez_magnumer=@magid and rez_datawaznosci>DateDiff(day,'18001228',getdate()) and zan_cechaopis='ŻÓŁTE'
            GROUP BY ZaN_GIDNumer,BsN_ID, ZaE_GIDLp, ZaE_TwrNumer



        END

            --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

--          INSERT INTO dbo.SED_log (program, klucz, wartosc)
--VALUES ('SED_ZamowieniaFiPo','proc','kursor END')

        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

    SELECT @bsnid as BSNID


    SET ANSI_WARNINGS ON
END






GO