GO

/****** Object:  StoredProcedure [dbo].[UP_Job_UpdatePromotionBeginInfo]    Script Date: 08/19/2014 19:02:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 ALTER PROCEDURE [dbo].[UP_Job_UpdatePromotionBeginInfo]

AS

    BEGIN 

        DECLARE MyFirstCursor CURSOR

        FOR

            SELECT  a.ProductProID ,

                    a.productItemid ,

                    p.productid ,

                    a.ProPromKindID ,

                    a.PromPrice,

                    --新添加五个字段

                    a.IsLimitBuyStatus,

                    a.LimitBuyPersonalCount,

                    a.LimitBuyCount,

                    a.bDate,

                    a.eDate

            FROM    ProductPro a

                    INNER JOIN productitem item ON a.productitemid = item.productitemid

                    INNER JOIN product p ON p.productid = item.productid

            WHERE   a.BDate <= GETDATE()

                    AND a.EDate >= GETDATE()

                    AND a.[State] = 0

        OPEN MyFirstCursor

        DECLARE @ProductProID INT

        DECLARE @productItemid INT

        DECLARE @productid INT

        DECLARE @ProPromKindID INT

        DECLARE @num INT 

        DECLARE @PromPrice MONEY

        --新添加五个字段

        DECLARE @IsPur INT

        DECLARE @OnePurNum INT

        DECLARE @TotalPurNum INT

        DECLARE @StartDate DATETIME

        DECLARE @LastDate DATETIME

        FETCH NEXT FROM MyFirstCursor INTO @ProductProID, @productItemid, @productid, @ProPromKindID, @PromPrice,@IsPur,@OnePurNum,@TotalPurNum,@StartDate,@LastDate

        WHILE ( @@FETCH_STATUS = 0 )

            BEGIN

   --修改ProductPro 状态     

                UPDATE  dbo.ProductPro

                SET     [State] = 1

                WHERE   ProductProID = @ProductProID

                UPDATE TOP ( 1 )

                        product

                SET     ProductAttributeID = @ProPromKindID ,

                        Price = @PromPrice ,

                        Price2 = @PromPrice

                WHERE   productid = @productid

         

                UPDATE TOP ( 1 )

                        productitem

                SET     OldPrice = price ,

                        price = @PromPrice

                        --新添加五个字段

                        ,IsPur=@IsPur

                        ,OnePurNum=@OnePurNum

                        ,TotalPurNum=@TotalPurNum

                        ,StartDate=@StartDate

                        ,LastDate=@LastDate

                WHERE   productitemid = @productItemid

    --xia 20140218 限制团购数量 改动

                SELECT  @num = LimitNum

                FROM    dbo.ProductPro

                WHERE   ProductProID = @ProductProID

                UPDATE  productitem

                SET     StoreNum = @num

                WHERE   ProductItemID = @productItemid

               

           

    

         

            --读取下一行

                FETCH NEXT FROM MyFirstCursor INTO @ProductProID,

                    @productItemid, @productid, @ProPromKindID, @PromPrice

            END

    --关闭游标

        CLOSE MyFirstCursor

    --删除游标

        DEALLOCATE MyFirstCursor

   

  

    END