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

-- =============================================
-- Author:  Sidney
-- ALTER  date: 2010-10-16
-- Description: 预提,奖项设置为9(其他)
-- =============================================
ALTER PROCEDURE [dbo].[ProceYT]
  @al_orno INT, @as_ym VARCHAR(7), @as_ctym VARCHAR(7), @as_user VARCHAR(20), @ai_cttimes INT
AS
  BEGIN
    DECLARE @ldc_yt_have NUMERIC(18, 4)
    DECLARE @ldc_yt NUMERIC(18, 4)
    DECLARE @ldc_prise_have NUMERIC(18, 4)
    DECLARE @as_info VARCHAR(500)
    DECLARE @ls_emno VARCHAR(20)
    DECLARE @li_assign INT
    DECLARE @ldc_pre NUMERIC(18, 4)
    DECLARE @ls_emno2 VARCHAR(20)
    DECLARE @ai_sum INT
    SET @as_info        = ''

    IF isnull(@as_ctym, '') = ''
      SET @as_ctym = @as_ym

    -----------------------------------------------------
    SELECT @ldc_yt         = isnull(t_ytje, 0),
           @ls_emno        = rtrim(t_emno)
    FROM tbl_sls
    WHERE t_orno = @al_orno

    IF @ldc_yt IS NULL
      SET @ldc_yt = 0

    IF NOT @ldc_yt > 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, 9, @as_info)

        SELECT @as_info AS info, 0.00 AS rtn, 9 priz

        RETURN 0
      END

    ------------------------------------------???t_priz=7
    SELECT @ldc_yt_have    = sum(t_amnt)
    FROM tbl_priseresult
    WHERE t_orno = @al_orno AND t_cttimes = @ai_cttimes AND t_priz = 9 AND t_flag1 = 8

    IF @ldc_yt_have IS NULL
      SET @ldc_yt_have = 0

    SELECT @ldc_prise_have = sum(t_amnt)
    FROM tbl_priseresult
    WHERE t_orno = @al_orno

    IF @ldc_prise_have IS NULL
      SET @ldc_prise_have = 0

    IF @ldc_prise_have < @ldc_yt
      BEGIN
        SET @ldc_yt       = @ldc_yt - @ldc_prise_have
        SET @as_info      = '目前已经产生提奖(' + rtrim(ltrim(str(@ldc_prise_have))) + '),本次预提(' + rtrim(ltrim(str(@ldc_yt))
                            ) + ')'
      END
    ELSE
      BEGIN
        SET @as_info      = @as_info + '已提奖金额（' + rtrim(ltrim(str(@ldc_prise_have))) + ')大于预提金额(' + rtrim(ltrim(str(@ldc_yt))
                            ) + '),不需预提!'

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

        SELECT @as_info AS info, 0.00 AS rtn, 9 priz

        RETURN 0
      END

    -----------------------------------------
    IF @ldc_yt > 0 AND NOT @ldc_yt_have > 0
      BEGIN
        ----------------
        SELECT @li_assign = count(*)
        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 @li_assign IS NULL
          SET @li_assign = 0

        IF @li_assign > 0
          BEGIN
            DECLARE
              tbl_assign_Cursor CURSOR FOR SELECT rtrim(ltrim(fld_emno)), isnull(fld_pre, 0)
                                           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)
                                                  )

            OPEN tbl_assign_Cursor

            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '100000', 9, @ldc_yt * 0.15, @as_info + ',分公司部分(15%)', '',
                         @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '100000', @as_ctym, @as_ym, 9, 0, -@ldc_yt * 0.15, @ai_cttimes, getdate(),
                         '分公司备抵部分(15%)')

            FETCH NEXT FROM tbl_assign_Cursor
            INTO @ls_emno2, @ldc_pre

            WHILE @@FETCH_STATUS = 0
            BEGIN
              INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                            t_cttimes     )
              VALUES      (getdate(), 8, @al_orno, @as_ctym, @ls_emno2, 9, @ldc_yt * 0.85 * @ldc_pre,
                           @as_info + '，个人部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')', '', @as_user, @ai_cttimes)

              INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
              VALUES      (@al_orno, @ls_emno2, @as_ctym, @as_ym, 9, 0, -@ldc_yt * 0.85 * @ldc_pre, @ai_cttimes, getdate(),
                           '个人备抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')')

              FETCH NEXT FROM tbl_assign_Cursor
              INTO @ls_emno2, @ldc_pre
            END

            CLOSE tbl_assign_Cursor
            DEALLOCATE tbl_assign_Cursor
          END
        ELSE
          BEGIN
            -- insert into tbl_priseresult
            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '100000', 9, @ldc_yt * 0.15, @as_info + ',分公司部分(15%)', '',
                         @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '100000', @as_ctym, @as_ym, 9, 0, -@ldc_yt * 0.15, @ai_cttimes, getdate(),
                         '分公司备抵部分(15%)')

            ---个人部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, @ls_emno, 9, @ldc_yt * 0.85, @as_info + '，个人部分(85%)', '',
                         @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, @ls_emno, @as_ctym, @as_ym, 9, 0, -@ldc_yt * 0.85, @ai_cttimes, getdate(),
                         '个人备抵部分(85%)')
          END

        SELECT @as_info AS info, @ldc_yt AS rtn, 9 priz
      END
    ELSE
      BEGIN
        SET @as_info = @as_info + '，已提奖金额已超过计划提前兑付金额。'

        SELECT @as_info AS info, 0 AS rtn, 9 priz
      END
  /*
  DECLARE t_user_Cursor CURSOR FOR
  SELECT f_xm,f_zh
  FROM t_user
  OPEN t_user_Cursor
  FETCH NEXT FROM t_user_Cursor
  into @ls_xm,@ls_zh
  WHILE @@FETCH_STATUS = 0
  BEGIN
      print @ls_xm
      print @ls_zh
      FETCH NEXT  FROM t_user_Cursor into @ls_xm,@ls_zh
  END

  CLOSE t_user_Cursor
  DEALLOCATE t_user_Cursor
  */

  /*select @as_info=f_xm,@ai_sum=1  from t_user;
  select @as_info=
  case

     when @al_flag=1 then  '111111'
     when @al_flag=2 then  '222222'
     else '00000'
  end ,
  @ai_sum=
  case

     when @al_flag=1 then  1
     when @al_flag=2 then  2
     else 0
  end

  select @as_info as f_xm,@ai_sum as sum
  select @ai_sum=count(*) from t_user
  */
  --union
  /*
  if @al_flag=1
  begin
   select '111111' as f_xm,1 as sum
   return 0
  end
  else if @al_flag=2
  begin
   select '22222' as f_xm,2 as sum
   return 0
  end
  else
  begin
   select '00000' as f_xm,0 as sum
   return 0
  end
     */
  END
GO