ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TJ_SellPrize_20120101

TJ_SellPrize_20120101

原创 Linux操作系统 作者:huangjt007 时间:2012-05-03 10:41:06 0 删除 编辑

SET  QUOTED_IDENTIFIER ON
GO
SET  ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[TJ_SellPrize_20120101]
  @al_orno INT, --订单号
               @as_ym CHAR(7), @as_ctym CHAR(7), --重提日期
                                                @ai_cttimes INT, @al_return INT, --
                                                                                @as_info VARCHAR(500) OUT --返回信息
AS
  BEGIN
    DECLARE @li_have_row INT
    DECLARE @li_ct INT
    DECLARE @ls_gs VARCHAR(1000)
    DECLARE
      @ll_yt    INT,
      @li_flag1 INT

    SET @li_flag1        = 0
    DECLARE @al_prize NUMERIC(18, 4)
    DECLARE @al_prize_manager NUMERIC(18, 4)
    DECLARE @ldb_all_return NUMERIC(18, 4)
    DECLARE @ls_gs_manager VARCHAR(1000)
    DECLARE @ls_error VARCHAR(1000)
    DECLARE @ld_month_end DATETIME
    DECLARE
      @ldb_PrizeDiffer NUMERIC(18, 4),
      @ldb_info_rate   NUMERIC(18, 4),
      @ldb_bc_rate     NUMERIC(18, 4)

    DECLARE @gs_userName VARCHAR(50)
    DECLARE
      @ldt_ValDate  DATETIME,
      @ldt_BackDate DATETIME

    --@ldts_assign.getitemstring(ll,'fld_emno')
    --@ldts_assign.getitemnumber(ll,'fld_corp')
    --@ldts_assign.getitemnumber(ll,'fld_province')
    --@ldts_assign.getitemnumber(ll,'fld_company')
    --@ldts_assign.getitemnumber(ll,'fld_pre')
    DECLARE
      tbl_assign_Cursor CURSOR FOR
        SELECT fld_emno, fld_pre, fld_province, fld_company, 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)
               )

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

    SET @ls_gs           = '(tj20120101)'
    SET @as_info         = '(tj20120101)'
    DECLARE @ls_ProdType VARCHAR(1000)
    DECLARE @ls_ErrType VARCHAR(1000)
    DECLARE @ls_PrizeDate VARCHAR(10)
    DECLARE @ls_errorType VARCHAR(10)
    DECLARE @ld_temp VARCHAR(10)
    --清除TJ记录
    --获取TJ月最后一天的日期:因为TJ是针对某一个月,那对于某些
    --需要时间期限的判断,以TJ月最后一天为期限。
    SET @ls_PrizeDate    = @as_ym
    SET @ld_temp         = CONVERT(DATETIME, @as_ym + '-01', 120)

    --set @ld_month_end=CONVERT(datetime, (date(year(ld_temp)+sign(month(@ld_temp) -12)+1,mod(month(@ld_temp)+1,13)+abs(sign(mod(month(@ld_temp)+1,13)) -1),1),)
    --清除该订单该奖项该月TJ结果表中的计算结果
    IF @li_ct = 0 --重提的已经在重提前一次性删除所有TJ月的TJ结果,不需要再删除 ??isnull(t_cttimes,0)=@ai_cttimes该条件是否需要?
      BEGIN
        DELETE FROM tbl_priseresult
        WHERE       t_orno = @al_orno AND (t_priz = 3 OR t_priz = 10) AND t_yymm = @as_ym AND ISNULL(t_cttimes, 0) = @ai_cttimes
      END

    --获取订单信息Begin
    DECLARE @li_zfz INT
    DECLARE
      @li_zzj  INT,
      @li_xxj  INT,
      @li_brdq INT,
      @li_zzzj INT

    DECLARE
      @li_080901 INT,
      @li_xlh    INT,
      @li_lsl    INT,
      @li_aj4    INT

    DECLARE @li_070516 INT
    DECLARE @ldb_dkr NUMERIC(18, 4)
    DECLARE
      @li_mort_years INT,
      @li_mort_per   INT,
      @li_416        INT,
      @ld_getdate    DATETIME,
      @li_hg         INT,
      @li_qk         INT

    DECLARE
      @li_yj        INT,
      @li_PayFlag   INT,
      @li_exch      INT,
      @li_LawFlag   INT,
      @li_zzz       INT,
      @ls_zzz       CHAR(7),
      @ldb_OrderSum NUMERIC(18, 4)

    DECLARE
      @ls_OrderLevel CHAR(5),
      @li_BackFlag   INT,
      @li_CustomType INT,
      @ldt_SendDate  DATETIME,
      @li_CompCode   INT

    DECLARE
      @ls_CrewId    CHAR(20),
      @ll_company   INT,
      @ll_province  INT,
      @li_orderFlag INT,
      @ll_corp      INT,
      @li_jxs       INT,
      @ldb_yxdb     INT,
      @ls_zfz       CHAR(7)

    DECLARE
      @ldb_Accuracy NUMERIC(18, 4),
      @li_jsb       INT,
      @li_yjhx      INT,
      @ldb_zs       NUMERIC(18, 4),
      @ldb_yhq      NUMERIC(18, 4),
      @ldb_nopp     NUMERIC(18, 4),
      @ldb_xzjbc    NUMERIC(18, 4),
      @li_ajqzsk    INT,
      @li_sx        INT

    SELECT @li_zfz          = ISNULL(t_cl, 0),
           @li_zzj          = ISNULL(t_zzj, 0),
           @li_xxj          = ISNULL(t_xxj, 0),
           @li_brdq         = ISNULL(t_brdq, 0),
           @li_zzzj         = ISNULL(t_zzzj, 0),
           @li_080901       = DATEDIFF(DAY, '2008-08-31', t_sdat),
           @li_xlh          = ISNULL(t_xlh, 0),
           @li_lsl          = ISNULL(t_lls, 0),
           @li_aj4          = ISNULL(t_aj4, 0),
           @ldb_dkr         = ISNULL(t_dkr, 0),
           @li_070516       = DATEDIFF(DAY, '2007-05-15', t_sdat),
           @li_mort_per     = t_mort_years,
           @li_mort_per     = t_mort_per,
           @li_416          = DATEDIFF(DAY, '2007-04-12', t_sdat),
           @ld_getdate      = GETDATE(),
           @li_hg           = ISNULL(t_rtn, 0),
           @li_qk           = ISNULL(t_qk, 0),
           @li_yj           = ISNULL(t_yj, 0),
           @li_PayFlag      = ISNULL(t_pamd, 0),
           @li_exch         = ISNULL(t_exch, 0),
           @li_LawFlag      = ISNULL(t_cl, 0),
           @li_zzz          = ISNULL(t_zzj, 0),
           @ls_zzz          = ISNULL(CONVERT(CHAR(7), t_zzsj, 121), '2020-01'),
           @ldb_OrderSum    = isnull(t_amnt, 0),
           @ls_OrderLevel   = ISNULL(t_grad, 'C'),
           @li_BackFlag     = ISNULL(t_rtnd, 0),
           @li_CustomType   = ISNULL(t_cutp, 0),
           @ls_ProdType     = t_item,
           @ldt_SendDate    = t_sdat,
           @ls_CrewId       = t_emno,
           @li_orderFlag    = t_flag,
           @ll_corp         = t_org,
           @li_jxs          = ISNULL(t_jxs, 0),
           @ldb_yxdb        = ISNULL(t_yxdb, 0),
           @ls_zfz          = ISNULL(CONVERT(CHAR(7), t_cldat, 121), '2050-01'),
           @ldb_Accuracy    = ISNULL(t_accuracy, 0),
           @li_jsb          = ISNULL(t_jsb, 0),
           @li_yjhx         = ISNULL(t_yjhx, 0),
           @ldb_zs          = ISNULL(t_prst, 0),
           @ldb_yhq         = ISNULL(t_yhq, 0),
           @ldb_nopp        = ISNULL(t_nopp, 0),
           @ldb_xzjbc       = ISNULL(t_xzjbc, 0),
           @li_ajqzsk       = ISNULL(t_ajqzsk, 0),
           @li_sx           = ISNULL(t_ppas, 0)
    FROM tbl_sls
    WHERE t_orno = @al_orno

    --获取订单信息END
    --订单信息处理BEGIN
    --如果转法制了
    IF @li_LawFlag = 1
      BEGIN
        --如果转法日期大于提奖日期 转法标志从新设置为0
        IF @ls_zfz > @as_ym
          SET @li_LawFlag = 0
      END

    IF @li_zzz = 1
      BEGIN
        --如果转专日期大于提奖日期 转专标志从新设置为0
        IF @ls_zzz > @as_ym
          SET @li_zzz = 0
      END

    IF @li_PayFlag = 6
      SET @li_qk = 1 --根据付未类型确定是否为全款

    --订单信息处理END
    --select @ldb_plan_sum=sum(t_amnt)  from tbl_backorder where t_orno=@al_orno
    IF @li_xlh = 1 --销售新利恒产品,不计销售奖
      BEGIN
        SET @as_info = '销售新利恒产品,不计销售奖!'

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

        RETURN 0
      END

    IF @li_LawFlag = 1
      BEGIN
        SET @as_info = @as_info + '订单转法制,不提销售奖'

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('一般提示', @al_orno, @as_ym, 2, @as_info)

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

        RETURN 0
      END

    --转法制
    IF @li_zzz = 1
      BEGIN
        SET @as_info = @as_info + '订单转专制,不提销售奖!'

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('一般提示', @al_orno, @as_ym, 2, @as_info)

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

        RETURN 0
      END

    --换物
    DECLARE @ldb_exchPara NUMERIC(18, 4)

    IF @li_exch = 1
      BEGIN
        SET @ldb_exchPara = 0.8
      END
    ELSE
      BEGIN
        SET @ldb_exchPara = 1
      END

    DECLARE
      @ldb_info       NUMERIC(18, 4),
      @ldb_sale_per   NUMERIC(18, 4),
      @ldb_BasicPrize NUMERIC(18, 4),
      @ldb_LowPrize   NUMERIC(18, 4)

    DECLARE
      @ldb_LevelA  NUMERIC(18, 4),
      @ldb_LevelA1 NUMERIC(18, 4),
      @ldb_LevelB  NUMERIC(18, 4),
      @ldb_LevelC  NUMERIC(18, 4)

    DECLARE
      @ldb_LevelC1 NUMERIC(18, 4),
      @ldb_LevelD  NUMERIC(18, 4),
      @ldb_a_per   NUMERIC(18, 4),
      @ldb_b_per   NUMERIC(18, 4)

    DECLARE
      @ldb_c_per  NUMERIC(18, 4),
      @ldb_d_per  NUMERIC(18, 4),
      @ls_type    CHAR(20),
      @ldb_sale_a NUMERIC(18, 4)

    DECLARE
      @ldb_sale_b  NUMERIC(18, 4),
      @ldb_fsyzjxs NUMERIC(18, 4)

    ----获取提奖参数
    SET @li_have_row     = 0
    SELECT             TOP 1
                       @li_have_row     = 1,
                       @ldb_info        = ISNULL(t_info, 0),
                       @ldb_sale_per    = ISNULL(t_jbc_xsjbl, 0.008),
                       @ldb_BasicPrize  = ISNULL(t_sale, 0),
                       @ldb_LowPrize    = ISNULL(t_lpri, 0),
                       @ldb_LevelA      = ISNULL(t_a, 0),
                       @ldb_LevelA1     = ISNULL(t_a1, 0),
                       @ldb_LevelB      = ISNULL(t_b, 0),
                       @ldb_LevelC      = ISNULL(t_c, 0),
                       @ldb_LevelC1     = ISNULL(t_c1, 0),
                       @ldb_LevelD      = ISNULL(t_d, 0),
                       @ldb_a_per       = ISNULL(t_a_per, 0),
                       @ldb_b_per       = ISNULL(t_b_per, 0),
                       @ldb_c_per       = ISNULL(t_c_per, 0),
                       @ldb_d_per       = ISNULL(t_d_per, 0),
                       @ls_type         = t_type,
                       @ldb_sale_a      = ISNULL(t_sale_a, 0),
                       @ldb_sale_b      = ISNULL(t_sale_b, 0),
                       @ldb_fsyzjxs     = ISNULL(t_fsyzjxs, 0)
    FROM               tbl_proprize
    WHERE              t_item = @ls_ProdType AND t_udat <= @ldt_SendDate
    ORDER BY           t_udat DESC;

    IF @li_have_row = 0
      BEGIN
        SET @as_info = @as_info + '获取提奖参数失败!'

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('错误', @al_orno, @as_ym, 2, @as_info)

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

        RETURN 0
      END

    DECLARE @ldb_ppri NUMERIC(18, 4)
    ----获取提奖参数
    SET @li_have_row     = 0
    SET @ldb_ppri        = 0
    SELECT            TOP 1
                      @ldb_ppri        = t_ppri,
                      @li_have_row     = 1
    FROM              tbl_proprice
    WHERE             t_item = @ls_ProdType AND t_udat <= @ldt_SendDate
    ORDER BY          t_udat DESC

    IF @li_have_row = 0
      BEGIN
        SET @as_info = @as_info + '获取提奖价格参数失败!'

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('错误', @al_orno, @as_ym, 2, @as_info)

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

        RETURN 0
      END

    --取得所有回款计划的总金额,该金额做为实际的合同金额
    DECLARE @ldb_plan_sum NUMERIC(18, 4)
    SELECT @ldb_plan_sum    = SUM(t_amnt)
    FROM tbl_backorder
    WHERE t_orno = @al_orno

    IF ISNULL(@ldb_plan_sum, 0) <= 0
      BEGIN
        SET @as_info = @as_info + '回款计划为零!'

        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('数据错误', @al_orno, @as_ym, 2, @as_info)

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

        RETURN
      END

    DECLARE @ldb_amnt NUMERIC(18, 4)
    DECLARE @ldb_Back NUMERIC(18, 4)
    --将回款计划做为总的合同金额
    SET @ldb_ordersum    = @ldb_plan_sum
    SET @ldb_amnt        = @ldb_ordersum
    --从回款信息表中取得截止TJ月的实际回款金额
    SELECT @ldb_Back        = ISNULL(SUM(t_rtmn), 0)
    FROM tbl_retamn
    WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym AND isnull(t_cash, 0) = 1

    IF @ldb_back IS NULL
      SET @ldb_back = 0

    -----
    DECLARE @ldb_lpri NUMERIC(18, 4)

    IF isnull(@ldb_fsyzjxs, 0) = 0
      SET @ldb_fsyzjxs = 0.5 --搅拌站非三一主机系数设定为0.5 2011

    SET @ls_type         = RTRIM(LTRIM(@ls_type))
    SET @ldb_lpri        = @ldb_LowPrize --保底佣金
                                         --2008-06-17  力士乐主油泵保底佣金减半

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

    --2009.01.01换房换物抵款合同,保底佣金设为0
    IF @li_exch = 1
      SET @ldb_lpri = 0

    DECLARE @ls_sdat CHAR(10)
    DECLARE @ldb_zk NUMERIC(18, 4)
    DECLARE @ldb_cjj NUMERIC(18, 4)
    DECLARE @ldb_yjpara NUMERIC(18, 4)
    SELECT @ls_sdat         = CONVERT(CHAR(10), @ldt_senddate, 121)

    IF @li_yj = 1 -- --优价
      BEGIN
        SET @ldb_cjj    = @ldb_amnt - (@ldb_zs + @ldb_yhq) * 0.5 - @ldb_zk - @ldb_xzjbc --计算出实际价格(可能是多台的总价) add by wangq11 2010-07-21 ,2010-08-07 增加优价机赠送配件*0.5
        SET @ldb_yjpara = @ldb_cjj / @ldb_ppri
        SET @ls_gs      = @ls_gs + '优价机,合同金额(' + STR(@ldb_cjj) + ')/保护价(' + STR(@ldb_ppri) + ')=' + STR(
                          @ldb_cjj / @ldb_ppri) + ','
      END
    ELSE
      BEGIN
        SET @ldb_yjpara = 1
      END

    --地区系数 2011 直接取自订单
    --地区系数
    DECLARE @ldb_AreaPara NUMERIC(18, 4)

    IF @li_brdq = 1
      SET @ldb_AreaPara = 1.2
    ELSE
      SET @ldb_AreaPara = 1

    --取得是否已经提过销售奖的标志
    DECLARE @ll_sale_flag INT
    DECLARE @ls_have_ym CHAR(7)
    DECLARE @sale_count_sjc NUMERIC(18, 4) --销售奖已提比率
    SET @ll_sale_flag    = 0
    SET @ls_have_ym      = ''
    SELECT TOP 1
           @ll_sale_flag    = t_orno,
           @ls_have_ym      = t_yymm
    FROM tbl_priseresult
    WHERE t_priz = 3 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes

    IF @ll_sale_flag IS NULL
      SET @ll_sale_flag = 0

    SELECT @sale_count_sjc  = SUM(ISNULL(t_ratio, 0))
    FROM tbl_priseresult
    WHERE t_priz = 3 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;

    IF @sale_count_sjc IS NULL
      SET @sale_count_sjc = 0

    IF @sale_count_sjc >= 1
      BEGIN
        SET @as_info = @as_info + '销售奖已于' + @ls_have_ym + '被提取!'

        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      ('一般提示', @al_orno, @as_ym, 2, @as_info)

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

        RETURN
      END

    --计算该定单所提取的所有奖项
    DECLARE @ldb_PrizeSum NUMERIC(18, 4)
    DECLARE @ldb_cjsf NUMERIC(18, 4)
    DECLARE @ldb_qkbl NUMERIC(18, 4)
    DECLARE @lb_zw BIT --占位变量,在需要有个语句的地方用set @lb_zw=1
                       --declare @ldb_BasicPrize numeric(18,4)
    SELECT @ldb_PrizeSum    = SUM(t_amnt)
    FROM tbl_priseresult
    WHERE t_orno = @al_orno AND t_priz <> 1 AND t_crep <> 1000000

    IF @ldb_PrizeSum IS NULL
      SET @ldb_PrizeSum = 0

    ------根据产品类型进行不同的处理--------------------
    IF @ls_type IN ('泵车', '拖泵', '车载泵', '布料机', '布料杆')
      BEGIN
        --set @lb_zw=1  --语法占位
        SET @ldb_cjsf = 0.5
        SET @ldb_qkbl = 0.9

        --预提S
        IF @ll_yt = 0
          begin
            IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
              --DATEDIFF
              begin
                IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
                  SET @as_info = @as_info + '发货后没有超过30天,不予TJ'

                -- set @ls_errorType='一般提示'
                INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
                VALUES      ('一般提示', @al_orno, @as_ym, 3, @as_info)

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

                RETURN 0
              end
          end

        --如果是经销商销售的,不提销售奖
        IF @li_jxs = 1
          --set @ls_ErrType='一般提示'
          begin
            SET @as_info = @as_info + '二级经销商销售,不提销售奖!'

            --set @as_info=@as_info+@ls_ErrType+':'+@ls_error
            --将错误信息写入系统日志表中
            INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
            VALUES      ('一般提示', @al_orno, @as_ym, 2, @ls_error)

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

            RETURN 0
          end
      END
    ELSE
      IF @ls_type IN ('搅拌车')
        BEGIN
          SET @lb_zw          = 1 --语法占位
                                  --2008年开始按销售价的百分比提取
------2012-5-2
          IF @ldb_sale_per < 0 OR @ldb_sale_per IS NULL
            SET @ldb_sale_per = 0.008

          IF @li_zzzj = 1
            --三一底盘搅拌车
            -- begin
            IF @li_PayFlag = 1 OR @li_PayFlag = 5
              SET @ldb_sale_per = 0.012
            ELSE
              SET @ldb_sale_per = 0.015
          -- end
          ELSE
            IF @li_PayFlag = 1 OR @li_PayFlag = 5
              SET @ldb_sale_per = 0.008
            ELSE
              SET @ldb_sale_per = 0.0065

          SET @ldb_BasicPrize = @ldb_ordersum * @ldb_sale_per
          SET @ls_gs          = @ls_gs + ',销售奖基数(' + STR(@ldb_BasicPrize) + ')=合同价(' + STR(@ldb_ordersum) +
                                ')×比例(' + STR(@ldb_sale_per) + ')'

          SET @ldb_qkbl       = 0.95

          --预提S
          IF @ll_yt = 0
            IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
              IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
                SET @as_info = '发货后没有超过30天,不予TJ'

          SET @ls_errorType   = '一般提示'

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

          RETURN 0
        --预提E
        END
      ELSE
        IF @ls_type IN ('混凝土搅拌站')
          BEGIN
            SET @ldb_cjsf     = 0.5
            SET @lb_zw        = 1 --语法占位

            IF @ldb_ordersum >= @ldb_ppri
              BEGIN
                SET @ldb_BasicPrize = @ldb_sale_a
                SET @ls_gs          = @ls_gs + '高于保护价(' + STR(@ldb_ppri) + '),'
              END
            ELSE
              BEGIN
                SET @ldb_BasicPrize = @ldb_sale_b
                SET @ls_gs          = @ls_gs + '低于保护价(' + STR(@ldb_ppri) + '),'
              END

            IF @li_zzzj = 1
              BEGIN
                SET @ldb_BasicPrize = @ldb_BasicPrize
                SET @ls_gs          = @ls_gs + '三一主机,销售奖标准(' + STR(@ldb_BasicPrize) + ')'
              END
            ELSE
              BEGIN
                SET @ldb_BasicPrize = @ldb_BasicPrize * @ldb_fsyzjxs
                SET @ls_gs          = @ls_gs + '非三一主机,销售奖标准(' + STR(@ldb_BasicPrize) + ')'
              END

            SET @ldb_qkbl     = 0.95

            --预提S--
            IF @ll_yt = 0
              IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
                IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
                  SET @as_info = '发货后没有超过30天,不予TJ'

            SET @ls_errorType = '一般提示'

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

            COMMIT;
            RETURN 0
          --预提E--
          END
        ELSE
          IF @ls_type IN ('沥青搅拌站')
            BEGIN
              SET @lb_zw        = 1 --语法占位
              SET @ldb_cjsf     = 0.5
              SET @ldb_qkbl     = 0.9

              --预提S--
              IF @ll_yt = 0
                IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
                  IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
                    SET @as_info = '发货后没有超过30天,不予TJ'

              SET @ls_errorType = '一般提示'

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

              COMMIT;
              RETURN 0
            END
          ELSE
            IF @ls_type IN ('砂浆车')
              BEGIN
                SET @lb_zw    = 1 --语法占位
                SET @ldb_cjsf = 0.5
                SET @ldb_qkbl = 0.9
              END
            ELSE
              BEGIN
                SET @ldb_cjsf = 0.5
                SET @ldb_qkbl = 0.9
                SET @lb_zw    = 1 --语法占位
              END

    DECLARE @ldb_RecMoney NUMERIC(18, 4)
    DECLARE @ldb_FirstPay NUMERIC(18, 4)
    DECLARE @ldb_AllMoney NUMERIC(18, 4)
    DECLARE @ldb_OrderPara NUMERIC(18, 4)
    DECLARE @ldb_fkbl NUMERIC(18, 4)
    DECLARE @ldb_jbzzjxs NUMERIC(18, 4)
    DECLARE @ldb_back_rate NUMERIC(18, 4)
    DECLARE @ldb_rate_sjc NUMERIC(18, 4)

    ----根据付款方式进行计算----
    IF @li_PayFlag = 1 OR @li_PayFlag = 6 --分期 全款付款方式
      BEGIN
        SELECT @ldb_RecMoney  = SUM(t_rtmn) ----首付款
        FROM tbl_retamn
        WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 1

        IF @ldb_RecMoney IS NULL
          SET @ldb_RecMoney = 0

        SET @ldb_FirstPay  = @ldb_RecMoney
        SET @ls_gs         = @ls_gs + ',首付款(' + STR(@ldb_FirstPay) + ')'

        SELECT @ldb_AllMoney  = SUM(t_rtmn) --当前已回货款
        FROM tbl_retamn
        WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym

        SET @ls_gs         = @ls_gs + ',已回款总额为(' + STR(@ldb_AllMoney) + ')'
        SET @ls_OrderLevel = 'C'
        SET @ldb_OrderPara = 1.1

        --2011 hjt li_qk系数没有用,是否按照付款方式里的来?
        IF NOT @ls_type = '砂浆车'
          IF @ll_yt = 0
            IF NOT @li_qk = 1
              -- @ld_sendDate=date(ldt_sendDate)
              --@days=DaysAfter(ld_sendDate,ld_month_end)
              IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30
                --发货后没有超过一个月(30天),什么都不干,返回
                SET @ls_errorType = '一般提示'

        SET @as_info       = '发货后没有超过一个月(30天),不予提销售奖。'
        RETURN (0)

        IF @li_qk = 1
          BEGIN
            SET @ls_OrderLevel = 'A'
            SET @ldb_OrderPara = 1.3
            SET @ls_gs         = @ls_gs + '全款'
          END
        ELSE
          IF @ldb_FirstPay / @ldb_plan_sum >= @ldb_cjsf
            BEGIN
              SET @ls_OrderLevel = 'C'
              SET @ldb_OrderPara = 1
              SET @ls_gs         = @ls_gs + '首付50%以上'
            END
          ELSE
            BEGIN
              SET @ls_OrderLevel = 'D' --<=50%@0.8
              SET @ldb_OrderPara = 0.8
              SET @ls_gs         = @ls_gs + '首付50%以下'
            END

        SET @ldb_jbzzjxs   = 1
        SET @ldb_fkbl      = 0.5 ----计提付款比例

        IF @li_exch = 1 --换房、换物和抵款合同销售奖合同等级系数为0.7。
          BEGIN
            SET @ldb_LevelA    = 0.7
            SET @ldb_Levelb    = 0.7
            SET @ldb_LevelC    = 0.7
            SET @ldb_LevelD    = 0.7
            SET @ldb_OrderPara = 0.7
            SET @ls_gs         = @ls_gs + '换房换物和抵款合同,'
          END

        SELECT @ldb_AllMoney  = SUM(t_rtmn) --当前已回货款
        FROM tbl_retamn
        WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym

        IF @ldb_AllMoney IS NULL
          SET @ldb_AllMoney = 0

        SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2)

        IF @ll_yt = 1
          SET @ldb_back_rate = 1

        IF @ls_type = '砂浆车'
          --分别在累计货款回笼30%、70%、100%时计提。
          --             ldb_back_rate, ldb_rate_sjc 回款比例,计提比例
          SET @ldb_rate_sjc = 0

        IF @ldb_back_rate >= 1
          BEGIN
            IF @sale_count_sjc >= 1
              BEGIN
                SET @ldb_rate_sjc = 0
              END
            ELSE
              IF @sale_count_sjc >= 0.7
                BEGIN
                  SET @ldb_rate_sjc = 0.3
                  SET @ls_gs        = @ls_gs + '30%销售奖:'
                END
              ELSE
                IF @sale_count_sjc >= 0.3
                  BEGIN
                    SET @ldb_rate_sjc = 0.7
                    SET @ls_gs        = @ls_gs + '70%销售奖:'
                  END
                ELSE
                  BEGIN
                    SET @ldb_rate_sjc = 1
                    SET @ls_gs        = @ls_gs + '100%销售奖:'
                  END
          END
        ELSE
          IF @ldb_back_rate >= 0.7
            BEGIN
              IF @sale_count_sjc >= 0.7
                SET @ldb_rate_sjc = 0
              ELSE
                IF @sale_count_sjc >= 0.3
                  BEGIN
                    SET @ldb_rate_sjc = 0.4
                    SET @ls_gs        = @ls_gs + '40%销售奖:'
                  END
                ELSE
                  BEGIN
                    SET @ldb_rate_sjc = 0.7
                    SET @ls_gs        = @ls_gs + '70%销售奖:'
                  END
            END
          ELSE
            IF @ldb_back_rate >= 0.3
              BEGIN
                IF @sale_count_sjc >= 0.3
                  SET @ldb_rate_sjc = 0
                ELSE
                  BEGIN
                    SET @ldb_rate_sjc = 0.3
                    SET @ls_gs        = @ls_gs + '30%销售奖:'
                  END
              END

        IF @ldb_rate_sjc > 0
          BEGIN
            SET @ldb_cjj            = @ldb_amnt - (@ldb_zs + @ldb_yhq) - @ldb_zk - @ldb_xzjbc --计算出实际价格(可能是多台的总价)
            SET @al_prize           = @ldb_amnt * @ldb_rate_sjc * 0.02
            SET @ls_gs              = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
                                      ')*本次提奖比例(' + STR(@ldb_rate_sjc) + ')*.0.02]'

            SET @al_prize_manager   = @ldb_amnt * @ldb_rate_sjc * 0.01
            SET @ls_gs_manager      = @ls_gs + '分公司经理销售奖(' + STR(@al_prize_manager) + ')[' + STR(@ldb_amnt) +
                                      ')*本次提奖比例(' + STR(@ldb_rate_sjc) + ')*.0.01]'
          END
        ELSE
          IF @ls_type = '搅拌车'
            --分别在累计货款回笼60%、100%时计提。
            IF @ll_yt = 1
              SET @ldb_back_rate = 0.7

        IF @ldb_back_rate >= 1
          BEGIN
            IF @sale_count_sjc >= 1
              SET @ldb_rate_sjc = 0
            ELSE
              IF @sale_count_sjc >= 0.5
                BEGIN
                  SET @ldb_rate_sjc = 0.5
                  SET @ls_gs        = @ls_gs + '50%销售奖:'
                END
              ELSE
                BEGIN
                  SET @ldb_rate_sjc = 1
                  SET @ls_gs        = @ls_gs + '100%销售奖:'
                END
          END
        ELSE
          IF @ldb_back_rate >= 0.6
            BEGIN
              IF @sale_count_sjc >= 0.5
                SET @ldb_rate_sjc = 0
              ELSE
                BEGIN
                  SET @ldb_rate_sjc = 0.5
                  SET @ls_gs        = @ls_gs + '50%销售奖:'
                END

              IF @ldb_rate_sjc > 0
                BEGIN
                  --区分sy底盘与非sy底盘
                  SET @al_prize   = @ldb_amnt * @ldb_rate_sjc * @ldb_sale_per
                  SET @ls_gs      = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
                                    ')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*销售奖系数(' + STR(@ldb_sale_per) + ')]'
                END
            END
          ELSE --除搅拌车 砂浆车外的 分期或全款合同
            IF @ll_yt = 1
              SET @ldb_AllMoney = @ldb_plan_sum

        IF @ldb_AllMoney / @ldb_plan_sum >= 0.5
          BEGIN
            SET @ldb_rate_sjc = 1

            IF @ls_type = '混凝土搅拌站'
              BEGIN
                SET @ls_gs      = @ls_gs + '销售奖:基数(' + STR(@ldb_BasicPrize) + ')×地区系数(' + STR(@ldb_AreaPara) +
                                  ')' + '×合同系数(' + STR(@ldb_OrderPara) + ')'

                SET @al_prize   = @ldb_BasicPrize * @ldb_AreaPara * @ldb_OrderPara
              END
            ELSE
              --非混凝土搅拌站
              --begin
              SET @ls_gs      = @ls_gs + '销售奖:基数(' + STR(@ldb_BasicPrize) + ')×合同系数(' + STR(@ldb_OrderPara) +
                                ')×地区系数(' + STR(@ldb_AreaPara) + ')×优价系数(' + STR(@ldb_yjpara) + ')'

            SET @al_prize     = @ldb_BasicPrize * @ldb_OrderPara * @ldb_AreaPara * @ldb_yjpara
          -- end
          END
        ELSE
          BEGIN
            SET @ls_gs   = @ls_gs + '回款未达到' + STR(@ldb_fkbl) + '不予TJ.'
            SET @as_info = @ls_gs
          END
      END
    ELSE
      --按揭销售、融资租赁

      -- 手续是否办完
      DECLARE @li_MortFlag INT

    DECLARE @ldb_BackPay NUMERIC(18, 4)

    DECLARE
      @ls_pdat        VARCHAR(7),
      @ls_yhfdsj      VARCHAR(7),
      @ls_ajdzsj      VARCHAR(7),
      @ls_yhfdsj_date VARCHAR(7) --  办理按揭时间
    SELECT @ls_ajdzsj       = ISNULL(CONVERT(VARCHAR(7), t_ajdzsj, 121), '2050-12'),
           @ls_yhfdsj       = ISNULL(CONVERT(VARCHAR(7), t_yhfdsj, 121), '2050-12'),
           @ls_yhfdsj_date  = ISNULL(CONVERT(VARCHAR(11), t_yhfdsj, 121), '2050-12-30')
    FROM tbl_sls
    WHERE t_orno = @al_orno

    --预提S

    IF @ll_yt = 0
      IF @li_zzj = 0 AND @li_zfz = 0
        IF (@ls_type <> '砂浆车')
          IF @ls_pdat > @as_ym OR @ls_yhfdsj > @as_ym OR @ls_ajdzsj > @as_ym
            SET @as_info = '按揭费用未到账或者银行未放贷或权证未受控,不能提取价格奖'

    INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
    VALUES      ('一般提示', @al_orno, @as_ym, 2, @as_info);

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

    -- commit;
    RETURN 0

    IF @li_ajqzsk <> 1
      --2010-11-02 wangq11 现在发现系统中在没有维护“取得按揭权证受控'但提了销售奖.请检查TJ系统程序设置是否没有考虑这一条件.
      --按揭权证受控,需要考虑权证受控时间,否则重提会有错误
      SET @as_info = '未取得按揭权证受控,不能提取销售奖'

    INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
    VALUES      ('操作错误', @al_orno, @as_ym, 3, @as_info);

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

    RETURN 0

    IF @ll_yt = 1
      SET @li_sx = 1

    IF @ls_type = '砂浆车'
      BEGIN
        --首付款到账,按首付款的2%计提
        --抵押登记手续办理完毕后,每月按符合计提条件还贷额的2%计TJ
        IF @li_sx = 1 --手续办完
          BEGIN
            IF @ll_yt = 0
              SELECT @ldb_AllMoney = SUM(t_amnt)
              FROM tbl_backorder
              WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), t_hdrq, 121)
                    <= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
                    <= @as_ym) AND t_orno = @al_orno
            ELSE
              SELECT @ldb_AllMoney = SUM(t_amnt)
              FROM tbl_backorder
              WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), ISNULL(t_hdrq, t_sdat), 121)
                    <= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
                    <= @as_ym) AND t_orno = @al_orno;

            IF @ldb_AllMoney IS NULL
              SET @ldb_AllMoney = 0

            SET @ls_gs = @ls_gs + '已回首付或还贷:' + STR(@ldb_AllMoney) + ';'
          END
        ELSE
          --手续未办完
          BEGIN
            SELECT @ldb_AllMoney  = SUM(t_amnt)
            FROM tbl_backorder
            WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
                  <= @as_ym);

            IF ISNULL(@ldb_AllMoney, 0) = 0
              SET @ldb_AllMoney = 0

            SET @ls_gs         = @ls_gs + '手续未办完,已回首付:' + STR(@ldb_AllMoney) + ';'

            SET @as_info       = '手续未办完,不能提取销售奖'

            INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
            VALUES      ('操作错误', @al_orno, @as_ym, 3, @as_info);

            RETURN -0

            SET @ldb_rate_sjc  = 0
            SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2) --回款率,该TJ的比率
          END

        IF @ll_yt = 1
          SET @ldb_back_rate = 1 --预提
                                 --sale_count_sjc已TJ的比率

        DECLARE @ldb_tjl NUMERIC(18, 4) --本次需TJ的比率

        SET @ldb_tjl            = @ldb_back_rate - @sale_count_sjc

        IF @ldb_tjl > 0
          SET @ldb_rate_sjc = @ldb_tjl

        SET @ls_gs              = @ls_gs + '本次TJ比率(' + STR(@ldb_rate_sjc)

        IF @ldb_rate_sjc > 0
          SET @al_prize = @ldb_amnt * @ldb_rate_sjc * 0.02

        SET @ls_gs             =  @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
                                  ')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*.0.02]'

        SET @al_prize_manager   = @ldb_amnt * @ldb_rate_sjc * 0.01
        SET @ls_gs_manager      = @ls_gs + '分公司经理销售奖(' + STR(@al_prize_manager) + ')[' + STR(@ldb_amnt) +
                                  ')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*.0.01]'
      END
    ELSE
      IF @ls_type = '搅拌车' --搅拌车按揭或融资\
        BEGIN
          -- 首付款和相关费用到账、银行放贷后计提50%;抵押登记手续办理完毕后,客户还完所有贷款计提余下的50%。
          IF @ll_yt = 1
            SET @li_sx = 1 --预提

          IF @li_sx = 1 --手续办完
            BEGIN
              IF @ll_yt = 0
                BEGIN
                  SELECT @ldb_AllMoney = SUM(t_amnt)
                  FROM tbl_backorder
                  WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), t_hdrq, 121)
                        <= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
                        <= @as_ym) AND t_orno = @al_orno
                END
              ELSE
                BEGIN
                  SELECT @ldb_AllMoney = SUM(t_amnt)
                  FROM tbl_backorder
                  WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0))

                  IF ISNULL(@ldb_AllMoney, 0) = 0
                    SET @ldb_AllMoney = 0

                  SET @ls_gs        = @ls_gs + '已回首付或还贷:' + STR(@ldb_AllMoney) + ';'
                END
            END
          ELSE
            --手续未办完
            BEGIN
              SELECT @ldb_AllMoney  = SUM(t_amnt)
              FROM tbl_backorder
              WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
                    <= @as_ym);

              IF ISNULL(@ldb_AllMoney, 0) = 0
                SET @ldb_AllMoney = 0

              SET @ls_gs         = @ls_gs + '手续未办完,已回首付:' + STR(@ldb_AllMoney) + ';'

              SET @as_info       = '手续未办完,不能提取销售奖'

              --@ls_ErrType='操作错误'
              INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
              VALUES      ('操作错误', @al_orno, @as_ym, 3, @as_info)

              RETURN -0

              SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2) --判断是否全部还贷

              IF @ll_yt = 1
                SET @ldb_back_rate = 0.7

              IF @ldb_back_rate < 1
                SET @ldb_rate_sjc = 0.5 - @sale_count_sjc
              ELSE
                SET @ldb_rate_sjc = 1 - @sale_count_sjc

              SET @ls_gs         = @ls_gs + '本次TJ比率(' + STR(@ldb_rate_sjc)

              IF @ldb_rate_sjc > 0
                SET @al_prize = @ldb_amnt * @ldb_rate_sjc * @ldb_sale_per

              SET @ls_gs         = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
                                   ')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*' + STR(@ldb_sale_per) + ')]'
            END
        END
      ELSE
        --非砂浆车 搅拌车 按揭或融资
        BEGIN
          SET @ldb_OrderPara = 1

          --2008 6.4
          IF @li_PayFlag = 7 --自办按揭
            BEGIN
              IF @li_exch = 1 --抵债换物合同按八成三年计提
                BEGIN
                  SET @li_mort_years = 3
                  SET @li_mort_per   = 8
                  SET @ls_gs         = @ls_gs + '换物合同,'
                  SET @ldb_OrderPara = 0.7
                END

              DECLARE @ll_sfs INT
              --取得首付分期数,如分多期则按八成三年计提---huangjt 2009-09-04
              SET @ll_sfs = 0
              SELECT @ll_sfs = COUNT(*)
              FROM tbl_backorder
              WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 1;

              IF @ll_sfs > 1 -----huangjt 2009-09-04 加入首付款期数的自动判定
                BEGIN
                  SET @li_mort_years = 3
                  SET @li_mort_per   = 8
                  SET @ls_gs         = @ls_gs + '首付期数(' + STR(@ll_sfs) + ')'
                  SET @ldb_OrderPara = 1
                END

              IF @li_aj4 = 1 --首付分期按八成三年计提
                BEGIN
                  SET @li_mort_years = 3
                  SET @li_mort_per   = 8
                  SET @ls_gs         = @ls_gs + '按揭4,'
                  SET @ldb_OrderPara = 1
                END

              IF @li_hg = 1 --公司承提回购
                BEGIN
                  SET @ls_OrderLevel = 'B'
                  SET @ldb_OrderPara = 1.1
                  SET @ls_gs         = @ls_gs + '公司承担回购,'

                  --***********--2010-07-12 by wangq11 2010规则p29******************--
                  IF @li_mort_years <= 2
                    SET @li_mort_years = 2

                  IF @li_mort_years > 2 AND @li_mort_years <= 3
                    SET @li_mort_years = 3

                  IF @li_mort_per <= 7
                    SET @li_mort_per = 7

                  IF @li_mort_per > 7
                    SET @li_mort_per = 8

                  --Messagebox(string(li_mort_per)+'成',String(li_mort_years)+'年')
                  --*******************************--

                  IF @li_mort_per = 7
                    BEGIN
                      IF @li_mort_years = 3
                        BEGIN
                          SET @ls_OrderLevel = 'B'
                          SET @ldb_OrderPara = 1.1
                          SET @ls_gs         = @ls_gs + '七成三年'
                        END
                      ELSE
                        IF @li_mort_years = 2
                          BEGIN
                            SET @ls_OrderLevel = 'A1'
                            SET @ldb_OrderPara = 1.2
                            SET @ls_gs         = @ls_gs + '七成二年'
                          END
                        ELSE
                          BEGIN
                            SET @ls_OrderLevel = 'C1'
                            SET @ls_gs         = @ls_gs + '其它按揭'
                            SET @ldb_OrderPara = 0.9
                          END
                    END
                  ELSE
                    IF @li_mort_per = 8
                      BEGIN
                        IF @li_mort_years = 3
                          BEGIN
                            SET @ls_OrderLevel = 'C'
                            SET @ldb_OrderPara = 1
                            SET @ls_gs         = @ls_gs + '八成三年'
                          END
                        ELSE
                          IF @li_mort_years = 2
                            BEGIN
                              SET @ls_OrderLevel = 'B'
                              SET @ldb_OrderPara = 1.1
                              SET @ls_gs         = @ls_gs + '八成二年'
                            END
                          ELSE
                            BEGIN
                              SET @ls_OrderLevel = 'C1'
                              SET @ldb_OrderPara = 0.9
                              SET @ls_gs         = @ls_gs + '其它按揭'
                            END
                      END
                    ELSE
                      BEGIN
                        SET @ls_OrderLevel = 'C1'
                        SET @ldb_OrderPara = 0.9
                        SET @ls_gs         = @ls_gs + '其它按揭'
                      END
                END --messagebox(ls_gs,ls_gs)
                    --if li_mort_per=8 then --八成
                    -- ldb_Levelb=1
                    --end if
              ELSE
                --公司不承提回购
                BEGIN
                  SET @ls_OrderLevel = 'A'
                  SET @ldb_OrderPara = 1.3
                  SET @ls_gs         = @ls_gs + '公司不承提回购,'
                END
            END
          ELSE
            IF @li_PayFlag = 3
              BEGIN
                SET @ls_OrderLevel = 'A'
                SET @ldb_OrderPara = 1.3
                SET @ls_gs         = @ls_gs + '融资,'
              END
            ELSE
              IF @li_PayFlag = 4
                BEGIN
                  SET @ls_OrderLevel = 'C'
                  SET @ldb_OrderPara = 1
                  SET @ls_gs         = @ls_gs + '一类融资,'
                END
              ELSE
                IF @li_PayFlag = 5
                  BEGIN
                    SET @ls_OrderLevel = 'D'
                    SET @ldb_OrderPara = 0.8
                    SET @ls_gs         = @ls_gs + '二类融资,'
                  END
                ELSE
                  IF @li_PayFlag = 2
                    BEGIN
                      SET @ls_OrderLevel = 'c'
                      SET @ldb_OrderPara = 1
                      SET @ls_gs         = @ls_gs + '代办按揭'
                    END

          --2009.01.01
          IF @li_exch = 1 --换房、换物和抵款合同销售奖合同等级系数为0.7。
            BEGIN
              SET @ldb_LevelA    = 0.7
              SET @ldb_LevelA1   = 0.7
              SET @ldb_Levelb    = 0.7
              SET @ldb_LevelC    = 0.7
              SET @ldb_LevelC1   = 0.7
              SET @ldb_LevelD    = 0.7
              SET @ldb_OrderPara = 0.7
              SET @ls_gs         = @ls_gs + '换房换物和抵款合同,'
            END

          IF @li_aj4 = 1 --首付分期按八成三年计提
            BEGIN
              SET @li_mort_years = 3
              SET @li_mort_per   = 8
              SET @ls_gs         = @ls_gs + '按揭4,'
              SET @ldb_OrderPara = 1
              SET @ldb_rate_sjc  = 1
            END

          SET @ldb_rate_sjc  = 1
          SET @ls_gs         = @ls_gs + '销售奖@基数(' + STR(@ldb_BasicPrize) + ')×合同系数(' + STR(@ldb_OrderPara) +
                               ')×地区系数(' + STR(@ldb_AreaPara) + ')×优价系数(' + STR(@ldb_yjpara) + ')'

          SET @al_prize      = @ldb_BasicPrize * @ldb_OrderPara * @ldb_AreaPara * @ldb_yjpara
        END

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

    SET @ldb_PrizeDiffer = 0

    IF @al_prize = 0
      BEGIN
        SET @as_info = @as_info + '本月无销售奖!' + @ls_gs
        RETURN 0
      END

    --2008检查是否有低价罚与延期罚息需要抵扣。
    DECLARE
      @ldb_cdjr     NUMERIC(18, 4),
      @ldb_wqdcr    NUMERIC(18, 4),
      @ldb_diff_all NUMERIC(18, 4) --ldb_diff_all所有未冲抵差额,本次需冲抵金额,本条TJ数据未冲抵金额.
    DECLARE @ll_id INT

    --
    --本月回笼奖不为零,检查是否有低价罚与延期罚息需要抵扣。
    --如果本次回笼奖加上所有已TJ超过保底佣金,则将多的部分冲抵

    --2008-06-17  力士乐主油泵奖罚减半
    IF @li_lsl = 1
      BEGIN
        SET @al_prize = @al_prize / 2
      END

    IF @li_yj = 1 AND @li_jsb = 1
      BEGIN
        SET @ldb_lpri = 0
      END

    IF @li_yjhx = 1
      BEGIN
        SET @ldb_lpri = @ldb_lpri * (@ldb_ordersum - @ldb_dkr) / @ldb_ordersum
      END
    ELSE
      BEGIN
        IF @li_yj = 1
          BEGIN
            SET @ldb_lpri      = @ldb_lpri * (@ldb_ordersum - @ldb_zs * 0.5 - @ldb_yhq * 0.5 - @ldb_zk + @ldb_nopp - @ldb_xzjbc) /
                                 @ldb_ppri
          END
      END

    DECLARE @row INT
    DECLARE @ls_emno2 VARCHAR(100) --@ldts_assign.getitemstring(ll,'fld_emno')
    DECLARE @ll_corp2 INT --@ldts_assign.getitemnumber(ll,'fld_corp')
    DECLARE @ll_province2 INT --@ldts_assign.getitemnumber(ll,'fld_province')
    DECLARE @ll_company2 INT --@ldts_assign.getitemnumber(ll,'fld_company')
    DECLARE @ldb_pre NUMERIC(18, 4) --@ldts_assign.getitemnumber(ll,'fld_pre')
    DECLARE @ldb_cdjr_pre NUMERIC(18, 4)
    DECLARE @ll_total INT
    DECLARE @ll INT

    IF @al_prize + @ldb_PrizeSum > 0
      BEGIN
        SELECT @ldb_diff_all = SUM(ISNULL(t_diff, 0) + ISNULL(t_ycd, 0))
        FROM tbl_priseresult
        WHERE ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;

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

        SET @ldb_diff_all = ROUND(@ldb_diff_all, 0)

        IF @ldb_diff_all < 0 --有差额需冲抵
          BEGIN
            IF @ldb_diff_all + (@al_prize + @ldb_PrizeSum - 0) <= 0
              BEGIN
                --正好将本次TJ多余金额全部冲抵或全部冲抵还不够
                SET @ldb_cdjr = ROUND((@al_prize + @ldb_PrizeSum - 0), 0)

                IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
                  BEGIN
                    SET @ldb_cdjr = 0
                  END

                WHILE @ldb_cdjr > 0
                BEGIN
                  SET @row      = 1 -- row=ldts_assign.retrieve(al_orno,as_ym)
                  SET @ll_total = 0
                  --for @ll=1 to @row
                  SET @ll       = 1

                  IF EXISTS
                       (SELECT 1
                        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)
                               ))
                    BEGIN
                      OPEN tbl_assign_Cursor

                      WHILE 1 = 1
                      BEGIN
                        FETCH tbl_assign_Cursor
                        INTO @ls_emno2, @ll_corp2, @ll_province2, @ll_province2, @ldb_pre --

                        IF @@fetch_status <> 0
                          BREAK

                        --while @ll<=@row
                        --begin
                        SET @ll           = @ll + 1
                        SET @ldb_cdjr_pre = @ldb_cdjr
                        SET @ll_total     = @ll_total + 1
                        SET @ls_gs        = @ls_gs + 'TJ分配,获取' + STR(@ldb_pre) + ';'

                        SET @ldb_wqdcr    = 0

                        --2009-11-21分公司:备抵人与冲抵人必须一致;个人:所属分公司一致即可
                        IF @ls_emno2 = '1000000'
                          BEGIN
                            SELECT        TOP 1
                                          @ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
                                          @ll_id = t_id
                            FROM          tbl_priseresult
                            WHERE         ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
                                          AND ISNULL(t_diff, 0) + ISNULL(t_ycd, 0) < 0 AND t_crep = @ls_emno2
                            ORDER BY      t_cdat ASC
                          END
                        ELSE
                          BEGIN
                            SELECT        TOP 1
                                          @ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
                                          @ll_id = t_id
                            FROM          tbl_priseresult
                            WHERE         ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
                                          AND ISNULL(t_diff, 0) + ISNULL(t_ycd, 0) < 0 AND t_crep IN (SELECT fld_emno
                                                                                                      FROM   tbl_assign
                                                                                                      WHERE  fld_company =
                                          @ll_company2 AND fld_orno = @al_orno) AND t_crep <> '1000000'
                            ORDER BY      t_cdat ASC
                          END

                        IF @ldb_wqdcr + (@ldb_cdjr_pre) * @ldb_pre >= 0 --本次的差额被全部冲抵完
                          BEGIN
                            --冲回本次差额
                            --插入TJ结果表
                            INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
                                     t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
                              SELECT GETDATE(), t_orno, t_crep,
                                     t_corp, t_company, t_comp,
                                     t_province, @as_ctym, t_rdat,
                                     3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
                                     1, '<冲抵-销售奖>' + t_yymm + t_gs, '<冲抵-销售奖>' + t_yymm + t_gs2,
                                     @gs_userName, @ai_cttimes
                              FROM   tbl_priseresult
                              WHERE  t_id = @ll_id

                            --将所有差额冲抵
                            UPDATE tbl_priseresult
                            SET    t_ycd = -t_diff
                            WHERE  t_id = @ll_id

                            IF @@ERROR <> 0
                              BEGIN
                                SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                                SET @ls_ErrType = '数据库错误'
                                ROLLBACK;

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

                                RETURN -1
                              END

                            SET @ldb_cdjr = ROUND(@ldb_wqdcr + @ldb_cdjr, 0)

                            IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
                              SET @ldb_cdjr = 0
                          END
                        ELSE
                          --本次的差额只被冲抵一部分,即ldb_cdjr*ldb_pre这么多
                          BEGIN
                            --冲回本次产生金额
                            INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
                                     t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
                              SELECT GETDATE(), t_orno, t_crep,
                                     t_corp, t_company, t_comp,
                                     t_province, @as_ctym, t_rdat,
                                     3 t_priz, 0, -@ldb_cdjr_pre * @ldb_pre,
                                     1, '<冲抵-销售奖>' + t_yymm + t_gs, '<冲抵-销售奖>' + t_yymm + t_gs,
                                     @gs_userName, @ai_cttimes
                              FROM   tbl_priseresult
                              WHERE  t_id = @ll_id

                            UPDATE tbl_priseresult
                            SET    t_ycd = ISNULL(t_ycd, 0) + @ldb_cdjr_pre * @ldb_pre
                            WHERE  t_id = @ll_id

                            IF @@ERROR <> 0
                              BEGIN
                                SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                                SET @ls_ErrType = '数据库错误'
                                ROLLBACK;

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

                                RETURN -1
                              END

                            SET @ldb_cdjr = ROUND(@ldb_cdjr - @ldb_cdjr_pre * @ldb_pre, 0)

                            IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
                              SET @ldb_cdjr = 0
                          END
                      END

                      CLOSE tbl_assign_Cursor
                      DEALLOCATE tbl_assign_Cursor
                    END
                  ELSE
                    --没有TJ分配
                    BEGIN
                      SET @ldb_wqdcr = 0
                      SELECT        TOP 1
                                    @ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
                                    @ll_id     = t_id --into @ldb_wqdcr,@ll_id
                      FROM          tbl_priseresult
                      WHERE         ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes AND
                                    ISNULL                                                                                    (t_diff
                                    ,
                                    0) + ISNULL                                                                               (t_ycd, 0)
                                    < 0
                      ORDER BY      t_cdat ASC

                      IF @ldb_wqdcr + @ldb_cdjr >= 0 --本次的差额被全部冲抵完
                        BEGIN
                          --冲回本次差额
                          INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
                                   t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
                            SELECT GETDATE(), t_orno, t_crep, t_corp,
                                   t_company, t_comp, t_province, @as_ctym,
                                   t_rdat, 3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
                                   1, '<冲抵-销售奖>' + t_yymm + t_gs, '<冲抵>' + t_yymm + t_gs2, @gs_userName,
                                   @ai_cttimes
                            FROM   tbl_priseresult
                            WHERE  t_id = @ll_id

                          --将所有差额冲抵
                          UPDATE tbl_priseresult
                          SET    t_ycd = -t_diff
                          WHERE  t_id = @ll_id;

                          IF @@ERROR <> 0
                            BEGIN
                              SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                              SET @ls_ErrType = '数据库错误'
                              ROLLBACK;

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

                              RETURN -1
                            END

                          SET @ldb_cdjr = @ldb_wqdcr + @ldb_cdjr
                        END
                      ELSE
                        BEGIN
                          --本次的差额只被冲抵一部分,即ldb_cdjr这么多
                          --冲回本次产生金额
                          INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
                                   t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
                            SELECT GETDATE(), t_orno, t_crep, t_corp, t_company, t_comp, t_province,
                                   @as_ctym, t_rdat, 3 t_priz, 0, -@ldb_cdjr, 1, '<冲抵-销售奖>' + t_yymm + t_gs,
                                   '<冲抵-销售奖>' + t_yymm + t_gs, @gs_userName, @ai_cttimes
                            FROM   tbl_priseresult
                            WHERE  t_id = @ll_id;

                          UPDATE tbl_priseresult
                          SET    t_ycd = ISNULL(t_ycd, 0) + @ldb_cdjr
                          WHERE  t_id = @ll_id;

                          IF @@ERROR <> 0
                            BEGIN
                              SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                              SET @ls_ErrType = '数据库错误'
                              ROLLBACK;

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

                              RETURN -1
                            END

                          SET @ldb_cdjr = 0
                        END
                    END
                END
              END
          END
        ELSE
          BEGIN
            --奖冲抵完之后还有多
            --冲回所有差额
            INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat, t_priz,
                     t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
              SELECT GETDATE(), t_orno, t_crep, t_corp, t_company, t_comp,
                     t_province, @as_ctym, t_rdat, 3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
                     1, '<冲抵-销售奖>' + t_yymm + t_gs, '<冲抵-销售奖>' + t_yymm + t_gs, @gs_userName, @ai_cttimes
              FROM   tbl_priseresult
              WHERE  t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes AND ISNULL(t_ycd, 0) + ISNULL(t_diff, 0) < 0

            --将所有差额冲抵
            UPDATE tbl_priseresult
            SET    t_ycd = -t_diff
            WHERE  ISNULL(t_ycd, 0) + ISNULL(t_diff, 0) < 0 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;

            IF @@ERROR <> 0
              BEGIN
                SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                SET @ls_ErrType = '数据库错误'
                ROLLBACK;

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

                RETURN -1
              END
          END

        --获取TJ分配比例
        DECLARE
          @ls_gs_tmp         VARCHAR(1000),
          @ls_gs_manager_tmp VARCHAR(1000)

        SET @row          = 1 --ldts_assign.retrieve(al_orno,as_ym)
        SET @ll_total     = 0
        OPEN tbl_assign_Cursor

        WHILE 1 = 1
        BEGIN
          FETCH tbl_assign_Cursor
          INTO @ls_emno2, @ll_corp2, @ll_province2, @ll_province2, @ldb_pre --

          IF @@fetch_status <> 0
            BREAK

          SET @ll           = @ll + 1
          SET @ldb_cdjr_pre = @ldb_cdjr
          SET @ll_total     = @ll_total + 1
          SET @ls_gs        = @ls_gs + 'TJ分配,获取' + STR(@ldb_pre) + ';'
          SET @ldb_wqdcr    = 0

          --插入TJ结果表
          INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                        t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
          VALUES      (@al_orno, @ldt_BackDate, 3, @al_prize * @ldb_pre * 0.85, @ldb_PrizeDiffer * @ldb_pre, @as_ctym, GETDATE(),
                       @ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
                       @gs_username, @ai_cttimes, @ldb_rate_sjc * @ldb_pre * 0.85)

          --分公司
          INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                        t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
          VALUES      (@al_orno, @ldt_BackDate, 3, (@al_prize_manager + @al_prize) * @ldb_pre * 0.15, @ldb_PrizeDiffer * @ldb_pre,
                       @as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
                       @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
                       @ai_cttimes, @ldb_rate_sjc * @ldb_pre * 0.15)

          --分公司经理
          IF @ls_type = '砂浆车'
            BEGIN
              SET @ls_gs_manager_tmp = @ls_gs_manager + 'TJ分配,获取' + STR(@ldb_pre) + ';'

              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
              VALUES      (@al_orno, @ldt_BackDate, 3, @al_prize_manager * @ldb_pre * 0.85, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
                           GETDATE(), '3000000', @ll_corp2, @ll_company2, @ll_province2, @ls_gs_manager_tmp,
                           @ls_gs_manager_tmp, @li_flag1, @gs_username, @ai_cttimes, 0)
            END

          IF @li_xxj = 1
            BEGIN
              --需要提信息奖
              SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
              FROM tbl_priseresult
              WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes

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

              SET @ldb_bc_rate   = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate

              --扣除营销代表销售奖做为信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
              VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
                           @ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
                           @gs_username, @ai_cttimes)

              --插入信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
              VALUES      (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
                           @ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
                           @gs_username, @ai_cttimes, @ldb_bc_rate * @ldb_pre * 0.85)

              --扣除分公司部分销售奖做为信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
              VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
                           @as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
                           @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
                           @ai_cttimes)

              --插入信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
              VALUES      (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
                           @as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
                           @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
                           @ai_cttimes, @ldb_bc_rate * @ldb_pre * 0.15)
            END
          ELSE
            BEGIN
              SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
              FROM tbl_priseresult
              WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes

              IF ISNULL(@ldb_info_rate, 0) = 0
                SET @ldb_info_rate = 0

              --sale_count_sjc + ldb_rate_sjc 已提的销售奖比率  ldb_info_rate已提的信息奖比率
              -- sale_count_sjc + ldb_rate_sjc - ldb_info_rate  --本次需冲的信息奖比率
              SET @ldb_bc_rate   = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate

              --扣除营销代表销售奖做为信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
              VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
                           @ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
                           @gs_username, @ai_cttimes)

              --   --插入信息奖
              --   insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
              --   values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.85*@ldb_pre,0,@as_ctym,getdate(),@ll_emno2,@ll_corp2,@ll_company2,@ll_province2,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*@ldb_pre*0.85)
              --扣除分公司部分销售奖做为信息奖
              INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                            t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
              VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
                           @as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
                           @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
                           @ai_cttimes)
            --     --插入信息奖
            --   insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
            --   values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.15*@ldb_pre,@ldb_PrizeDiffer*@ldb_pre,@as_ctym,getdate(),1000000,@ll_corp2,@ll_company2,@ll_province2,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*@ldb_pre*0.15)
            END

          IF @@ERROR <> 0
            BEGIN
              SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
              SET @ls_ErrType = '数据库错误'
              ROLLBACK;

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

              RETURN -1
            END
        END

        CLOSE tbl_assign_Cursor
        DEALLOCATE tbl_assign_Cursorn

        --DESTROY @ldts_assign
        IF @ll_total = 0 --没有分配系数,照常
          BEGIN
            --插入TJ结果表
            INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                          t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
            VALUES      (@al_orno, @ldt_BackDate, 3, @al_prize * 0.85, @ldb_PrizeDiffer, @as_ctym, GETDATE(), @ls_CrewId, @ll_corp,
                         @ll_company, @ll_province, @ls_gs, @ls_gs, @li_flag1, @gs_username, @ai_cttimes, @ldb_rate_sjc * 0.85)

            --分公司
            INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                          t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
            VALUES      (@al_orno, @ldt_BackDate, 3, (@al_prize_manager + @al_prize) * 0.15, @ldb_PrizeDiffer, @as_ctym, GETDATE(),
                         '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs, @ls_gs, @li_flag1,
                         @gs_username, @ai_cttimes, @ldb_rate_sjc * 0.15)

            --分公司经理
            IF @ls_type = '砂浆车'
              BEGIN
                SET @ls_gs_manager = @ls_gs_manager + '公司提留0.15.'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
                VALUES      (@al_orno, @ldt_BackDate, 3, @al_prize_manager * 0.85, @ldb_PrizeDiffer, @as_ctym, GETDATE(),
                             '3000000', @ll_corp, @ll_company, @ll_province, @ls_gs_manager, @ls_gs_manager, @li_flag1,
                             @gs_username, @ai_cttimes, 0)
              END

            IF @li_xxj = 1 --需要提信息奖
              BEGIN
                SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
                FROM tbl_priseresult
                WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;

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

                --扣除营销代表销售奖做为信息奖
                SET @ls_gs_tmp     = '信息奖扣除'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
                VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
                             @ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes)

                --插入信息奖
                SET @ls_gs_tmp     = '信息奖'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
                VALUES      (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
                             @ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes,
                             @ldb_bc_rate * 0.85)

                --扣除分公司部分销售奖做为信息奖
                SET @ls_gs_tmp     = '信息奖扣除'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
                VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
                             GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
                             @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes)

                --插入信息奖
                SET @ls_gs_tmp     = '信息奖'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
                VALUES      (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
                             GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
                             @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes, @ldb_bc_rate * 0.15)
              END
            ELSE
              BEGIN
                SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
                FROM tbl_priseresult
                WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;

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

                --sale_count_sjc + ldb_rate_sjc 已提的销售奖比率  ldb_info_rate已提的信息奖比率
                -- sale_count_sjc + ldb_rate_sjc - ldb_info_rate  --本次需冲的信息奖比率
                SET @ldb_bc_rate   = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate
                --扣除营销代表销售奖做为信息奖
                SET @ls_gs_tmp     = '信息奖扣除'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
                VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
                             @ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes);

                --   --插入信息奖
                --   ls_gs_tmp='信息奖'
                --   insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
                --   values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.85,0,@as_ctym,getdate(),@ls_CrewId,@ll_corp,@ll_company,@ll_province,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*0.85);
                --扣除分公司部分销售奖做为信息奖
                SET @ls_gs_tmp     = '信息奖扣除'

                INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
                              t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
                VALUES      (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
                             GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
                             @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes);
              --     --插入信息奖
              --   ls_gs_tmp='信息奖'
              --   insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
              --   values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.15,@ldb_PrizeDiffer*@ldb_pre,@as_ctym,getdate(),1000000,@ll_corp,@ll_company,@ll_province,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*0.15);
              END

            IF @@ERROR <> 0
              BEGIN
                SET @as_info    = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
                SET @ls_ErrType = '数据库错误'
                ROLLBACK;

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

                RETURN -1
              END
          END
      END

    SET @as_info         = @ls_gs
    RETURN
  -----------------------------------------------------------------------------------------------
  END
GO

SET  QUOTED_IDENTIFIER OFF
GO
SET  ANSI_NULLS ON
GO

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

上一篇: pppp
下一篇: rar
请登录后发表评论 登录
全部评论

注册时间:2012-01-31

  • 博文量
    39
  • 访问量
    18722