TJ_LowPrize_2012

USE [prise];
GO
SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE [dbo].[TJ_LowPrize_2012]
  @al_orno INT, --订单号
               @as_ym CHAR(7), @as_ctym CHAR(7), --重提日期
                                                @ai_cttimes INT, --@al_return int, --
                                                                @as_info nVARCHAR(500) OUT --返回信息
AS
  BEGIN
    --定单号，营销代表工号

    --long ll_CrewId
    DECLARE
      @ls_CrewId   VARCHAR(50),
      @gs_username nVARCHAR(50)
    --提奖日期
    DECLARE
      @ls_PrizeDate VARCHAR(10),
      @ls_gs        nVARCHAR(1000),
      @ls_gs2       nVARCHAR(1000)

    DECLARE
      @ls_type     nVARCHAR(10),
      @ls_SendDate VARCHAR(10) --设备类型,发货日期
                               --回款金额,回款金额(用于计算回款是否超过60%,提取保底佣金[08年08月01日生效]),合同总额,销售奖金,所有奖金,保底佣金
    DECLARE
      @ldb_BackSum          NUMERIC(18, 4),
      @ldb_BackBD1          NUMERIC(18, 4),
      @ldb_BackBD2          NUMERIC(18, 4),
      @ldb_OrderSum         NUMERIC(18, 4),
      @ldb_SoldPrize        NUMERIC(18, 4),
      @ldb_PrizeSum         NUMERIC(18, 4),
      @ldb_PrizeSum_manager NUMERIC(18, 4),
      @ldb_LowPrize         NUMERIC(18, 4),
      @ldb_OrderPara        NUMERIC(18, 4),
      @ldb_Ratio            NUMERIC(18, 4),
      @ldb_RatioNew         NUMERIC(18, 4),
      @temp_al_prize        NUMERIC(18, 4) --temp_al_prize，由于分公司经理保底佣金计算错误，误用al_prize进行计算，所以用temp_al_prize保存原值。
                                           --产品规格型号
    DECLARE
      @ls_ProductId  VARCHAR(10),
      @ls_OrderLevel VARCHAR(10)
    --产品质量问题数目
    DECLARE
      @li_ProQuaNum  INT,
      @li_PayFlag    INT,
      @li_Comp       INT,
      @li_rtnd       INT,
      @li_rtn        INT,
      @li_corp       INT,
      @li_priceprize INT,
      @li_jxs        INT

    DECLARE
      @li_yjhx INT,
      @li_jsb  INT,
      @li_yj   INT --以旧换新,旧设备,优价产品
    DECLARE
      @li_mort_years INT,
      @li_smbg       INT,
      @li_zdfxht     INT

    DECLARE @li_yhfd INT --是否银行已放贷

    --取得当前的时间
    DECLARE @ls_ErrType nVARCHAR(100)
    DECLARE
      @ldb_Diff     NUMERIC(18, 4),
      @ldb_plan_sum NUMERIC(18, 4),
      @ldb_prize_f  NUMERIC(18, 4),
      @ldb_Back     NUMERIC(18, 4),
      @ldb_xzjbc    NUMERIC(18, 4)
    --抵款额,正常保护价,赠送配件,优惠券,业务费,不管配金额,选装件补差
    DECLARE
      @ldb_dkr   NUMERIC(18, 4),
      @ldb_zcbhj NUMERIC(18, 4), ---??? hjt 2012-05-17
      @ldb_zs    NUMERIC(18, 4),
      @ldb_yhq   NUMERIC(18, 4),
      @ldb_zk    NUMERIC(18, 4),
      @ldb_nopp  NUMERIC(18, 4)

    DECLARE
      @ldt_BackDate DATETIME,
      @ldt_senddate DATETIME,
      @ldt_pdat     DATETIME,
      @ldt_temp     DATETIME

    DECLARE @ld_temp DATETIME
    DECLARE
      @ll_company  INT,
      @ll_province INT

    DECLARE @al_prize_manager NUMERIC(18, 4)
    DECLARE @ls_gs_manager nVARCHAR(1000)
    DECLARE @al_prize NUMERIC(18, 4)
    --@ld_temp=CONVERT(datetime,@as_ym+'-01',120)

    SET @ldb_Diff      = 0
    SET @ld_temp       = CONVERT(DATETIME, @as_ym + '-01', 120)
    SET @ldt_temp      = CONVERT(DATETIME, @as_ym + '-01', 120)
    SET @ldb_OrderPara = 1
    SET @as_info       = ''
    SET @al_prize      = 0

    DECLARE @li_ct INT
    SET @li_ct         = 0

    IF ISNULL(@as_ctym, '') = '' --重提日期为空为正常提奖
      BEGIN
        SET @as_ctym = @as_ym
        SET @li_ct   = 0
      END
    ELSE
      --为重提
      BEGIN
        SET @ls_gs = ''
        SET @li_ct = 1
      END

    SET @ls_gs         = '(TJ2012)'

    --清除提奖记录
    --清除该订单该奖项该月提奖结果表中的计算结果
    IF @li_ct = 0 --重提的已经在重提前一次性删除所有提奖月的提奖结果，不需要再删除
      BEGIN
        DELETE FROM tbl_priseresult
        WHERE       t_orno = @al_orno AND t_priz = 5 AND t_yymm = @as_ym
      END

    --获取定单信息
    DECLARE
      @li_zfz    INT,
      @li_zzz    INT,
      @li_exch   INT,
      @li_lsl    INT,
      @li_080801 INT,
      @li_tfz    INT --li_tfz 提法制的保底佣金
    DECLARE
      @ls_zfz char(7),
      @ls_zzz CHAR(7)

    DECLARE @ldb_bybcrl NUMERIC(18, 4)
    SET @li_exch       = 0
    SELECT @li_080801     = DATEDIFF(DAY, '2008-08-01', t_sdat),
           @li_lsl        = ISNULL(t_lls, 0),
           @li_exch       = ISNULL(t_exch, 0),
           @li_zfz        = 0,
           @ls_zfz        = ISNULL(CONVERT(CHAR(7), t_cldat, 121), '2050-01'),
           @ls_zzz        = ISNULL(CONVERT(CHAR(7), t_zzsj, 121), '2050-01'),
           @li_zzz        = 0,
           @li_PayFlag    = t_pamd,
           @ls_ProductId  = t_item,
           @ls_OrderLevel = t_grad,
           @ldb_OrderSum  = t_amnt,
           @ls_CrewId     = t_emno,
           @ldt_senddate  = t_sdat,
           @li_rtnd       = t_rtnd,
           @li_rtn        = t_rtn,
           @li_corp       = t_org,
           @li_jxs        = t_jxs
    --into @li_080801,@li_lsl,@li_exch,@li_zfz,@ls_zfz,@ls_zzz,@li_zzz,@li_PayFlag,@ls_ProductId,@ls_OrderLevel,
    --@ldb_OrderSum,@ls_CrewId ,@ldt_senddate,@li_rtnd,@li_rtn,@li_corp,@li_jxs,@ll_company
    FROM v_sls
    WHERE t_orno = @al_orno

    SELECT @ldb_bybcrl    = ISNULL(t_bybcrl, 0),
           @ldb_zk        = ISNULL(t_zjf, 0),
           @li_yjhx       = isnull(t_yjhx, 0),
           @ldb_dkr       = isnull(t_dkr, 0),
           @li_jsb        = isnull(t_jsb, 0),
           @li_yj         = isnull(t_yj, 0),
           @ldb_zs        = ISNULL(t_prst, 0),
           @ldb_yhq       = ISNULL(t_yhq, 0),
           @ldb_nopp      = ISNULL(t_nopp, 0),
           @li_zfz        = isnull(t_cl, 0),
           @ls_zfz        = ISNULL(CONVERT(CHAR(7), t_cldat, 121), '2050-01'),
           @ls_zzz        = ISNULL(CONVERT(CHAR(7), t_zzsj, 121), '2050-01'),
           @li_zzz        = isnull(t_zzj, 0),
           @ldb_xzjbc     = isnull(t_xzjbc, 0),
           @li_yhfd       = ISNULL(t_yhfd, 0),
           @li_smbg       = ISNULL(t_smbg, 0),
           @li_zdfxht     = ISNULL(t_zdfxht, 0)
    --into @ldb_bybcrl,@ldb_zk,@li_yjhx,@ldb_dkr,@li_jsb, @li_yj, @ldb_zs, @ldb_yhq, @ldb_nopp,@li_zfz,
    --@ls_zfz,@ls_zzz,@li_zzz,@ldb_xzjbc,@li_yhfd,@li_smbg,@li_zdfxht
    FROM tbl_sls
    WHERE t_orno = @al_orno

    IF ISNULL(@ldb_bybcrl, 0) = 0
      BEGIN
        SET @ldb_bybcrl = 0
      END

    --判断保底佣金是否已经提取
    SELECT @ldb_Ratio     = SUM(t_ratio) -- into @ldb_Ratio
    FROM tbl_priseresult
    WHERE t_orno = @al_orno AND t_priz = 5 AND ISNULL(t_adjust, 0) <> 1 AND ISNULL(t_cttimes, 0) = @ai_cttimes

    IF ISNULL(@ldb_Ratio, 0) = 0
      BEGIN
        SET @ldb_Ratio = 0
      END

    IF @ldb_Ratio >= 1
      BEGIN
        SET @as_info    = '保底佣金已提'
        SET @ls_ErrType = '一般提示'

        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      (@ls_ErrType, @al_orno, @as_ym, 5, @as_info);

        SET @al_prize   = 0
        SELECT @as_info AS info, 0 AS rtn, 0 priz
        RETURN 0
      END

    --select t_amnt into @ldb_zk from tbl_discount where t_orno=@al_orno;
    IF ISNULL(@ldb_zk, 0) = 0
      BEGIN
        SET @ldb_zk = 0
      END

    IF ISNULL(@ldb_zs, 0) = 0
      BEGIN
        SET @ldb_zs = 0
      END

    IF ISNULL(@ldb_yhq, 0) = 0
      BEGIN
        SET @ldb_yhq = 0
      END

    IF ISNULL(@ldb_nopp, 0) = 0
      BEGIN
        SET @ldb_nopp = 0
      END

    IF ISNULL(@ldb_xzjbc, 0) = 0
      BEGIN
        SET @ldb_xzjbc = 0
      END

    IF ISNULL(@ldb_dkr, 0) = 0
      BEGIN
        SET @ldb_dkr = 0
      END

    --换房换物不提保底佣金
    IF ISNULL(@li_exch, 0) = 0
      BEGIN
        SET @li_exch = 0
      END

    IF @li_smbg <> 1
      BEGIN
        IF @li_exch = 1
          BEGIN
            SET @as_info = '换房换物抵款不提保底佣金'
            RETURN 0
          END

        IF @li_zdfxht = 1
          BEGIN
            SET @as_info = '重大风险合同不提保底佣金'
            RETURN 0
          END
      END

    IF @ls_zfz > @as_ym
      begin
        SET @li_zfz = 0
      end
    else
      begin
        SET @li_zfz = 1
      end

    IF @ls_zzz > @as_ym
      BEGIN
        SET @li_zzz = 0
      END
    else
      begin
        SET @li_zzz = 1
      end

    --取得合同金额
    SELECT @ldb_ordersum  = SUM(t_amnt) -- into @ldb_ordersum
    FROM tbl_backorder
    WHERE t_orno = @al_orno

    --取得保底佣金金额
    SELECT            TOP 1
                      @al_prize      = isnull(t_lpri,0),
                      @ls_type       = t_type --into @al_prize, @ls_type
    FROM              tbl_proprize
    WHERE             t_item = @ls_ProductId AND t_udat <= @ldt_SendDate
    ORDER BY          t_udat DESC
    SELECT @al_prize      = isnull(@al_prize, 0)

    --取得保护价基数@生效日期在发货日之前的最近一个
    SELECT            TOP 1
                      @ldb_zcbhj     = ISNULL(t_ppri, 0)
    FROM              tbl_proprice
    WHERE             t_item = @ls_ProductId AND t_udat <= @ldt_SendDate
    ORDER BY          t_udat DESC

    SELECT @ldb_zcbhj     = ISNULL(@ldb_zcbhj, 0)

    IF @al_prize = 0
      BEGIN
        SET @as_info = '保底佣金参数为空'
        --al_prize=40000 --add by wangq11 2010-09-03
         SELECT @as_info AS info, 0 AS rtn, 0 priz
        RETURN 0
      END

    IF @li_lsl = 1
      BEGIN
        SET @al_prize = @al_prize / 2
        SET @ls_gs    = @ls_gs + ',力士乐主油泵。'
      END

    --从回款信息表中取得截止提奖月的实际回款金额
    SELECT @ldb_Back      = ISNULL(SUM(t_rtmn), 0) -- into @ldb_Back
    FROM tbl_retamn
    WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym AND ISNULL(t_yhfd, 0) = 0

    SELECT @ldb_back      = ISNULL(@ldb_back, 0)

    IF NOT (@li_zfz = 1 OR @li_zzz = 1 OR @li_smbg = 1) --如已转专制（法制）或有书面报告，则不再需要判断以下的条件
      BEGIN
        IF @li_PayFlag = 2 OR @li_PayFlag = 3 OR @li_PayFlag = 4 OR @li_PayFlag = 5 OR @li_PayFlag = 7 --按揭订单
          BEGIN
            --手续是否办完
            DECLARE @li_MortFlag INT
            DECLARE
              @ldb_BackPay    NUMERIC(18, 4),
              @ldb_BackPaysum NUMERIC(18, 4)

            DECLARE @ls_pdat VARCHAR(7) --    办理按揭时间
            SELECT @li_MortFlag = t_ppas,
                   @ls_pdat     = isnull(CONVERT(CHAR(7), t_pdat, 121), '2050-01')
            FROM tbl_sls
            WHERE t_orno = @al_orno

            IF @li_smbg <> 1
              BEGIN
                IF @ls_pdat > @as_ym
                  BEGIN
                    SET @as_info    = '未办完按揭手续，不能提取保底佣金'
                    SET @ls_ErrType = '一般提示'

                    INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
                    VALUES      (@ls_ErrType, @al_orno, @as_ym, 3, @as_info)

                   

                    SET @al_prize   = 0
                    SELECT @as_info AS info, 0 AS rtn, 0 priz
                    RETURN 0
                  END
              END

            --未还清银行贷款
            SET @ldb_BackPay = 0
            --SELECT @ldb_BackPay=isnull(@ldb_BackPaysum,0)
            SELECT          @ldb_BackPay = SUM(t_amnt)
            FROM            tbl_backorder
            WHERE           t_orno = @al_orno AND ISNULL(convert(char(7),t_hdrq,121), '2050-01') > @as_ym AND (t_cash <> 1 AND t_first <> 1)
            GROUP BY        t_orno
            --if isnull(ldb_BackPay) then ldb_BackPay=0
            SELECT @ldb_BackPay = ISNULL(@ldb_BackPay, 0)

            --20091230银行未放贷，只维护了首付，系统会误判为贷款已收回。
            IF @li_smbg <> 1
              BEGIN
                IF @ldb_BackPay > 0 OR (@ldb_BackPay = 0 AND @li_yhfd = 0)
                  BEGIN
                    SET @as_info    = '客户贷款未还完，不能提取保佣金.'
                    SET @ls_ErrType = '一般提示'

                    INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
                    VALUES      (@ls_ErrType, @al_orno, @as_ym, 5, @as_info)

                    SELECT @as_info AS info, 0 AS rtn, 0 priz

                    SET @al_prize   = 0
                    RETURN 0
                  END
              END
          END
        ELSE
          --分期订单
          BEGIN
            --货款全部收回
            SELECT @ldb_BackSum = SUM(t_rtmn) --into @ldb_BackSum
            FROM tbl_retamn
            WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym AND ISNULL(t_cash, 0) = 1

            SELECT @ldb_BackSum = ISNULL(@ldb_BackSum, 0)

            IF @li_smbg <> 1
              BEGIN
                IF @ldb_BackSum < @ldb_OrderSum
                  BEGIN
                    --messagebox('ldb_BackSum@'+string(ldb_BackSum),'ldb_OrderSum@'+string(ldb_OrderSum))
                    SET @as_info    = '货款还没有全部收回，不能提取保底佣金'
                    SET @ls_ErrType = '操作错误'

                    INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
                    VALUES      (@ls_ErrType, @al_orno, @as_ym, 5, @as_info);

                    --commit;
                    SET @al_prize   = 0
                    RETURN -1
                  END
              END
          END
      END --非转专制（法制）或没有书面报告时，需要判断以上的条件

    --计算该定单所提取的所有奖项
    SELECT @ldb_PrizeSum  = SUM(t_amnt) --into @ldb_PrizeSum
    FROM tbl_priseresult
    WHERE t_orno = @al_orno AND t_priz <> 1

    IF ISNULL(@ldb_PrizeSum, 0) = 0
      BEGIN
        SET @ldb_PrizeSum = 0
      END

    IF @li_jxs = 1
      BEGIN
        SET @al_prize = @al_prize / 2
        SET @ls_gs    = @ls_gs + '经过经销商销售，保底佣金减半.'
      END

    --2010-01-01书面报告，保底佣金分两次提
    IF @li_yjhx = 1
      BEGIN
        SET @al_prize   = @al_prize * (@ldb_ordersum - @ldb_dkr) / @ldb_ordersum
        SET @ls_gs      = @ls_gs + '以旧换新，保底佣金=（合同金额(' + STR(@ldb_ordersum) + ')－抵款额(' + STR(
                          @ldb_dkr) + ')）／合同金额(' + STR(@ldb_ordersum) + ')*正常产品保底佣金;'
      END
    ELSE
      BEGIN
        IF @li_yj = 1
          BEGIN
            IF @ldb_zcbhj = 0
              BEGIN
                SET @as_info = '正常产品保护价参数为空'
                RETURN 0
              END

            SET @al_prize      = @al_prize * (@ldb_ordersum - @ldb_zs - @ldb_yhq - @ldb_zk + @ldb_nopp - @ldb_xzjbc - @ldb_bybcrl)
                                 / @ldb_zcbhj

            SET @ls_gs        =  @ls_gs + '优价产品，保底佣金=(实际成交价[合同金额(' + STR(@ldb_ordersum) +
                                 ') - ' + '配件(' + STR(@ldb_zs) + ')-优惠券(' + STR(@ldb_yhq) + ') - 业务费(' + STR(
                                 @ldb_zk) + '+ 不配管金额(' + STR(@ldb_nopp) + ')-选装件补差(' + STR(@ldb_xzjbc) +
                                 ' - 备用泵车让利(' + STR(@ldb_bybcrl) + ')])／正常产品保护价(' + STR(@ldb_zcbhj) +
                                 ')*正常产品保底佣金;'
          END
      END

    IF @ldb_PrizeSum >= @al_prize AND @ldb_PrizeSum > 0
      BEGIN
        SET @as_info  = '其它奖项金额总额(' + STR(@ldb_PrizeSum) + '),已经超过保底佣金，不能提取保底佣金'

        SET @al_prize = 0

        --将提奖标志置为提毕
        UPDATE tbl_sls
        SET    t_flag = 5
        WHERE  t_orno = @al_orno;

        --commit;
        SET @al_prize = 0
        SELECT @as_info AS info, 0 AS rtn, 0 priz
        RETURN 0
      END
    ELSE
      IF @ldb_PrizeSum < 0
        BEGIN
          --没有书面报告就不能提取保底佣金吗？
          IF NOT @li_smbg = 1
            BEGIN
              SET @as_info    = '没有书面报告，不能提取保底佣金'
              SET @ls_ErrType = '一般提示'

              INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
              VALUES      (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);

              --commit;
              SET @al_prize   = 0
              RETURN 0
            END

          SET @ldb_prize_f      = -@ldb_prizeSum --如果已有奖项总额为负，保底佣金冲抵负的这一部分由总部承担
          SET @ldb_PrizeSum     = 0 -- add by wangq11 20100-09-03 如果已有奖项总额为负，-已提奖总额为0
          SET @ls_gs2           = @ls_gs + '已有奖项总额为负，保底佣之冲抵负的这一部分由总部承担.'

          SET @temp_al_prize    = @al_prize
          SET @ls_gs            = @ls_gs + '保底佣金(' + STR(@al_prize - @ldb_PrizeSum) + ')[保底佣金基数(' + STR(@al_prize)
                                  + ') -已提奖总额(' + STR(@ldb_PrizeSum) + ')]'
          --messagebox('al_prize@'+string(al_prize),'ldb_PrizeSum@'+string(ldb_PrizeSum))
          SET @al_prize         = @al_prize - @ldb_PrizeSum
          SET @ls_gs           =  @ls_gs + '保底佣金(' + STR(@temp_al_prize / 2 - @ldb_PrizeSum_manager) +
                                  ')[保底佣金基数(' + STR(@temp_al_prize / 2) + ') -已提奖总额(' + STR(
                                  @ldb_PrizeSum_manager) + ')]'

          SET @al_prize_manager = @temp_al_prize / 2 - @ldb_PrizeSum_manager
        END
      ELSE
        BEGIN
          --没有书面报告就不能提取保底佣金吗？
          IF NOT @li_smbg = 1
            BEGIN
              SET @as_info    = '没有书面报告，不能提取保底佣金'
              SET @ls_ErrType = '一般提示'

              INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
              VALUES      (@ls_ErrType, @al_orno, @as_ym, 3, @as_info)

              --commit;
              SET @al_prize   = 0
              RETURN 0
            END

          IF @li_smbg = 1
            BEGIN
              SELECT @ldb_BackBD1 = ISNULL(SUM(t_rtmn), 0)
              FROM tbl_retamn
              WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym AND t_cash = 1

              SELECT @ldb_BackBD2 = ISNULL(SUM(t_amnt), 0)
              FROM tbl_backorder
              WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_hdrq, 121) <= @as_ym

              IF (@ldb_backBD1 + @ldb_backBD2) / @ldb_ordersum >= 0.6
                BEGIN
                  --大于60%，一次性计提
                  IF @ldb_Ratio = 0
                    BEGIN
                      --
                      SET @ldb_RatioNew     = 1
                      SET @ls_gs            = @ls_gs + '书面报告,保底佣金(' + STR(@al_prize - @ldb_PrizeSum) +
                                              ')[保底佣金基数(' + STR(@al_prize) + ') -已提奖总额(' + STR(@ldb_PrizeSum)
                                              + ')]'

                      SET @al_prize         = @al_prize - @ldb_PrizeSum
                      --分公司经理保底佣金为营销代表的一半
                      SET @ls_gs            = @ls_gs + '书面报告,保底佣金(' + STR(@al_prize / 2 - @ldb_PrizeSum) +
                                              ')[保底佣金基数(' + STR(@al_prize) + ') -已提奖总额(' + STR(@ldb_PrizeSum)
                                              + ')]'

                      SET @al_prize_manager = @al_prize_manager - @ldb_PrizeSum_manager
                      SET @ls_gs            = @ls_gs + ',回款达到60%,一次性计提;'
                    END --end  大于60%，一次性计提
                END
              ELSE
                BEGIN
                  SET @as_info = '回款未达60%,不能计提保底佣金;'
                  RETURN 0
                END
            END
          ELSE
            BEGIN
              SET @temp_al_prize = @al_prize
              SET @ls_gs        =  @ls_gs + '保底佣金(' + STR(@al_prize - @ldb_PrizeSum) + ')[保底佣金基数(' + STR(
                                   @al_prize) + ') -已提奖总额(' + STR(@ldb_PrizeSum) + ')]'

              SET @al_prize      = @al_prize - @ldb_PrizeSum
            -- ls_gs=ls_gs+'保底佣金('+string(temp_al_prize/2 - ldb_PrizeSum_manager,'#')+')[保底佣金基数('+string(temp_al_prize/2,'#')+') -已提奖总额('+string(ldb_PrizeSum_manager)+')]'
            -- al_prize_manager=temp_al_prize/2 - ldb_PrizeSum_manager
            END
        END

    IF @li_smbg <> 1
      BEGIN
        IF @li_exch = 1
          BEGIN
            --将提奖标志置为提毕
            UPDATE tbl_sls
            SET    t_flag = 5
            WHERE  t_orno = @al_orno

            SET @al_prize = 0
            RETURN 0
          END
      END

    --messagebox(string(ldb_Ratio),string(ldb_Ratio))
    --获取提奖分配比例
    DECLARE
      @ll_total     INT,
      @ll_emno22    INT,
      @ll_corp2     INT,
      @ll_province2 INT,
      @ll_company2  INT,
      @ll_orno2     INT,
      @ll           INT

    DECLARE @ls_emno2 VARCHAR(50)
    DECLARE @ldb_pre NUMERIC(18, 4)
    --boolean lb_continue
    DECLARE @lb_continue BIT

    DECLARE
      tbl_assign_Cursor CURSOR FOR
        SELECT fld_emno, fld_pre
        FROM   tbl_assign
        WHERE  tbl_assign.fld_orno = @al_orno AND (@as_ym BETWEEN CONVERT(CHAR(7), ISNULL(fld_udat, '2010-01-01'), 121) AND CONVERT(CHAR(7), ISNULL(fld_ddat, '2055-01-01'), 121)
               )

    SET @ll_total      = 0

    IF @al_prize > 0
      BEGIN
        OPEN tbl_assign_Cursor

        WHILE 1 = 1
        BEGIN
          FETCH tbl_assign_Cursor
          INTO @ls_emno2, @ldb_pre

          IF @@fetch_status <> 0
            BREAK

          SET @ll_total = @ll_total + 1
          SET @ls_gs    = @ls_gs + '提奖分配，获取' + STR(@ldb_pre) + ';'

          --奖提奖结果插入到数据库中
          INSERT INTO tbl_priseresult(t_orno, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_gs, t_gs2, t_user,
                        t_cttimes, t_ratio)
          VALUES      (@al_orno, 5, @al_prize * @ldb_pre, @ldb_Diff, @as_ctym, GETDATE(), @ls_emno2, @ls_gs, @ls_gs, @gs_username,
                       @ai_cttimes, @ldb_RatioNew * @ldb_pre);
        END

        CLOSE tbl_assign_Cursor
        DEALLOCATE tbl_assign_Cursor

        --DESTROY ldts_assign
        IF @ll_total = 0
          BEGIN
            --没有分配系数，照常
            --奖提奖结果插入到数据库中
            INSERT INTO tbl_priseresult(t_orno, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_gs, t_gs2, t_user,
                          t_cttimes, t_ratio)
            VALUES      (@al_orno, 5, @al_prize, @ldb_Diff, @as_ctym, GETDATE(), @ls_CrewId, @ls_gs, @ls_gs, @gs_username,
                         @ai_cttimes, @ldb_RatioNew);
          END
      END

    --将提奖标志置为提毕
    IF @li_smbg <> 1 OR (@li_smbg = 1 AND (@ldb_RatioNew + @ldb_Ratio = 1))
      BEGIN
        UPDATE tbl_sls
        SET    t_flag = 5, t_wbsj = GETDATE()
        WHERE  t_orno = @al_orno
      --commit;
      END

    SET @as_info       = @ls_gs
    RETURN
  END
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/75527/viewspace-730060/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/75527/viewspace-730060/