CREATE PROCEDURE [dbo].[spRandomLottery]
    @StartShamsi VARCHAR(10),
    @EndShamsi VARCHAR(10),
    @StartFactorNumber BIGINT,
    @EndFactorNumber BIGINT,
    @LotteryKind TINYINT,
    @UserId SMALLINT
AS
DECLARE @RandomId INT;
DECLARE @AutoId INT;
DECLARE @Id_Havaleh NUMERIC;
DECLARE @LowerId NUMERIC;
DECLARE @UpperId NUMERIC;
DECLARE @FactCount INT;
DECLARE @SaleDay INT;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
DECLARE @DateDiff INT;
DECLARE @MaxIdHavaleh INT;
DECLARE @AvgFactor INT;
DECLARE @RowCounter INT;
DECLARE @TmpRowCount INT;
DECLARE @Drawed INT;
DECLARE @Flag INT;

-- Flag Condition
-- -1 => تعدا جايزه بيشتر از تعداد فاکتور قرعه کشي
--  0 => اقلام جايزه وجود ندارد
--  1 => OK
SET @RowCounter = 0;
SET @MaxIdHavaleh = 0;
SET @SaleDay = 0;
SET @FactCount = 0;

IF @LotteryKind = 0
BEGIN
    ----------------- Cast Date 
    SET @StartDate = dbo.ShamsiToMiladi(@StartShamsi);
    SET @EndDate = dbo.ShamsiToMiladi(@EndShamsi);
    SET @DateDiff = DATEDIFF(DAY, @StartDate, @EndDate);
    SET @DateDiff = @DateDiff + 1;
    --------------  بزرگترين Id_Havaleh
    INSERT INTO dbo.TmpHeadH
    (
        Kind_Flag,
        User_id
    )
    VALUES
    ('7', @UserId);
    SELECT @MaxIdHavaleh = SCOPE_IDENTITY();
    DELETE FROM dbo.TmpHeadH
    WHERE Id_Havaleh = @MaxIdHavaleh;
    --------------  محاسبه تعداد کل فاکتور      
    SELECT @FactCount = COUNT(Id_Havaleh)
    FROM dbo.headH
    WHERE Kind_Flag = 7
          AND Tarikh > DATEADD(DAY, -30, GETDATE());
    --------------  محاسبه تعداد روز فروش 
    SELECT @SaleDay = COUNT(T1.FactCount)
    FROM
    (
        SELECT COUNT(Id_Havaleh) FactCount
        FROM dbo.headH
        WHERE Kind_Flag = 7
              AND Tarikh > DATEADD(DAY, -30, GETDATE())
        GROUP BY CONVERT(VARCHAR(10), Tarikh, 111)
    ) AS T1;
    ----------------- رنج پايين و بالا 
    IF @FactCount = 0
    BEGIN
        SET @AvgFactor = -1;
        SET @TmpRowCount = -1;
    END;
    ELSE
    BEGIN
        SET @AvgFactor = (@FactCount / @SaleDay) * @DateDiff;
        SET @LowerId = @MaxIdHavaleh;
        SET @UpperId = @MaxIdHavaleh + @AvgFactor;
        SELECT @TmpRowCount = COUNT(AutoId)
        FROM dbo.Tbl_RandomLottery
        WHERE CONVERT(VARCHAR(10), StartDate, 111) >= @StartDate
              AND CONVERT(VARCHAR(10), EndDate, 111) <= @EndDate;
    END;
END;
ELSE
BEGIN
    SET @LowerId = @StartFactorNumber;
    SET @UpperId = @EndFactorNumber;
    SET @AvgFactor = @EndFactorNumber - @StartFactorNumber;
    SET @FactCount = @AvgFactor;

    SELECT @Drawed = COUNT(AutoId)
    FROM dbo.Tbl_RandomLottery
    WHERE StartFactorNumber >= @StartFactorNumber
          AND EndFactorNumber <= @EndFactorNumber
          AND Flag IN ( 1, 2 );

    SELECT @TmpRowCount = COUNT(AutoId)
    FROM dbo.Tbl_RandomLottery
    WHERE StartFactorNumber >= @StartFactorNumber
          AND EndFactorNumber <= @EndFactorNumber;
--AND Flag = 0;
END;

IF @AvgFactor = -1
   AND @TmpRowCount = -1
   AND @LotteryKind = 0
    SET @Flag = -2;

ELSE IF (
            @LotteryKind = 0
            AND @TmpRowCount > @AvgFactor
        )
        OR (
               @LotteryKind = 1
               AND @Drawed = @TmpRowCount
           )
    SET @Flag = -1;

ELSE IF @TmpRowCount = 0
    SET @Flag = 0;
ELSE IF @TmpRowCount < @AvgFactor
        AND @TmpRowCount > 0
BEGIN
    SET @Flag = 1;
    ----------------- 
    DECLARE random_cursor CURSOR FOR
    SELECT AutoId,
           Id_Havaleh
    FROM dbo.Tbl_RandomLottery
    WHERE (
              @LotteryKind = 0
              AND CONVERT(VARCHAR(10), StartDate, 111) >= @StartDate
              AND CONVERT(VARCHAR(10), EndDate, 111) <= @EndDate
          )
          OR (
                 @LotteryKind = 1
                 AND StartFactorNumber >= @StartFactorNumber
                 AND EndFactorNumber <= @EndFactorNumber
             )
             AND Flag = '0';

    OPEN random_cursor;
    FETCH NEXT FROM random_cursor
    INTO @AutoId,
         @Id_Havaleh;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @RowCounter = @RowCounter + 1;
        EXECUTE spCreateRandom @Lower = @LowerId,           -- numeric
                               @Upper = @UpperId,           -- numeric
                               @LotteryKind = @LotteryKind, -- tiny int 
                               @StartDate = @StartDate,     -- smalldatetime
                               @EndDate = @EndDate,         -- smalldatetime							                     
                               @Random = @RandomId OUTPUT;
        UPDATE dbo.Tbl_RandomLottery
        SET Flag = 1,
            Id_Havaleh = @RandomId
        WHERE AutoId = @AutoId;
        FETCH NEXT FROM random_cursor
        INTO @AutoId,
             @Id_Havaleh;
    END;
    CLOSE random_cursor;
    DEALLOCATE random_cursor;
END;
--------------  
SELECT @SaleDay SaleDay,
       @FactCount FactCount,
       @DateDiff [DateDiff],
       @LowerId LowerId,
       @UpperId UpperId,
       @AvgFactor AvgFactor,
       @RowCounter RowCounter,
       @Flag ResultFlag;
