定时执行sql

USE [zc_pro]
GO
/****** Object:  StoredProcedure [dbo].[PROC_DEAL_DATE_ZC]    Script Date: 2017/8/25 9:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PROC_DEAL_DATE_ZC]
AS
BEGIN
    -- 定义当前时间
    DECLARE @CUR_DATE DATETIME;
    SET @CUR_DATE = GETDATE();
    -- 定义众采活动ID
    DECLARE @GBIID INT;
    -- 定义实际、额定、最低参团人数
    DECLARE @FACTNUM INT;
    DECLARE @RATENUM INT;
    DECLARE @MINNUM INT;
    /**********************************************************************
    ***************----------未处理的众采----------************************
    ***********************************************************************/
    -- 情况一：处理已达到定金支付截止日期的众采活动 
    --（[dbo].[GroupBuy_Info].[Status] = 10 @CUR_DATE >= [dbo].[GroupBuy_Info].[DepositEndTime]）
    -- 众采活动状态：定金阶段 并且 当前时间大于定金支付截止时间
    -- 定义游标
    DECLARE CURSOR_DEPOSIT CURSOR SCROLL FOR
    SELECT [GroupBuyInfoID],[RegulationsCount],[MinRegulationsCounts] FROM zc_pro.DBO.GroupBuy_Info WHERE [Status] = 10 AND [DepositEndTime] <= @CUR_DATE;
    -- 打开游标
    OPEN CURSOR_DEPOSIT;
    FETCH NEXT FROM CURSOR_DEPOSIT INTO @GBIID,@RATENUM,@MINNUM;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -----------------------------------------------------------------------------------------------------------------------
        -----------------------------------处理已达到定金截止日期的众采活动----------------------------------------------------
        -- 获得实际参与人数
        -- 实际参与人数为 【众采参与表】中，状态为已支付定金状态的数据；申请定金退款状态不在此列；
        SELECT @FACTNUM = COUNT([AddGroupBuyInfoID]) FROM zc_pro.dbo.Add_Group_Buy_Info WHERE [AddGroupStatus] = 10;

        IF @FACTNUM = 0
        BEGIN
            -- 没有一个人支付定金，则标记众采活动为失败
            UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = -10 WHERE [GroupBuyInfoID] = @GBIID;
        END
        ELSE IF @FACTNUM > 0 AND @FACTNUM < @MINNUM
        BEGIN
            -- 支付定金的人数大于0，并且小于最低成团人数时，则标记众采活动为失败
            UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = -10 WHERE [GroupBuyInfoID] = @GBIID;
            -- 同时标记已支付定金的状态为【团购失败需退定金】
            UPDATE zc_pro.dbo.Add_Group_Buy_Info SET AddGroupStatus = -30 WHERE [GroupBuyInfoID] = @GBIID;
            -- 涉及到的短信类型
            -- 众采活动失败通知（退定金提示）
            INSERT INTO zc_pro.dbo.NeedSendMsg    
            SELECT TB.UserInfoID, TB.Phone, @GBIID AS GBID,'失败需退定金' AS MSGTYPE, dbo.MSGDATE() AS AFTERDATE, 0 ISSEND, GETDATE() ADDDATE  
            FROM zc_pro.dbo.Add_Group_Buy_Info TA 
            LEFT JOIN zc_pro.dbo.User_Info TB ON TA.UserInfoID = TB.UserInfoID 
            WHERE TA.GroupBuyInfoID = @GBIID AND TB.IsFictitiousUser = 1 AND TA.AddGroupStatus = 10;
        END
        ELSE IF @FACTNUM >= @MINNUM AND @FACTNUM < @RATENUM
        BEGIN
            -- 支付定金的人数大于等于最低成团人数，并且小于额定成团人数时，则标记众采活动为尾款阶段
            UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = 20 WHERE [GroupBuyInfoID] = @GBIID;
            -- 增加虚拟参团人员，以达到指定人数
            DECLARE @NEED_USER_NUM INT;
            SET @NEED_USER_NUM = @RATENUM - @FACTNUM;
            INSERT INTO zc_pro.dbo.Add_Group_Buy_Info
                    ( GroupBuyInfoID ,
                      UserInfoID ,
                      AddTime ,
                      IsFictitiousUser ,
                      IsPayDeposit ,
                      PayDepositPrice ,
                      PayDepositTime ,
                      IsPayRetainage ,
                      PayRetainagePrice ,
                      PayRetainageTime ,
                      ApplyRefundTime ,
                      AddGroupStatus
                    )
            SELECT TOP (@NEED_USER_NUM) @GBIID,[UserInfoID],GETDATE() AS [AddTime], 0 AS [IsFictitiousUser], 
            1 AS [IsPayDeposit], 0 AS [PayDepositPrice], DATEADD(SECOND,-185,GETDATE()) AS [PayDepositTime], 
            0 AS [IsPayRetainage], 0 AS [PayRetainagePrice], NULL AS [PayRetainageTime], NULL AS [ApplyRefundTime], 
            10 AS [AddGroupStatus]
            FROM zc_pro.DBO.User_Info WHERE IsFictitiousUser = 0 ORDER BY NEWID();
            -- 涉及到的短信类型
            -- 众采活动成功通知（支付尾款提示）
            INSERT INTO zc_pro.dbo.NeedSendMsg    
            SELECT TB.UserInfoID, TB.Phone, @GBIID AS GBID,'成功需支付尾款' AS MSGTYPE, dbo.MSGDATE() AS AFTERDATE, 0 ISSEND, GETDATE() ADDDATE  
            FROM zc_pro.dbo.Add_Group_Buy_Info TA 
            LEFT JOIN zc_pro.dbo.User_Info TB ON TA.UserInfoID = TB.UserInfoID 
            WHERE TA.GroupBuyInfoID = @GBIID AND TB.IsFictitiousUser = 1 AND TA.AddGroupStatus = 10;
        END
        ELSE IF @FACTNUM = @RATENUM
        BEGIN
            -- 当实际参与人数等于额定人数时，标记众采活动状态为【尾款阶段】
            UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = 20 WHERE [GroupBuyInfoID] = @GBIID;
            -- 涉及到的短信类型
            -- 众采活动成功通知（支付尾款提示）
            INSERT INTO zc_pro.dbo.NeedSendMsg    
            SELECT TB.UserInfoID, TB.Phone, @GBIID AS GBID,'成功支付尾款' AS MSGTYPE, dbo.MSGDATE() AS AFTERDATE, 0 ISSEND, GETDATE() ADDDATE  
            FROM zc_pro.dbo.Add_Group_Buy_Info TA 
            LEFT JOIN zc_pro.dbo.User_Info TB ON TA.UserInfoID = TB.UserInfoID 
            WHERE TA.GroupBuyInfoID = @GBIID AND TB.IsFictitiousUser = 1 AND TA.AddGroupStatus = 10;
        END
        
        

        FETCH NEXT FROM CURSOR_DEPOSIT INTO @GBIID,@RATENUM,@MINNUM;
        -----------------------------------------------------------------------------------------------------------------------
    END
    -- 关闭并释放游标
    CLOSE CURSOR_DEPOSIT;
    DEALLOCATE CURSOR_DEPOSIT;

    -- 情况二：处理已达到尾款截止日期的众采活动 
    --（[dbo].[GroupBuy_Info].[Status] = 20 @CUR_DATE >= [dbo].[GroupBuy_Info].[RetainageStartTime]）
    -- 众采活动状态：尾款阶段 并且 当前时间大于尾款截止日期
    DECLARE CURSOR_TAIL CURSOR SCROLL FOR
    SELECT [GroupBuyInfoID] FROM zc_pro.DBO.GroupBuy_Info WHERE [Status] = 20 AND [RetainageStartTime] <= @CUR_DATE;

    OPEN CURSOR_TAIL;
    FETCH NEXT FROM CURSOR_TAIL INTO @GBIID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -----------------------------------------------------------------------------------------------------------------------
        -----------------------------------处理已达到尾款截止日期的众采活动----------------------------------------------------
        -- 标记众采活动为尾款统筹阶段
        UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = 40 WHERE GroupBuyInfoID = @GBIID;
        FETCH NEXT FROM CURSOR_TAIL INTO @GBIID;
        -----------------------------------------------------------------------------------------------------------------------
    END
    CLOSE CURSOR_TAIL;
    DEALLOCATE CURSOR_TAIL;

    -- 情况三：处理已达到指标截止日期的众采活动
    --（[dbo].[GroupBuy_Info].[Status] = 30 @CUR_DATE >= [dbo].[GroupBuy_Info].[RetainageEndTime]）
    -- 众采活动状态：指标阶段 并且 当前时间大于指标截止日期
    DECLARE CURSOR_QUOTA CURSOR SCROLL FOR
    SELECT [GroupBuyInfoID] FROM zc_pro.DBO.GroupBuy_Info WHERE [Status] = 30 AND [RetainageEndTime] <= @CUR_DATE;

    OPEN CURSOR_QUOTA;
    FETCH NEXT FROM CURSOR_QUOTA INTO @GBIID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -----------------------------------------------------------------------------------------------------------------------
        -----------------------------------处理已达到指标支付截止日期的众采活动------------------------------------------------
        -- 标记众采活动为指标统筹阶段；
        UPDATE zc_pro.DBO.GroupBuy_Info SET [Status] = 60 WHERE GroupBuyInfoID = @GBIID;
        FETCH NEXT FROM CURSOR_QUOTA INTO @GBIID;
        -----------------------------------------------------------------------------------------------------------------------
    END
    CLOSE CURSOR_QUOTA;
    DEALLOCATE CURSOR_QUOTA;

END