SQLsummary/存储过程/管理奖1.0.sql

CREATE DEFINER=`bi`@`%` PROCEDURE `BONUS_GL`(
                     `PRM_PERIOD_NUM` INT
                     )
BEGIN
    DECLARE
    Done INT DEFAULT 0;
    DECLARE
    R1_USER_NAME VARCHAR ( 30 );
    DECLARE
    R1_REAL_NAME VARCHAR ( 50 );
    DECLARE
    R1_USER_LEVEL VARCHAR ( 10 );
    DECLARE
    R1_PARENT_LEVEL VARCHAR ( 10 );
    DECLARE
    R1_PARENT_USER_NAME VARCHAR ( 30 );
    DECLARE
    R3_USER_NAME VARCHAR ( 30 );
    DECLARE
    R3_REAL_NAME VARCHAR ( 50 );
    DECLARE
    R3_PARENT_USER_NAME VARCHAR ( 30 );
    DECLARE
    R3_PARENT_REAL_NAME VARCHAR ( 50 );
    DECLARE
    R1_PARENT_REAL_NAME VARCHAR ( 50 );
    DECLARE
    SHIJIDAISHU INT DEFAULT 0;
    DECLARE
    JICENGDAISHU INT DEFAULT 0;
    DECLARE
    JIJIANGDAISHU INT DEFAULT 0;
    DECLARE
    R1_ORI_BONUS_QY_BD DECIMAL ( 16, 3 );
    DECLARE
    R2_USER_NAME VARCHAR ( 30 );
    DECLARE
    R2_REAL_NAME VARCHAR ( 50 );
    DECLARE
    R2_ORI_BONUS_QY_BD DECIMAL ( 16, 3 );
    DECLARE
    R2_BDYJ DECIMAL ( 16, 3 );
    DECLARE
    R3_BDYJ DECIMAL ( 16, 3 );
    DECLARE
    R2_PARENT_USER_NAME VARCHAR ( 30 );
    DECLARE
    R2_PARENT_REAL_NAME VARCHAR ( 50 );
    DECLARE
    XIANAJIANGJIN DECIMAL ( 16, 3 );
    DECLARE
    TUIGUANGRENSHU INT;
    DECLARE
    DAISHUXIANZHI INT;
    DECLARE
    JIANGJINBILI DECIMAL ( 16, 3 );
    /* 声明游标 */
    DECLARE
    rs
    CURSOR FOR
    SELECT
              U.USER_NAME
            , U.REAL_NAME
            , B.ORI_BONUS_QY_BD
            , U2.USER_NAME AS PARENT_USER_NAME
            , U2.REAL_NAME AS PARENT_REAL_NAME
    FROM
              ar_calc_bonus B
              LEFT JOIN
                        ar_user U
                        ON
                                  U.ID = B.USER_ID
              LEFT JOIN
                        ar_user_relation_new R
                        ON
                                  R.USER_ID = B.USER_ID
              LEFT JOIN
                        ar_user U2
                        ON
                                  U2.ID = R.PARENT_UID
              LEFT JOIN
                        ar_perf_period P
                        ON
                                  P.USER_ID        = U.ID
                                  AND P.PERIOD_NUM = PRM_PERIOD_NUM
    WHERE
              B.PERIOD_NUM          = PRM_PERIOD_NUM
              AND B.ORI_BONUS_QY_BD > 0
    ;
    
    /* 异常处理 */
    DECLARE
    CONTINUE HANDLER FOR SQLSTATE '02000'
    SET Done = 1;
    /* 打开游标 */
    OPEN rs;
    DELETE
    FROM
           ek_temp_gl_cs
    ;
    
    /* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
    FETCH NEXT
    FROM
          rs
    INTO
          R1_USER_NAME
        , R1_REAL_NAME
        , R1_ORI_BONUS_QY_BD
        , R1_PARENT_USER_NAME
        , R1_PARENT_REAL_NAME
    ;
    
    /* 遍历数据表 */
    REPEAT
        SET JIJIANGDAISHU = 1;
        SET JICENGDAISHU  = 1;
        SET SHIJIDAISHU   = 0;-- INSERT INTO ORI_BONUS_GX_CS(USER_NAME,REAL_NAME,PARENT_NAME,ORI_BONUS_GX) VALUES (USER_NAME,REAL_NAME,PARENT_NAME,ORI_BONUS_GX);
        REPEAT
            SET R2_USER_NAME = NULL;
            SELECT
                      A.USER_NAME
                    , A.REAL_NAME
                    , A.LEVEL_NAME
                    , A.ORI_BONUS_QY_BD
                    , A.PARENT_USER_NAME
                    , A.PARENT_REAL_NAME
                    , A.PARENT_LEVEL_NAME
                    , A.BDYJ
                    , A.TUIGUANGRENSHU
                    , G.`实际代数`
            INTO
                      R2_USER_NAME
                    , R2_REAL_NAME
                    , R1_USER_LEVEL
                    , R2_ORI_BONUS_QY_BD
                    , R2_PARENT_USER_NAME
                    , R2_PARENT_REAL_NAME
                    , R1_PARENT_LEVEL
                    , R2_BDYJ
                    , TUIGUANGRENSHU
                    , DAISHUXIANZHI
            FROM
                      (
                                SELECT
                                          U.USER_NAME
                                        , U.REAL_NAME
                                        , D.LEVEL_NAME
                                        , IFNULL( B.ORI_BONUS_QY_BD, 0 )                     AS ORI_BONUS_QY_BD
                                        , U2.USER_NAME                                       AS PARENT_USER_NAME
                                        , U2.REAL_NAME                                       AS PARENT_REAL_NAME
                                        , D2.LEVEL_NAME                                      AS PARENT_LEVEL_NAME
                                        ,( XIAOYEJI ( R1_PARENT_USER_NAME, PRM_PERIOD_NUM )) AS BDYJ
                                        , (
                                                 SELECT
                                                        COUNT(*)
                                                 FROM
                                                        zr_user_vw R
                                                 WHERE
                                                        R.`推荐编号` = U.USER_NAME
                                          )
                                          AS TUIGUANGRENSHU
                                FROM
                                          ar_user U
                                          LEFT JOIN
                                                    ar_calc_bonus B
                                                    ON
                                                              B.USER_ID        = U.ID
                                                              AND B.PERIOD_NUM = PRM_PERIOD_NUM
                                          LEFT JOIN
                                                    ar_declaration_level D
                                                    ON
                                                              D.ID = U.DEC_LV
                                          LEFT JOIN
                                                    ar_user_relation_new R
                                                    ON
                                                              R.USER_ID = U.ID
                                          LEFT JOIN
                                                    ar_user U2
                                                    ON
                                                              U2.ID = R.PARENT_UID
                                          LEFT JOIN
                                                    ar_declaration_level D2
                                                    ON
                                                              D2.ID = U2.DEC_LV
                                          LEFT JOIN
                                                    ar_perf_period P
                                                    ON
                                                              P.USER_ID        = U.ID
                                                              AND P.PERIOD_NUM = PRM_PERIOD_NUM
                                WHERE
                                          U.USER_NAME = R1_PARENT_USER_NAME
                      )
                      A
                      LEFT JOIN
                                ek_temp_glds G
                                ON
                                          G.`会员级别`     = A.LEVEL_NAME
                                          AND G.`推广人数` =
                                          IF
                                          (
                                                    A.TUIGUANGRENSHU > 3, 3, A.TUIGUANGRENSHU
                                          )
            ;
            
            SET Done        = 0;
            SET SHIJIDAISHU = SHIJIDAISHU + 1;-- SET SHANGDAI = SHANGDAI + 1;
            IF R2_USER_NAME IS NOT NULL THEN
                SET JIANGJINBILI     = 0;
                SET XIANAJIANGJIN    = 0;
                IF JIJIANGDAISHU    <= DAISHUXIANZHI THEN
                    IF JIJIANGDAISHU = 1
                        OR
                        JIJIANGDAISHU = 3
                        OR
                        JIJIANGDAISHU    = 5 THEN
                        SET JIANGJINBILI = 0.05;
                    ELSEIF
                        JIJIANGDAISHU = 7
                        OR
                        JIJIANGDAISHU = 9
                        OR
                        JIJIANGDAISHU    = 11 THEN
                        SET JIANGJINBILI = 0.04;
                    ELSEIF
                        JIJIANGDAISHU = 13
                        OR
                        JIJIANGDAISHU = 15
                        OR
                        JIJIANGDAISHU    = 17 THEN
                        SET JIANGJINBILI = 0.03;
                    ELSE
                        SET JIANGJINBILI = 0;
                    END IF;
                    SET XIANAJIANGJIN = R1_ORI_BONUS_QY_BD * JIANGJINBILI;
                END IF;
                IF R2_ORI_BONUS_QY_BD > 0 THEN
                    SET JICENGDAISHU  = JICENGDAISHU + 1;
                END IF;
                INSERT INTO ek_temp_gl_cs
                       ( `得奖人编号`
                            , `得奖人姓名`
                            , `得奖人级别`
                            , `给奖人编号`
                            , `给奖人姓名`
                            , `实际代数`
                            , `计层代数`
                            , `计奖代数`
                            , `给奖人报团奖`
                            , `得奖人报团奖`
                            , `得奖人小业绩`
                            , `奖金`
                            , `推广人数`
                            , `代数限制`
                            , `奖金比例`
                       )
                       VALUES
                       ( R2_USER_NAME
                            , R2_REAL_NAME
                            , R1_USER_LEVEL
                            , R1_USER_NAME
                            , R1_REAL_NAME
                            , SHIJIDAISHU
                            , JICENGDAISHU
                            , JIJIANGDAISHU
                            , R1_ORI_BONUS_QY_BD
                            , R2_ORI_BONUS_QY_BD
                            , R2_BDYJ
                            , if(r2_bdyj <980,0,XIANAJIANGJIN)
                            , TUIGUANGRENSHU
                            , DAISHUXIANZHI
                            , JIANGJINBILI
                       )
                ;
                
                SET JIJIANGDAISHU = JICENGDAISHU;
            END IF;
            IF R2_BDYJ < 980 THEN
                SELECT
                       U.`推荐编号`
                INTO
                       R3_PARENT_USER_NAME
                FROM
                       zr_user_vw U
                WHERE
                       U.`会员编号` = R2_USER_NAME
                ;
                
                REPEAT
                    SELECT
                              U.USER_NAME
                            , U.REAL_NAME
                            , U2.USER_NAME                               AS PARENT_USER_NAME
                            , U2.REAL_NAME                               AS PARENT_REAL_NAME
                            ,( XIAOYEJI ( U.USER_NAME, PRM_PERIOD_NUM )) AS BDYJ
                    INTO
                              R3_USER_NAME
                            , R3_REAL_NAME
                            , R3_PARENT_USER_NAME
                            , R3_PARENT_REAL_NAME
                            , R3_BDYJ
                    FROM
                              ar_user U
                              LEFT JOIN
                                        ar_user_relation_new R
                                        ON
                                                  R.USER_ID = U.ID
                              LEFT JOIN
                                        ar_user U2
                                        ON
                                                  U2.ID = R.PARENT_UID
                              LEFT JOIN
                                        ar_perf_period P
                                        ON
                                                  P.USER_ID        = U.ID
                                                  AND P.PERIOD_NUM = PRM_PERIOD_NUM
                    WHERE
                              U.USER_NAME = R3_PARENT_USER_NAME
                    ;
                    
                    INSERT INTO ek_temp_gl_cs
                           ( `得奖人编号`
                                , `得奖人姓名`
                                , `得奖人级别`
                                , `给奖人编号`
                                , `给奖人姓名`
                                , `实际代数`
                                , `计层代数`
                                , `计奖代数`
                                , `给奖人报团奖`
                                , `得奖人报团奖`
                                , `得奖人小业绩`
                                , `奖金`
                                , `推广人数`
                                , `奖金比例`
                                , `是否紧缩980`
                           )
                           VALUES
                           ( R3_USER_NAME
                                , R3_REAL_NAME
                                , NULL
                                , R1_USER_NAME
                                , R1_REAL_NAME
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , R3_BDYJ
                                , if(r3_bdyj >=980 ,XIANAJIANGJIN,0)
                                , NULL
                                , NULL
                                , 1
                           )
                    ;
                    
                    UNTIL R3_BDYJ >= 980
                    OR
                    R3_PARENT_USER_NAME IS NULL
                END REPEAT;
            END IF;
            SET R1_PARENT_USER_NAME = R2_PARENT_USER_NAME;
            UNTIL JIJIANGDAISHU    >= 18
            OR
            R2_USER_NAME IS NULL
        END REPEAT;
        FETCH NEXT
        FROM
              rs
        INTO
              R1_USER_NAME
            , R1_REAL_NAME
            , R1_ORI_BONUS_QY_BD
            , R1_PARENT_USER_NAME
            , R1_PARENT_REAL_NAME
        ;
        
        UNTIL Done
    END REPEAT;
    /* 关闭游标 */
    CLOSE rs;
END