CREATE PROCEDURE [dbo].[Sodor_Exp] @Main_Id NUMERIC(18, 0)
AS
DECLARE @E_Qty REAL; --مقدار کالا در رکورد فعال فايل تاريخ انقضا
DECLARE @E_Exp_date DATETIME; -- تاريخ انقضا کالا در رکورد
DECLARE @AutoId BIGINT; -- متغير کد لينک 

--ابتدا چک ميکند اگر  تمپ حواله داراي رکوردي با فلاگ يک بود اين روتين اجرا ميشود
IF
(
    SELECT COUNT(*)
    FROM dbo.TmpHavaleh
    WHERE (Id_Havaleh = @Main_Id)
          AND (Exp_Flag = 1)
) > 0
BEGIN
    -- اگر تمپ انقضا داراي مقدار باشد به عنوان رسيد عمل مي کند و کل ان پس از اصلاح شماره به جدول اصلي کپي مي شود
    IF
    (
        SELECT COUNT(*)
        FROM dbo.TmpExp
            INNER JOIN dbo.TmpHavaleh
                ON dbo.TmpExp.AutoId = dbo.TmpHavaleh.AutoId
        WHERE (dbo.TmpHavaleh.Id_Havaleh = @Main_Id)
    ) > 0
    BEGIN

        --From
        -- Dbo.TmpExp  WHERE     (AutoId= @Tmp_Id)  ) > 0  Begin
        --      Update Dbo.TmpExp   Set [Id] =@Main_Id  Where [id]=@Tmp_Id
        --      Insert Into Dbo.Exp_File   Select * from Dbo.TmpExp Where  [id]=@Main_Id
        --      Delete From Dbo.TmpExp   where  [id]=@Main_Id
        --//////////////////////////////
        DECLARE CursorExp CURSOR FOR
        SELECT dbo.TmpExp.AutoId,
               dbo.TmpExp.K_Qty,
               dbo.TmpExp.Exp_Date
        FROM dbo.TmpExp
            INNER JOIN dbo.TmpHavaleh
                ON dbo.TmpExp.AutoId = dbo.TmpHavaleh.AutoId
        WHERE (dbo.TmpHavaleh.Id_Havaleh = @Main_Id);
        OPEN CursorExp;

        FETCH NEXT FROM CursorExp
        INTO @AutoId,
             @E_Qty,
             @E_Exp_date;

        WHILE @@FETCH_STATUS = 0
        BEGIN

            INSERT INTO dbo.Exp_File
            (
                AutoId,
                K_Qty,
                Exp_Date
            )
            VALUES
            (@AutoId, @E_Qty, @E_Exp_date);


            FETCH NEXT FROM CursorExp
            INTO @AutoId,
                 @E_Qty,
                 @E_Exp_date;
        END;

        CLOSE CursorExp;
        DEALLOCATE CursorExp;

    --///////////////////////////////

    --        Insert Into Dbo.Exp_File(           AutoId            ,                 K_Qty      ,        Exp_Date)
    --SELECT          dbo.TmpExp.AutoId , dbo.TmpExp.K_Qty, dbo.TmpExp.Exp_Date
    --FROM         dbo.TmpExp INNER JOIN  dbo.TmpHavaleh ON dbo.TmpExp.AutoId = dbo.TmpHavaleh.AutoId
    --WHERE     (dbo.TmpHavaleh.Id_Havaleh = @Main_Id)


    END;
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -- در اين حالت حواله است و سيستم به صورت خودکار انقضا  را تشخيص داده و اصلاح مي کند
    ELSE
    BEGIN
        DECLARE @H_Sender VARCHAR(5); --کد فرستنده حواله
        DECLARE @H_Reciver VARCHAR(5); --کد گيرنده حواله
        DECLARE @H_K_Code VARCHAR(15); -- کد کالاي رکورد حواله	
        DECLARE @H_Sum_Qty REAL; -- جمع مقادير کالا ها در حواله 
        DECLARE @E_A_Code VARCHAR(5); --کد انبار در فايل تاري انقضا
        DECLARE @E_K_Code VARCHAR(15); -- کد کالا در فايل تاريخ انقضا
        DECLARE @tmp_Qty REAL; -- متغير تمپ


        SET @tmp_Qty = 0;

        -- ايجاد يک فچ روي رکوردهايي از تمپ حواله که فلگ انها يک است
        DECLARE Cursor_Havaleh CURSOR FOR
        SELECT AutoId,
               Sender,
               Reciver,
               K_Code,
               K_Qty1 AS [sum(qty)]
        FROM dbo.TmpHavaleh
        WHERE (LEN(Sender) = 3)
              AND (Exp_Flag = 1)
              AND (Id_Havaleh = @Main_Id);

        --   HAVING      (Exp_Flag = 1) AND (Id_Havaleh = @Main_Id )
        --	GROUP BY Sender , Reciver , K_Code, Id_Havaleh, Exp_Flag
        OPEN Cursor_Havaleh;

        FETCH NEXT FROM Cursor_Havaleh
        INTO @AutoId,
             @H_Sender,
             @H_Reciver,
             @H_K_Code,
             @H_Sum_Qty;

        WHILE @@FETCH_STATUS = 0
        BEGIN

            SET @tmp_Qty = @H_Sum_Qty;

            -- ايجاد يک فچ روي رکوردهايي از فايل تاريخ انقضا که مشخصات انها مثل متغير هاي رکورد تمپ حواله است
            DECLARE Cursor_Exp CURSOR FOR
            SELECT A_Code,
                   K_Code,
                   Exp_Date,
                   K_Qty
            FROM dbo.Exp_Date
            WHERE (A_Code = @H_Sender)
                  AND (K_Code = @H_K_Code)
            ORDER BY Exp_Date;

            OPEN Cursor_Exp;

            FETCH NEXT FROM Cursor_Exp
            INTO @E_A_Code,
                 @E_K_Code,
                 @E_Exp_date,
                 @E_Qty;
            --در اينجا بر حسب مقدار تمپ و مقدار تاريخ انقضا يک حلقه درست کرده و تا زماني که مقدار
            --متغير تمپ صفر گرددرکوردهاي درون حلقه را در فايل حواله تاريخ انقضا ثبت کرده و مقدار 
            --فايل تاريخ انقضا را به روز رساني ميکند
            IF @tmp_Qty > @E_Qty
               AND @E_Qty <> 0
                WHILE (@@FETCH_STATUS = 0 AND @tmp_Qty > 0)
                BEGIN
                    IF @E_Qty <= @tmp_Qty
                    BEGIN
                        INSERT INTO dbo.Exp_File
                        (
                            AutoId,
                            K_Qty,
                            Exp_Date
                        )
                        VALUES
                        (@AutoId, @E_Qty, @E_Exp_date);
                        --   	        Update Dbo.Exp_Date Set K_QTY = 0 where A_Code = @E_A_Code AND  K_Code = @E_K_Code AND Exp_Date = @E_Exp_date AND K_Qty = @E_Qty
                        SET @tmp_Qty = @tmp_Qty - @E_Qty;
                    END;
                    ELSE
                    BEGIN
                        INSERT INTO dbo.Exp_File
                        (
                            AutoId,
                            K_Qty,
                            Exp_Date
                        )
                        VALUES
                        (@AutoId, @tmp_Qty, @E_Exp_date);
                        -- 	          Update Dbo.Exp_Date Set K_QTY = @E_Qty-@Tmp_Qty where A_Code = @E_A_Code AND  K_Code = @E_K_Code AND Exp_Date = @E_Exp_date AND K_Qty = @E_Qty
                        SET @tmp_Qty = 0;
                    END;

                    IF @tmp_Qty = 0
                        BREAK;

                    FETCH NEXT FROM Cursor_Exp
                    INTO @E_A_Code,
                         @E_K_Code,
                         @E_Exp_date,
                         @E_Qty;

                END;
            ELSE IF @E_Qty <> 0
            BEGIN
                INSERT INTO dbo.Exp_File
                (
                    AutoId,
                    K_Qty,
                    Exp_Date
                )
                VALUES
                (@AutoId, @tmp_Qty, @E_Exp_date);
            --        	 Update Dbo.Exp_Date Set K_QTY = @E_Qty - @tmp_Qty where A_Code = @E_A_Code AND  K_Code = @E_K_Code AND Exp_Date = @E_Exp_date AND K_Qty = @E_Qty
            END;

            CLOSE Cursor_Exp;
            DEALLOCATE Cursor_Exp;

            FETCH NEXT FROM Cursor_Havaleh
            INTO @AutoId,
                 @H_Sender,
                 @H_Reciver,
                 @H_K_Code,
                 @H_Sum_Qty;
        END;

        CLOSE Cursor_Havaleh;
        DEALLOCATE Cursor_Havaleh;

        --  Delete From Exp_Date where K_Qty=0
        IF
        (
            SELECT COUNT(*) FROM dbo.Exp_Date WHERE K_Qty = 0
        ) > 0
            DELETE FROM dbo.Exp_Date
            WHERE K_Qty = 0;


    END;
END;
