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

ALTER PROCEDURE [dbo].[TJ_pricePrize_2012]
  @al_orno INT, --订单号
               @as_ym CHAR(7), @as_ctym CHAR(7), --重提日期
                                                @ai_cttimes INT, @al_return INT, --
                                                                                @as_info VARCHAR(500) OUT --返回信息
AS
  BEGIN
    ---------------------------------------------
    --函数名称    ：TJ_pricePrize_2012 价格奖计提
    --函数功能描述:按照传入的订单号和T月对该订单进行T
    -----------------------------------------------------------------
    DECLARE @ll_orno INT ----订单号
    DECLARE
      @ls_emno2    VARCHAR(20),
      @ls_emno     VARCHAR(20),
      @gs_userName VARCHAR(20)

    DECLARE
      @li_prize_type INT,
      @li_Comp       INT,
      @li_mort_years INT,
      @li_mort_per   INT,
      @li_yj         INT,
      @li_jxs        INT,
      @li_thh        INT ----所T项（此处即为2-价格价）
    DECLARE
      @days     INT,
      @li_month INT --发货后到T月最后一天的天数
    DECLARE
      @li_prize_count INT,
      @li_fine_count  INT,
      @li_jsb         INT,
      @li_yjhx        INT ----价格奖，价格罚提取次数,旧设备,以旧换新
    DECLARE
      @ls_ym   VARCHAR(10),
      @ls_info VARCHAR(1000),
      @ls_gs1  VARCHAR(1000),
      @ls_gs2  VARCHAR(1000),
      @ls_ppri VARCHAR(1000),
      @ls_cjj  VARCHAR(1000) --T月　T公式个人部分，T公式公司部分
    DECLARE @ls_errorType VARCHAR(50) --错误类型－－用字符描述：同样的错误注意用同样的名字
    DECLARE
      @ls_error  VARCHAR(1000),
      @ls_pdat   VARCHAR(7),
      @ls_ajqzsk VARCHAR(7) --存放出错信息
    DECLARE
      @ldb_return_sum NUMERIC(18, 4),
      @ldb_ppri       NUMERIC(18, 4),
      @ldb_amnt       NUMERIC(18, 4),
      @ldb_plan_sum   NUMERIC(18, 4) --发货后一个月内回款总额，保护价基数,合同金额,回款计划总金额
    DECLARE
      @ld_month_end DATETIME,
      @ld_temp      DATETIME,
      @ld_sendDate  DATETIME,
      @ld_31_after  DATETIME --T月最后一日,临时,发货日期,发货后一个月的时间
    DECLARE
      @ldb_all_return       NUMERIC(18, 4),
      @ldb_am_company_per_a NUMERIC(18, 4) --已回款总额
    DECLARE
      @ldt_pdat DATETIME,
      @ldt_temp DATETIME
    --计算实际价格 -- 该实际价格有可能是多台的总额
    DECLARE
      @ldb_zs  NUMERIC(18, 4),
      @ldb_zk  NUMERIC(18, 4),
      @ldb_dkr NUMERIC(18, 4) --赠送配件，折扣,抵款额
    DECLARE @ldb_cjj NUMERIC(18, 4) --成交价
    DECLARE
      @ldb_am_company NUMERIC(18, 4),
      @ldb_am_indv    NUMERIC(18, 4) --罚款分公司部分　罚款个人部分
    DECLARE @ldb_am_indiv_per_a NUMERIC(18, 4) --价格罚分公司比例(高于保护价10%部分)，价格罚个人比例(高于保护价10%部分)
    DECLARE
      @ldb_am_company_per_b NUMERIC(18, 4),
      @ldb_am_indiv_per_b   NUMERIC(18, 4) --价格罚分公司比例(低于保护价10%部分)，价格罚个人比例(低保护价10%部分)
    DECLARE
      @ldb_pz_company_per NUMERIC(18, 4),
      @ldb_pz_indiv_per   NUMERIC(18, 4) --价格奖分公司比例，价格奖个人比例
    DECLARE
      @ldb_pz_company NUMERIC(18, 4),
      @ldb_pz_indiv   NUMERIC(18, 4) --价格奖分公司部分　价格奖个人部分
    DECLARE
      @ldb_company NUMERIC(18, 4),
      @ldb_indiv   NUMERIC(18, 4), --分公司奖罚，个人奖罚
      @ldb_nopp    NUMERIC(18, 4)
    DECLARE @ldb_xzjbc NUMERIC(18, 4) --选装件补差
    DECLARE
      @ldb_cjsf NUMERIC(18, 4), --C级首付
      @ldb_lpri NUMERIC(18, 4),
      @as_prise NUMERIC(18, 4)
    DECLARE
      @ls_type      VARCHAR(15),
      @ls_yhfdsj    VARCHAR(7), --产品类型 银行放贷时间
      @ls_prizedate VARCHAR(15),
      @ls_ajdzsj    VARCHAR(7)
    DECLARE @ldb_have_rate NUMERIC(6, 4) --已T比率
    DECLARE @ldb_all_rate NUMERIC(6, 4) --应该T比率
    DECLARE @ldb_rate NUMERIC(6, 4) --本次T比率
    --根据获取的订单号,对指定月份该订单进行T
    SET @ll_orno        = @al_orno
    SET @ls_ym          = @as_ym
    SET @li_prize_type  = 2
    SET @ls_PrizeDate   = @as_ym
    DECLARE @li_ct INT
    SET @li_ct          = 0
    SET @ls_gs1         = ''
    SET @ls_gs2         = ''

    IF ISNULL(@as_ctym, '') = ''
      BEGIN
        --重提日期为空为正常T
        SET @as_ctym = @ls_ym
        SET @li_ct   = 0
      END
    ELSE --为重提
      BEGIN
        SET @ls_gs1 = '<重提' + @ls_ym + '>'
        --SET @ls_gs2 = '<重提' + @ls_ym + '>'
        SET @li_ct  = 1
      END

    SET @ls_gs1         = @ls_gs1 + '(js20120101)'
    --SET @ls_gs2         = @ls_gs2 + '(js20120101)'
    SET @as_info        = '(js20120101)'
    SET @as_prise       = 0

    --清除该订单该奖项该月T结果表中的计算结果
    IF @li_ct = 0
      BEGIN
        --重提的已经在重提前一次性删除所有T月的T结果，不需要再删除
        DELETE FROM tbl_priseresult
        WHERE       t_orno = @ll_orno AND (t_priz = 2 OR t_priz = 7) AND t_yymm = @ls_ym AND ISNULL(t_cttimes, 0) = @ai_cttimes;
      END

    DECLARE @ll_sale_flag INT
    SELECT TOP 1
           @ll_sale_flag   = t_orno
    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;
    SELECT @ll_sale_flag   = ISNULL(@ll_sale_flag, 0)

    IF NOT @ll_sale_flag > 0 --判断该订单价格奖是否已提
      BEGIN
        --销售奖未提，不提价格奖罚
        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, 2 priz

        RETURN 0
      END

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

    IF @ldb_have_rate >= 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, 2 priz

        RETURN 0
      END

    --取得订单基本信息
    DECLARE @ls_item VARCHAR(50)
    DECLARE
      @ll_company INT,
      @ll_emno22  INT,
      @ll_payType INT,
      @ll_rtn     INT --分公司号，营销代表工号，付款方式，是否有回购
    DECLARE
      @ll_corp     INT,
      @ll_province INT --子公司
    DECLARE
      @li_sum      INT,
      @li_BackFlag INT,
      @li_havertn  INT --发货数量,发生回购，有回购
    DECLARE
      @ldt_sendDate DATETIME,
      @ldt_adat     DATETIME
    DECLARE
      @ldb_prst        NUMERIC(18, 4),
      @ldb_comdec      NUMERIC(18, 4),
      @ldb_comdec_je   NUMERIC(18, 4),
      @ldb_nortndec    NUMERIC(18, 4),
      @ldb_nortndec_je NUMERIC(18, 4),
      @ldb_yhq         NUMERIC(18, 4) --赠送配件
    DECLARE @li_ajdz INT --按揭到账
    DECLARE @ldt_ajdzsj DATETIME
    DECLARE @li_yhfd INT --用户还贷
    DECLARE
      @ldt_yhfdsj DATETIME,
      @ldt_today  DATETIME
    DECLARE
      @li_zzj   INT,
      @li_zfz   INT,
      @li_jbzbp INT --转专制,转法制,搅拌站标配
                    --      产品代码,分公司,营销代表工号,付款方式,发货日期,合同金额,是否有回购，子公司，省份
    DECLARE
      @ls_zfz CHAR(7),
      @ls_zzz VARCHAR(7)

    DECLARE
      @li_416    INT,
      @li_51     INT,
      @li_080101 INT,
      @li_new    INT,
      @li_lsl    INT,
      @li_xlh    INT,
      @li_exch   INT --li_new是否为新产品
    DECLARE
      @ldb_diff_all    NUMERIC(18, 4),
      @ldb_prizediffer NUMERIC(18, 4),
      @ldb_bybcrl      NUMERIC(18, 4),
      @ldb_gssqj_dd    NUMERIC(18, 4) --ldb_gssqj_dd订单上的公司授权价
    --DECLARE
    -- @ldb_yjjxs NUMERIC(18, 4),
    --@ldb_djfxs NUMERIC(18, 4)
    SET @li_51          = 0
    SET @li_416         = 0
    SET @li_080101      = -1
    SELECT -- @ldb_yjjxs=isnull(t_yjjxs,1),
           --@ldb_djfxs=isnull(t_djfxs,1),
    @ls_ajdzsj      = ISNULL(CONVERT(VARCHAR(7), t_ajdzsj, 121), '2050-12'),
    @ldb_gssqj_dd   = ISNULL(t_gssqj, 0),
    @ldb_bybcrl     = ISNULL(t_bybcrl, 0),
    @ldb_zk         = ISNULL(t_zjf, 0),
    @li_exch        = ISNULL(t_exch, 0),
    @li_xlh         = ISNULL(t_xlh, 0),
    @li_lsl         = ISNULL(t_lls, 0),
    @li_new         = ISNULL(t_new, 0),
    @ldb_yhq        = ISNULL(t_yhq, 0),
    @li_zfz         = ISNULL(t_cl, 0),
    @li_zzj         = ISNULL(t_zzj, 0),
    @li_080101      = DATEDIFF(DAY, '2008-01-01', t_sdat),
    @li_51          = DATEDIFF(DAY, '2007-04-30', t_sdat),
    @li_416         = DATEDIFF(DAY, '2007-04-12', t_sdat),
    @li_jbzbp       = ISNULL(t_jbzbp, 0),
    @li_yj          = ISNULL(t_yj, 0),
    @ls_item        = ltrim(RTRIM(t_item)),
    @ls_emno        = ltrim(rtrim(t_emno)),
    @ll_payType     = t_pamd,
    @ldt_sendDate   = t_sdat,
    @ldt_adat       = t_adat,
    @ldb_amnt       = t_amnt,
    @ll_rtn         = ISNULL(t_rtn, 0),
    @li_sum         = t_dqua,
    @ldb_prst       = ISNULL(t_prst, 0),
    @li_BackFlag    = ISNULL(t_rtnd, 0),
    @ldb_nopp       = ISNULL(t_nopp, 0),
    @li_jxs         = ISNULL(t_jxs, 0),
    @ldb_xzjbc      = ISNULL(t_xzjbc, 0),
    @ldt_today      = GETDATE(),
    @ls_zfz         = ISNULL(CONVERT(CHAR(7), t_cldat, 121), '2050-01'),
    @ls_zzz         = ISNULL(CONVERT(CHAR(7), t_zzsj, 121), '2050-01'),
    @li_jsb         = ISNULL(t_jsb, 0),
    @li_yjhx        = t_yjhx,
    @ldb_dkr        = t_dkr,
    @li_thh         = ISNULL(t_thh, 0)
    -- into @ldb_yjjxs,@ldb_djfxs,@ls_ajdzsj,@ldb_gssqj_dd,@ldb_bybcrl,@ldb_zk,@li_exch,@li_xlh,@li_lsl,@li_new, @ldb_yhq, @li_zfz,@li_zzj,@li_080101,@li_51,@li_416,
    --@li_jbzbp, @li_yj,@ls_item,@ll_company,@ls_emno,@ll_payType,@ldt_sendDate,@ldt_adat,@ldb_amnt,
    -- @ll_rtn,@li_sum,@ll_corp,@ll_province,@ldb_prst,@li_BackFlag,@ldb_nopp,@li_jxs,@ldb_xzjbc,@ldt_today,
    --@ls_zfz,@ls_zzz,@li_jsb,@li_yjhx,@ldb_dkr,@li_thh
    FROM tbl_sls
    WHERE t_orno = @ll_orno

    SELECT @ls_ajqzsk      = ISNULL(CONVERT(VARCHAR(7), t_ajqzsksj, 121), '2050-12'),
           @ls_pdat        = ISNULL(CONVERT(VARCHAR(7), t_ajdzsj, 121), '2050-12'),
           @ls_yhfdsj      = ISNULL(CONVERT(VARCHAR(7), t_yhfdsj, 121), '2050-12'),
           @li_mort_years  = ISNULL(t_mort_years, 0),
           @li_mort_per    = ISNULL(t_mort_per, 0)
    --into  @ls_ajqzsk,@ls_pdat,@ls_yhfdsj,@li_mort_years,@li_mort_per
    FROM tbl_sls
    WHERE t_orno = @al_orno

    SELECT @ldb_zk         = ISNULL(@ldb_zk, 0)
    SELECT @ldb_bybcrl     = ISNULL(@ldb_bybcrl, 0)
    SET @ldb_zs         = @ldb_prst --赠送配件金额
    SET @ldb_cjj        = 0
    SET @ldb_company    = 0
    SET @ldb_indiv      = 0
    SELECT @ldb_zs         = ISNULL(@ldb_zs, 0)
    SELECT @ldb_nopp       = ISNULL(@ldb_nopp, 0)
    SELECT @ldb_xzjbc      = ISNULL(@ldb_xzjbc, 0)
    SELECT @ldb_prst       = ISNULL(@ldb_prst, 0)

    IF @ls_zfz > @as_ym
      SET @li_zfz = 0

    IF @ls_zzz > @as_ym
      SET @li_zzj = 0

    --ld_sendDate=date(ldt_sendDate)
    SELECT @li_new         = ISNULL(@li_new, 0)

    IF @li_xlh = 1
      BEGIN
        --销售新利恒产品，不计价格奖罚
        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, 2 priz

        RETURN 0
      END

    DECLARE @ldt_ValDate DATETIME
    DECLARE @top_1 INT
    SET @top_1          = 0
    --取得价格奖参数
    SELECT                   TOP 1
                             @top_1          = 1,
                             @ldt_ValDate    = t_udat,
                             @ldb_lpri       = ISNULL(t_lpri, 0),
                             @ldb_comdec     = ISNULL(t_comdec, 0),
                             @ldb_comdec_je  = ISNULL(t_comdec_je, 0),
                             @ldb_nortndec   = ISNULL(t_nortndec, 0),
                             @ldb_nortndec_je = ISNULL(t_nortndec_je, 0),
                             @ldb_am_company_per_a = ISNULL(t_am_company_a, 0),
                             @ldb_am_indiv_per_a = ISNULL(t_am_indiv_a, 0),
                             @ldb_am_company_per_b = ISNULL(t_am_company_b, 0),
                             @ldb_am_indiv_per_b = ISNULL(t_am_indiv_b, 0),
                             @ldb_pz_company_per = ISNULL(t_pz_company, 0),
                             @ldb_pz_indiv_per = ISNULL(t_pz_indiv, 0),
                             @ls_type        = ltrim(rtrim(t_type))
    --into  @ldt_ValDate,           @ldb_lpri,                     @ldb_comdec,                   @ldb_comdec_je,                           @ldb_nortndec,                @ldb_nortndec_je,                                   @ldb_am_company_per_a,                   @ldb_am_indiv_per_a,                 @ldb_am_company_per_b,                                          @ldb_am_indiv_per_b,               @ldb_pz_company_per,             @ldb_pz_indiv_per,@ls_type
    FROM                     tbl_proprize
    WHERE                    t_item = @ls_item AND t_udat <= @ldt_SendDate
    ORDER BY                 t_udat DESC

    IF @top_1 = 0
      BEGIN
        --没有取得相应的T参数
        SET @ls_errorType = '数据错误'
        SET @ls_error     = '没有取得该型号的T参数'
        SET @as_info      = @ls_errorType + '@' + @ls_error

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      (@ls_errorType, @ll_orno, @ls_ym, 2, @ls_error + @as_info);

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

        --commit;
        RETURN -1
      END

    ---hjtok 2012-05-07
    SET @ls_gs1         = @ls_gs1 + '设施类型@' + @ls_type + '.'
    --20080101
    SET @ls_gs1         = @ls_gs1 + ',参数生效日(' + CONVERT(VARCHAR(15), @ldt_ValDate, 112) + ')'

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

    IF @ls_type = '砂浆车'
      BEGIN
        --砂浆车，不计价格奖罚
        SET @ls_errorType = '一般提示'
        SET @as_info      = '砂浆车，不计价格奖罚!'

        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      (@ls_errorType, @ll_orno, @ls_ym, 2, @as_info);

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

        RETURN 0
      END

    --取得发货后一个月内的回款总额。
    SET @ldb_return_sum = 0
    SELECT @ldb_return_sum = SUM(t_rtmn)
    FROM tbl_retamn
    WHERE t_orno = @ll_orno AND t_rdat <= DATEADD(DAY, 30, @ldt_SendDate) AND ISNULL(t_yhfd, 0) = 0
    SELECT @ldb_return_sum = ISNULL(@ldb_return_sum, 0) --then   ldb_return_sum=0
                                                        --datetime ldt_month_end
    --DECLARE @ldt_month_end DATETIME
    --SET @ldt_month_end  = @ldt_month_end
    --截止T月的所有回款
    SELECT @ldb_all_return = SUM(t_rtmn)
    FROM tbl_retamn
    WHERE CONVERT(CHAR(7), t_rdat, 121) <= @ls_ym AND t_orno = @al_orno AND ISNULL(t_yhfd, 0) = 0
    SELECT @ldb_all_return = isnull(@ldb_all_return, 0)
    --取得保护价基数@生效日期在发货日之前的最近一个
    SET @ldb_ppri       = 0
    DECLARE @ldb_tpri NUMERIC(18, 4) --特惠价
    DECLARE @ldb_gssqj NUMERIC(18, 4) --公司授权价
    SELECT             TOP 1
                       @top_1          = 1,
                       @ldb_ppri       = ISNULL(t_ppri, 0),
                       @ldb_tpri       = ISNULL(t_tpri, 0),
                       @ldb_gssqj      = ISNULL(t_gssqj, 0)
    --into @ldb_ppri,@ldb_tpri,@ldb_gssqj
    FROM               tbl_proprice
    WHERE              t_item = @ls_item AND t_udat <= @ldt_SendDate
    ORDER BY           t_udat DESC;

    IF @top_1 = 100
      BEGIN
        --没有相应的保护价数据
        SET @ls_errorType = '数据错误'
        SET @ls_error     = '没有取得该型号的保护价'
        SET @as_info      = @ls_errorType + '@' + @ls_error

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

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

        RETURN -1
      END

    SELECT @ldb_ppri       = ISNULL(@ldb_ppri, 0) -- then ldb_ppri=0
    SELECT @ldb_tpri       = ISNULL(@ldb_tpri, 0) -- then ldb_tpri=0
    SELECT @ldb_gssqj      = ISNULL(@ldb_gssqj, 0) -- then ldb_gssqj=0

    IF @ldb_gssqj_dd > 0
      BEGIN
        SET @ldb_gssqj = @ldb_gssqj_dd
      END

    SET @ls_gs1         = @ls_gs1 + '公司授权价为(' + STR(@ldb_gssqj) + '),特惠价为(' + STR(@ldb_tpri) + '),'

    IF @ldb_tpri < @ldb_gssqj
      BEGIN
        SET @ls_gs1 = @ls_gs1 + '!!特惠价低于公司授权价!'
      END

    --取得所有回款计划的总金额，该金额做为实际的合同金额
    SELECT @ldb_plan_sum   = SUM(t_amnt)
    FROM tbl_backorder
    WHERE t_orno = @ll_orno;
    SELECT @ldb_plan_sum   = ISNULL(@ldb_plan_sum, 0) -- then ldb_plan_sum=0

    IF @ldb_plan_sum = 0
      BEGIN
        SET @ls_errorType = '数据错误'
        SET @ls_error     = '回款计划为零!'
        SET @as_info      = @ls_errorType + '@' + @ls_error

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

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

        RETURN -1
      END

    ---hjtok 2012-05-08
    IF @ldb_plan_sum < @ldb_amnt --计划回款金额低于订单中的合同金额，照提，但是将情况插入日志中
      BEGIN
        SET @ls_errorType  = '一般提示'
        SET @ls_error      = '计划回款总金额(' + STR(@ldb_plan_sum) + ')低于订单合同金额(' + STR(@ldb_amnt) +
                             '),T将正常进行，但是将以计划回款总金额做为实际的合同金额进行计算'

        --将错误信息写入系统日志表中
        INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
        VALUES      (@ls_errorType, @ll_orno, @ls_ym, 2, @ls_error);
      END

    --以计划回款总金额做为实际的合同金额进行计算
    SET @ldb_amnt       = @ldb_plan_sum

    --取得已还贷期数
    DECLARE @ll_hdqs INT
    SET @ll_hdqs        = 0
    SELECT @ll_hdqs        = COUNT(*)
    FROM tbl_backorder
    WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 0 AND ISNULL(t_cash, 0) = 0 AND ISNULL(CONVERT(CHAR(7), t_hdrq, 121), '2050-01')
          <= @as_ym;
    SELECT @ll_hdqs        = ISNULL(@ll_hdqs, 0) -- then ll_hdqs=0

    DECLARE @ll_yqqs INT --取得逾期期数
    SET @ll_yqqs        = 0
    SELECT @ll_yqqs        = COUNT(*)
    FROM tbl_backorder
    WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 0 AND ISNULL(t_cash, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121) <= @as_ym AND
          ISNULL                                                                                                                 (CONVERT(CHAR(7), t_hdrq, 121)
          ,
          '2050-01') > @as_ym;
    SELECT @ll_yqqs        = ISNULL(@ll_yqqs, 0)

    DECLARE @ldb_qkbl NUMERIC(18, 4) --全款比例
    DECLARE @li_jgj INT --是否计算价格奖
    SET @li_jgj         = 1

    IF @ls_type IN ('泵车', '拖泵', '车载泵', '布料机', '布料杆')
      BEGIN
        SELECT 1
      END
    ELSE
      IF @ls_type = '混凝土搅拌站'
        BEGIN
          SELECT 1
        END
      ELSE
        IF @ls_type = '搅拌车'
          BEGIN
            SELECT 1
          END
        ELSE
          IF @ls_type = '沥清搅拌站'
            BEGIN
              --SET @ldb_zk = 0
              SELECT 1
            END
          ELSE
            IF @ls_type = '搅拌站'
              BEGIN
                SELECT 1
              END
            ELSE
              BEGIN
                SELECT 1
              END

    --扣除由公司承担的赠送优惠券、赠送配件、客服费、备用泵车让利等让利后的价格
    SET @ldb_cjj        = @ldb_amnt - (@ldb_zs + @ldb_yhq) * 0.5 - @ldb_zk + @ldb_nopp - @ldb_xzjbc - @ldb_bybcrl --计算出实际价格(可能是多台的总价)
    SET @ls_cjj        =  '实际成交价(' + rtrim(ltrim(STR(@ldb_cjj))) + ')[合同金额(' + rtrim(ltrim(STR(@ldb_amnt))) +
                          ') - 赠送配件(' + STR(@ldb_zs) + ')×0.5 - 优惠券(' + rtrim(ltrim(STR(@ldb_yhq))) +
                          ')×0.5 - 业务费(' + STR(@ldb_zk) + ')+ 不配管金额(' + rtrim(ltrim(STR(@ldb_nopp))) +
                          ')-选装件补差(' + rtrim(ltrim(STR(@ldb_xzjbc))) + ') ' + '-备用泵车让利(' + rtrim(ltrim(STR(@ldb_bybcrl))
                          ) + ')]; '

    IF @ldb_cjj = @ldb_gssqj * @li_sum
      BEGIN
        --不需要提价格奖
        SET @as_info      = '无价格奖,实际成交价(' + rtrim(ltrim(STR(@ldb_cjj))) + ')=公司授权价(' + rtrim(ltrim(STR(@ldb_gssqj * @li_sum))
                            ) + ');' + @ls_cjj + @ls_ppri

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

        RETURN 0
      END

    IF @ldb_cjj > @ldb_gssqj * @li_sum
      BEGIN
        --有价格奖
        --计算价格奖

        IF @li_exch = 1
          BEGIN
            SET @as_info = @as_info + '换房换物抵款订单，不计价格奖'

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

            RETURN 0
          END

        IF @li_zzj = 1 OR @li_zfz = 1
          BEGIN
            SET @as_info = @as_info + '订单转专制或法制，不提价格奖。'

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

            RETURN 0
          END

        IF @li_yj = 1 OR @li_yjhx = 1
          BEGIN
            SET @as_info = @as_info + '优价产品，不提价格奖。'

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

            RETURN 0
          END

        IF @li_thh = 1
          BEGIN
            SET @as_info = @as_info + '退换货、首付款未付清、出现风险的合同，不提价格奖。'

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

            RETURN 0
          END

        IF @ll_payType = 1 -- 分期
          BEGIN
            IF @ls_type = '混凝土搅拌站'
              BEGIN
                SET @ldb_pz_indiv = (@ldb_cjj - @ldb_gssqj * @li_sum) * 0.3
              END
            ELSE --???hjt
              BEGIN
                SET @ldb_pz_indiv = (@ldb_cjj - @ldb_gssqj * @li_sum) * 0.1
              END

            ---???hjt
            SET @ls_gs1      = @ls_gs1 + '价格奖(' + STR(@ldb_pz_indiv) + ')[成交价高于公司授权价(' + STR(@ldb_cjj -
                               @ldb_gssqj * @li_sum) + ')*0.1]'

            IF @ldb_all_return / @ldb_amnt >= 1
              BEGIN
                SET @ldb_all_rate = 1
              END
            ELSE
              BEGIN
                SET @ldb_all_rate = 0
                SET @as_info      = '货款未全部回，不能提取价格奖'
                SET @ls_errorType = '一般提示'

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

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

                --commit;
                RETURN 0
              END
          END
        ELSE
          IF @ll_payType = 6 --全款
            BEGIN
              SET @ldb_pz_indiv = (@ldb_cjj - @ldb_gssqj * @li_sum) * 0.3
              SET @ls_gs1      =  '价格奖(' + STR(@ldb_pz_indiv) + ')[成交价高于公司授权价(' + STR(@ldb_cjj -
                                  @ldb_gssqj * @li_sum) + ')*0.3]'

              IF @ldb_all_return / @ldb_amnt >= 1
                BEGIN
                  SET @ldb_all_rate = 1
                END
              ELSE
                BEGIN
                  SET @ldb_all_rate = 0
                  SET @as_info      = '货款未全部回，不能提取价格奖'
                  SET @ls_errorType = '一般提示'

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

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

                  RETURN 0
                END
            END
          ELSE
            --按揭或融资,
            BEGIN
              SET @ldb_pz_indiv = (@ldb_cjj - @ldb_gssqj * @li_sum) * 0.3
              SET @ls_gs1      =  '价格奖(' + STR(@ldb_pz_indiv) + ')[成交价高于公司授权价(' + STR((@ldb_cjj -
                                  @ldb_gssqj * @li_sum)) + ')*0.3]'

              IF @ll_rtn = 1
                BEGIN
                  --有回购，无回购不需判断这些条件
                  IF @li_BackFlag = 1
                    BEGIN
                      --发生回购
                      SET @as_info      = '发生回购，不能提取价格奖'
                      SET @ls_errorType = '系统信息'

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

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

                      RETURN 0
                    END
                END

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

                          SET @ls_errorType = '一般提示'

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

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

                          RETURN 0
                        END
                    END
                END

              --二年期按揭销售的溢价奖在客户按期还贷6期后计提50％，另50％在客户按期还贷12期后计提；三年及以上期按揭销售的价格奖在客户按期
              --还贷9期后计提50％，另50％在客户按期还贷18期后计提；期间客户有按揭逾期的只有客户还清逾期贷款后才能计提。
              IF @ll_yqqs < 1 --手续办完,无逾期
                BEGIN
                  IF @li_mort_years <= 2
                    BEGIN
                      IF @ll_hdqs >= 12
                        BEGIN
                          SET @ldb_all_rate = 1
                        END
                      ELSE
                        IF @ll_hdqs >= 6
                          BEGIN
                            SET @ldb_all_rate = 0.5
                          END
                        ELSE
                          BEGIN
                            SET @ldb_all_rate = 0
                          END
                    END
                  ELSE
                    BEGIN --???hjt
                      IF @ll_hdqs >= 18
                        BEGIN
                          SET @ldb_all_rate = 1
                        END
                      ELSE
                        IF @ll_hdqs >= 9
                          BEGIN
                            SET @ldb_all_rate = 0.5
                          END
                        ELSE
                          BEGIN
                            SET @ldb_all_rate = 0
                          END
                    END
                END
            END
      END

    IF @ldb_cjj < @ldb_gssqj * @li_sum
      BEGIN
        --价格罚
        --计算价格罚
        IF @li_new = 1
          BEGIN
            SET @as_info = '新产品不计低价罚'

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

            RETURN 0
          END

        IF @li_yj = 1 OR @li_jsb = 1
          BEGIN
            --优价产品或旧设备，不计提低价罚款
            SET @as_info = '优价产品，不计提低价罚款'

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

            RETURN 0
          END

        IF @ll_payType = 1
          BEGIN
            -- 分期
            SET @ldb_all_rate = 1 --???hjt
            SET @ldb_am_indv  = (@ldb_gssqj * @li_sum - @ldb_cjj) * 0.15
            SET @ls_gs1       = @ls_gs1 + '低价罚(' + STR(@ldb_am_indv) + ')[差价(' + STR(@ldb_gssqj * @li_sum - @ldb_cjj) +
                                ')*0.15];'

            SET @ldb_indiv    = -@ldb_am_indv
          END
        ELSE
          --其他
          BEGIN
            SET @ldb_am_indv  = (@ldb_gssqj * @li_sum - @ldb_cjj) * 0.05
            SET @ls_gs1       = @ls_gs1 + '低价罚(' + STR(@ldb_am_indv) + ')[差价(' + STR(@ldb_gssqj * @li_sum - @ldb_cjj) +
                                ')*0.05];'

            SET @ldb_indiv    = -@ldb_am_indv

            IF @li_zzj = 0 AND @li_zfz = 0
              BEGIN
                IF @ls_pdat > @as_ym OR @ls_yhfdsj > @as_ym OR @ls_ajqzsk > @as_ym OR @ls_ajdzsj > @as_ym
                  BEGIN
                    SET @as_info      = '按揭费用未到账或者银行未放贷或权证未受控，不能提取价格罚'

                    SET @ls_errorType = '一般提示'

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

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

                    --commit;
                    RETURN 0
                  END
                ELSE
                  BEGIN
                    SET @ldb_all_rate = 1 --未转专制或法制，但是相关条件全部达到
                  END
              END

            SET @ldb_all_rate = 1 --转专制或转法制，
          END
      END

    SET @ldb_rate       = @ldb_all_rate - @ldb_have_rate --本次T的比率
    SET @ls_gs1         = @ls_gs1 + '本次T比率(' + STR(@ldb_rate) + ');'
    SET @ldb_indiv      = @ldb_indiv * @ldb_rate

    DECLARE
      @ll_total     INT,
      @ll_emno222   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),
      @ldb_PrizeSum NUMERIC(18, 4),
      @ldb_5        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)
               )

    --保存价格罚
    IF @ldb_indiv < 0
      BEGIN
        --
        DECLARE @ls_yhfdy VARCHAR(50)
        --获取T分配比例
        SET @ll_total     = 0
        SET @ll           = 0
        --有低价罚。
        --计算该定单所提取的所有奖项
        SELECT @ldb_PrizeSum = SUM(t_amnt)
        FROM tbl_priseresult
        WHERE t_orno = @al_orno AND t_priz <> 1;

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

        INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user, t_cttimes)
        VALUES      (@ll_orno, @as_ctym, '1000000', 7, @ldb_indiv * 0.15, @ls_gs1 + +@ls_gs2, '', @gs_userName, @ai_cttimes); --公司部分销代表工号设为1000000

        SET @ldb_lpri     = 0
        OPEN tbl_assign_Cursor

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

          --???hjt tbl_assign 中province 的类型
          IF @@fetch_status <> 0
            BREAK

          SET @ll_total = @ll_total + 1
          SET @ls_gs2   = 'T分配，获取' + STR(@ldb_pre) + ';'

          INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_diff, t_gs, t_gs2, t_user, t_cttimes,
                        t_ratio       )
          VALUES      (@ll_orno, @as_ctym, @ls_emno2, 7, @ldb_indiv * @ldb_pre * 0.85, @ldb_PrizeDiffer * @ldb_pre,
                       @ls_gs1 + @ls_gs2, '', @gs_userName, @ai_cttimes, @ldb_rate * @ldb_pre * 0.85);
        --commit;
        END

        CLOSE tbl_assign_Cursor
        DEALLOCATE tbl_assign_Cursor

        --destroy ldts_assign
        IF @ll_total = 0
          BEGIN
            INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_diff, t_gs, t_gs2, t_user, t_cttimes,
                          t_ratio       )
            VALUES      (@ll_orno, @as_ctym, @ls_emno, 7, @ldb_indiv * 0.85, @ldb_PrizeDiffer, @ls_gs1 + @ls_cjj + @ls_ppri,
                         @ls_gs1 + @ls_gs2 + @ls_cjj + @ls_ppri, @gs_userName, @ai_cttimes, @ldb_rate * @ldb_pre * 0.85);
          END
      END

    --将T结果插入数据库中，并设置相应的标志位
    --获取T分配比例
    SET @ldb_indiv      = @ldb_pz_indiv
    --ldts_assign = Create DataStore
    --ldts_assign.DataObject ='d_assign_3'
    --ldts_assign.settransobject(sqlca)
    --ldts_assign.retrieve(al_orno,ls_ym)
    SET @ll_total       = 0
    SET @ll             = 0

    IF @ldb_pz_indiv > 0
      BEGIN
        SET @ldb_indiv = @ldb_pz_indiv
        --2008,有价格奖
        DECLARE @ll_id INT

        INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user, t_cttimes)
        VALUES      (@ll_orno, @as_ctym, '1000000', 2, @ldb_indiv * @ldb_pre * 0.15, @ls_gs1 + @ls_gs2, '', @gs_userName,
                     @ai_cttimes); --公司部分销代表工号设为1000000

        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_gs2   = @ls_gs2 + 'T分配，获取' + STR(@ldb_pre) + ';'

          --commit;
          INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_diff, t_gs, t_gs2, t_user, t_cttimes)
          VALUES      (@ll_orno, @as_ctym, @ls_emno2, 2, @ldb_indiv * @ldb_pre * 0.85, @ldb_prizediffer * @ldb_pre,
                       @ls_gs1 + @ls_gs2, '', @gs_userName, @ai_cttimes);
        --commit;
        END

        CLOSE tbl_assign_Cursor
        DEALLOCATE tbl_assign_Cursor

        --destroy ldts_assign
        IF @ll_total = 0
          BEGIN
            INSERT INTO tbl_priseresult(t_orno, t_yymm, t_crep, t_priz, t_amnt, t_diff, t_gs, t_gs2, t_user, t_cttimes)
            VALUES      (@ll_orno, @as_ctym, @ls_emno, 2, @ldb_indiv * 0.85, @ldb_prizediffer, @ls_gs1 + @ls_gs2, '', @gs_userName,
                         @ai_cttimes);
          --commit;
          END
      END

    SET @as_prise       = @ldb_indiv + @ldb_company
    SET @as_info        = @ls_gs1 + @ls_gs2 + @ls_cjj + @ls_ppri

    SELECT @as_info AS info, 1 AS rtn, 2 priz

    RETURN (1)
  -----------------------------------------------------------
  END
GO