CREATE PROCEDURE [dbo].[spExtraStock] @Tmp_id NUMERIC(18, 0)
AS
DECLARE @Stock TABLE
(
    PersonCode VARCHAR(20),
    GoodsCode VARCHAR(17),
    [Status] TINYINT,
    Stock MONEY,
    RequestQty MONEY,
    GoodsName NVARCHAR(100),
    StartDate SMALLDATETIME,
    EndDate SMALLDATETIME
);


DECLARE @Member_Count AS INTEGER;
SELECT @Member_Count = ISNULL(Guy.Member_Count, 0)
FROM Guy
    INNER JOIN TmpHavaleh
        ON Guy.Guy_Code = TmpHavaleh.Reciver
WHERE TmpHavaleh.Id_Havaleh = @Tmp_id;

IF @Member_Count = 0
   OR @Member_Count = ''
    SET @Member_Count = 1;

INSERT INTO @Stock
(
    PersonCode,
    GoodsCode,
    Status,
    Stock,
    RequestQty,
    GoodsName,
    StartDate,
    EndDate
)
SELECT tb.PersonCode,
       tb.GoodsCode,
       tb.Status,
       MIN(tb.Stock) Stock,
       SUM(tb.RequestQty) RequestQty,
       tb.GoodsName,
       tb.StartDate,
       tb.EndDate
FROM
(
    SELECT PersonCode = CASE
                            WHEN Kind_Flag IN ( 5, 7, 9 ) THEN
                                TmpHavaleh.Reciver
                            WHEN Kind_Flag IN ( 6, 8, 0 ) THEN
                                TmpHavaleh.Sender
                        END + CASE
                                  WHEN Kind_Flag IN ( 7, 8 )
                                       AND ISNULL(Number_bar, '0') <> '0' THEN
                                      Number_bar
                                  ELSE
                                      ''
                              END,
           tblSahmiye_Kala.K_Code GoodsCode,
           ISNULL(tblSahmiye_Kala.Status, 0) Status,
           Stock = CASE ISNULL(tblSahmiye_Kala.Status, 0)
                       WHEN 0 THEN
                           tblSahmiye_Kala.Qty
                       WHEN 1 THEN
                           tblSahmiye_Kala.Qty * @Member_Count
                       WHEN 2 THEN
                           tblSahmiye_Kala.Sarane
                   END,
           RequestQty = (CASE ISNULL(tblSahmiye_Kala.Status, 0)
                             WHEN 2 THEN
           (TmpHavaleh.K_Qty1 * TmpHavaleh.Price_Forosh)
                             ELSE
           (TmpHavaleh.K_Qty1)
                         END
                        ),
           KalaId.Name1 GoodsName,
           tblSahmiye_Kala.Start_Date StartDate,
           tblSahmiye_Kala.End_Date EndDate
    FROM TmpHavaleh
        JOIN dbo.TmpHeadH
            ON TmpHeadH.Id_Havaleh = TmpHavaleh.Id_Havaleh
        JOIN tblSahmiye_Kala
            ON TmpHavaleh.K_Code = tblSahmiye_Kala.K_Code
        JOIN KalaId
            ON TmpHavaleh.K_Code = KalaId.K_Code
    WHERE (TmpHavaleh.Id_Havaleh = @Tmp_id)
          AND (tblSahmiye_Kala.Active = 0)
          AND CONVERT(VARCHAR(10), GETDATE(), 111)
          BETWEEN CONVERT(VARCHAR(10), tblSahmiye_Kala.Start_Date, 111) AND CONVERT(
                                                                                       VARCHAR(10),
                                                                                       tblSahmiye_Kala.End_Date,
                                                                                       111
                                                                                   )
) AS tb
GROUP BY tb.PersonCode,
         tb.GoodsCode,
         tb.Status,
         tb.GoodsName,
         tb.StartDate,
         tb.EndDate;
---------------

IF NOT EXISTS (SELECT * FROM @Stock S)
BEGIN
    SELECT *,
           CAST(0 AS MONEY) ExtraQty,
           CAST(0 AS MONEY) UsedQty
    FROM @Stock;
    RETURN;
END;
IF EXISTS (SELECT * FROM @Stock S WHERE S.RequestQty - S.Stock > 0)
    SELECT CAST(0 AS MONEY) UsedQty,
           Stock,
           RequestQty,
           GoodsCode,
           GoodsName,
           RequestQty - Stock ExtraQty
    FROM @Stock
	WHERE RequestQty - Stock >  0 
ELSE
    SELECT UsedQty = CAST(ISNULL(
                                    SUM(   CASE
                                               WHEN S.Status IN ( 0, 1 ) THEN
                                                   ISNULL(   (CASE
                                                                  WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                                      FD.K_Qty1
                                                              END
                                                             ),
                                                             0
                                                         ) - ISNULL(   (CASE
                                                                            WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                                                FD.K_Qty1
                                                                        END
                                                                       ),
                                                                       0
                                                                   )
                                               ELSE
                                                   ISNULL(   (CASE
                                                                  WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                                      FD.K_Qty1 * FD.Price_Forosh
                                                              END
                                                             ),
                                                             0
                                                         ) - ISNULL(   (CASE
                                                                            WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                                                FD.K_Qty1 * FD.Price_Forosh
                                                                        END
                                                                       ),
                                                                       0
                                                                   )
                                           END
                                       ),
                                    0
                                ) AS MONEY),
           S.GoodsCode,
           S.GoodsName,
           S.RequestQty,
           S.Stock,
           ISNULL(SUM(   CASE
                             WHEN S.Status IN ( 0, 1 ) THEN
                                 ISNULL(   (CASE
                                                WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                    FD.K_Qty1
                                            END
                                           ),
                                           0
                                       ) - ISNULL(   (CASE
                                                          WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                              FD.K_Qty1
                                                      END
                                                     ),
                                                     0
                                                 )
                             ELSE
                                 ISNULL(   (CASE
                                                WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                    FD.K_Qty1 * FD.Price_Forosh
                                            END
                                           ),
                                           0
                                       ) - ISNULL(   (CASE
                                                          WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                              FD.K_Qty1 * FD.Price_Forosh
                                                      END
                                                     ),
                                                     0
                                                 )
                         END
                     ) + S.RequestQty - S.Stock,
                  0
                 ) ExtraQty
    FROM dbo.headH F
        JOIN dbo.Havaleh FD
            ON FD.Id_Havaleh = F.Id_Havaleh
        JOIN @Stock S
            ON S.GoodsCode = FD.K_Code
    WHERE F.Tarikh_faktor
          BETWEEN S.StartDate AND S.EndDate
          AND S.PersonCode = CASE
                                 WHEN Kind_Flag IN ( 5, 7, 9 ) THEN
                                     FD.Reciver
                                 WHEN Kind_Flag IN ( 6, 8, 0 ) THEN
                                     FD.Sender
                             END + CASE
                                       WHEN Kind_Flag IN ( 7, 8 )
                                            AND
                                            (
                                                FD.Sender = '10000'
                                                OR FD.Reciver = '10000'
                                            )
                                            AND ISNULL(Number_bar, '0') <> '0' THEN
                                           Number_bar
                                       ELSE
                                           ''
                                   END
    GROUP BY S.GoodsCode,
             S.GoodsName,
             S.Stock,
             S.RequestQty
    HAVING S.Stock - ISNULL(SUM(   CASE
                                       WHEN S.Status IN ( 0, 1 ) THEN
                                           ISNULL(   (CASE
                                                          WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                              FD.K_Qty1
                                                      END
                                                     ),
                                                     0
                                                 ) - ISNULL(   (CASE
                                                                    WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                                        FD.K_Qty1
                                                                END
                                                               ),
                                                               0
                                                           )
                                       ELSE
                                           ISNULL(   (CASE
                                                          WHEN F.Kind_Flag IN ( 5, 7, 9 ) THEN
                                                              FD.K_Qty1 * FD.Price_Forosh
                                                      END
                                                     ),
                                                     0
                                                 ) - ISNULL(   (CASE
                                                                    WHEN F.Kind_Flag IN ( 6, 8, 0 ) THEN
                                                                        FD.K_Qty1 * FD.Price_Forosh
                                                                END
                                                               ),
                                                               0
                                                           )
                                   END
                               ),
                            0
                           ) - S.RequestQty < 0;


