CREATE PROCEDURE [dbo].[spRandomLottery]
    @StartShamsi VARCHAR(10) ,
    @EndShamsi VARCHAR(10)
AS
    DECLARE @RandomId NUMERIC
    DECLARE @AutoId NUMERIC
    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 @Flag INT    
    
    -- Flag Condition
    -- -1 => تعدا جايزه بيشتر از تعداد فاکتور قرعه کشي
    --  0 => اقلام جايزه وجود ندارد
    --  1 => OK
    SET @RowCounter = 0
----------------- 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 )VALUES  ( '7')
    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   
----------------- رنج پايين و بالا 
    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
            AND Flag = '0'
            
    IF @TmpRowCount > @AvgFactor
        SET @Flag = -1
    IF @TmpRowCount = 0
        SET @Flag = 0       
     
    IF @TmpRowCount < @AvgFactor
        AND @TmpRowCount > 0
        BEGIN
            SET @Flag = 1
 ----------------- 
            DECLARE random_cursor CURSOR
            FOR
                SELECT  AutoId ,
                        Id_Havaleh
                FROM    dbo.Tbl_RandomLottery
                WHERE   CONVERT(VARCHAR(10), StartDate, 111) >= @StartDate
                        AND CONVERT(VARCHAR(10), EndDate, 111) <= @EndDate
                        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
                        @StartDate = @StartDate, -- smalldatetime
                        @EndDate = @EndDate ,  -- smalldatetime                    
                        @Random = @RandomId OUTPUT
                    --ROUND(( ( @UpperId - @LowerId - 1 ) * RAND() + @LowerId ), 0)        
                                           
                    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



