pro_update_mort

最新推荐文章于 2022-07-04 10:10:59 发布

congtu9141

最新推荐文章于 2022-07-04 10:10:59 发布

阅读量210

点赞数

ALTER PROCEDURE pro_update_mort
AS
BEGIN
    DECLARE
      @ll           INT,
      @ll_rtn       INT,
      @ll_orno      INT, --in
      @ll_sls_orno CHAR(15),
      @ll_mort_per INT, --in
      @ll_mort_year INT --in
      @ll_aqhd int
    DECLARE
      @ll_yq INT,
      @ll_row INT,
      @ll2    INT
    DECLARE
      @ll_ppas INT,
      @ll_ajdz INT,
      @ll_error INT,
      @ll_bqhds INT
    DECLARE
      @ls_jkr         NVARCHAR(2), --in
      @ls_deal_result NVARCHAR(200),
      @ls_ym          CHAR(7),
      @ls_yq          CHAR(20),
      @ls_ppas        CHAR(7)
    DECLARE
      @ls_ajdz CHAR(7),
      @ls_qzsk CHAR(7)
    DECLARE
      @ldb_ajje DECIMAL(18, 4), --in
      @ldb_wfdje DECIMAL(18, 4) --in
      @ldc_amnt DECIMAL(18, 4)
    DECLARE
      @ldt_yhfdsj    DATETIME, --in
      @ldt_hdsj      DATETIME, --in
      @ldt_have_hdsj DATETIME,
      @ldt_pdat      DATETIME,
      @ldt_ajdzsj    DATETIME,
      @ldt_qzsksj    DATETIME
      @ldt_sdat DATETIME
    SET @ls_deal_result = ''
    SET @ll_rtn         = 0
    --dw_2.settransobject(sqlca)
    --int suc=0
    --int count=0 //add by wangq11 2010-12-17

    -- for ll=1 to dw_1.rowcount()
    -- yield()
    -- ll_error=0
    -- setnull(ls_yq)
    -- setnull(ll_yq)
    -- setnull(ls_jkr)
    -- ls_deal_result=""
    -- dw_1.selectrow(0,false)
    -- dw_1.selectrow(ll,true)
    -- dw_1.scrolltorow(ll)
    -- ls_ym=trim(dw_1.getitemstring(ll,"f_yymm"))
    -- ldt_hdsj=datetime(date(ls_ym+"-15"))--in
    -- ls_ym=string(ldt_hdsj,"yyyy-mm")
    -- ll_orno=dw_1.getitemnumber(ll,"t_orno") --in
    SET @ll_sls_orno    = 0
    SELECT @ll_sls_orno    = t_orno
    FROM tbl_sls
    WHERE t_orno = @ll_orno
    SELECT @ll_sls_orno    = isnull(@ll_sls_orno, 0)

    IF @ll_orno <> @ll_sls_orno
      BEGIN
        SET @ls_deal_result = @ls_deal_result + '订单号不存在!'

SELECT -1 rtn, @ls_deal_result info

RETURN
END

    --更新借款人
    IF @ls_jkr <> ''
      BEGIN
        UPDATE tbl_sls
        SET    t_jkr = @ls_jkr
        WHERE t_orno = @ll_orno

SET @ls_deal_result = @ls_deal_result + '更新借款人为(' + @ls_jkr + ').'
END

    --       //更新按揭金额
    --       setnull(ldb_ajje)
    --       ldb_ajje=dw_1.getitemnumber(ll,"t_ajje")
    IF ldb_ajje > 0
      BEGIN
        UPDATE tbl_sls
        SET    t_ajje = @ldb_ajje
        WHERE t_orno = @ll_orno;

SET @ls_deal_result = @ls_deal_result + '更新借款金额为(' + dbo.tostr(@ldb_ajje) + ').'
END

    --       end if
    --       ldb_wfdje=0
    --       ldb_wfdje=dw_1.getitemnumber(ll,"t_wfdje")
    --       if isnull(ldb_wfdje) then ldb_wfdje=0
    DELETE FROM tbl_wfdjr
    WHERE       t_orno = @ll_orno AND CONVERT(CHAR(7), t_yymm, 121) = @ls_ym;

    IF @ldb_wfdje > 0
      BEGIN
        INSERT tbl_wfdjr(t_orno, t_yymm, t_wfdjr)
        VALUES (@ll_orno, @ldt_hdsj, @ldb_wfdje * 10000)

SET @ls_deal_result = @ls_deal_result + '更新未放款金额为(' + dbo.tostr(@ldb_wfdje) + ').'
END

    --
    --       //更新银行放贷时间
    --       setnull(ldt_yhfdsj)
    --       ldt_yhfdsj=dw_1.getitemdatetime(ll,"t_yhfdsj")
    DECLARE @ldt_check DATETIME
    SET @ldt_check      = NULL
    SELECT @ldt_check      = t_yhfdsj
    FROM tbl_sls
    WHERE t_orno = @ll_orno

    IF @ldt_yhfdsj IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_yhfdsj = @ldt_yhfdsj
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_yhfd = 1
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将银行放贷时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')更新为(' + CONVERT(CHAR(10), @ldt_yhfdsj, 121) + ').'
            SET @ll_rtn              = @ll_rtn + 1
          END
        ELSE
          BEGIN
            SET @ls_deal_result = @ls_deal_result + '更新银行放货时间为(' + CONVERT(CHAR(10), @ldt_yhfdsj, 121) + ').'
          END
      END
    ELSE
      BEGIN
        UPDATE tbl_sls
        SET    t_yhfdsj = NULL
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_yhfd = 0
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将银行放贷时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')清除为空,请确认是否正确。'
            SET @ll_rtn              = @ll_rtn + 1
          END
      END

    --       //按揭成数
    IF @ll_mort_per IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_mort_per = @ll_mort_per
        WHERE t_orno = @ll_orno

SET @ls_deal_result = @ls_deal_result + '更新按揭成数为(' + dbo.tostr(@ll_mort_per) + ').'
END

-- //按揭年数

    IF @ll_mort_per IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_mort_years = @ll_mort_year
        WHERE t_orno = @ll_orno

SET @ls_deal_result = @ls_deal_result + '更新按揭年数为(' + str(@ll_mort_year) + ').'
END

    --       end if
    --       // //办完手续
    --       // setnull(ls_ppas)
    --       // ls_ppas=dw_1.getitemstring(ll,"t_ppas")
    --       //更新手续办完时间
    --       setnull(ldt_pdat)
    --       ldt_pdat=dw_1.getitemdatetime(ll,"t_pdat")
    --       // if not isnull(ls_ppas) and ls_ppas="是" then
    --       if not isnull(ldt_pdat) then
    --       update tbl_sls set t_ppas=1 where t_orno=:ll_orno;
    --       update tbl_sls set t_pdat=:ldt_pdat where t_orno=:ll_orno;
    --       commit;
    --       ls_deal_result=ls_deal_result+"更新办完手续."
    --       ls_deal_result=ls_deal_result+"更新手续办完时间为("+string(ldt_pdat,"yyyy-mm-dd")+")."
    --       else
    --       update tbl_sls set t_ppas=0 where t_orno=:ll_orno;
    --       update tbl_sls set t_pdat=null where t_orno=:ll_orno;
    --       commit;
    --       end if
    --       // end if
    SET @ldt_check      = NULL
    SELECT @ldt_check      = t_pdat
    FROM tbl_sls
    WHERE t_orno = @ll_orno

    IF @ldt_pdat IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_pdat = @ldt_pdat
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ppas = 1
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将手续办完时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')更新为(' + CONVERT(CHAR(10), @ldt_pdat, 121) + ').'
            SET @ll_rtn              = @ll_rtn + 1
          END
        ELSE
          BEGIN
            SET @ls_deal_result = @ls_deal_result + '更新手续办完时间为(' + CONVERT(CHAR(10), @ldt_pdat, 121) + ').'
          END
      END
    ELSE
      BEGIN
        UPDATE tbl_sls
        SET    t_pdat = NULL
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ppas = 0
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将手续办完时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')清除为空,请确认是否正确。'
            SET @ll_rtn              = @ll_rtn + 1
          END
      END

    --
    --       //更新按揭权证受控
    --       // setnull(ls_qzsk)
    --       // ls_qzsk=dw_1.getitemstring(ll,"t_qzsk")
    --       //更新权证受控时间
    --       setnull(ldt_qzsksj)
    --       ldt_qzsksj=dw_1.getitemdatetime(ll,"t_qzsksj")
    --       // if not isnull(ls_qzsk) and ls_qzsk="是" then
    --       if not isnull(ldt_qzsksj) then
    --       update tbl_sls set t_ajqzsk=1 where t_orno=:ll_orno;
    --       update tbl_sls set t_ajqzsksj=:ldt_qzsksj where t_orno=:ll_orno;
    --       commit;
    --       ls_deal_result=ls_deal_result+"更新按揭权证受控标记."
    --       ls_deal_result=ls_deal_result+"更新权证受控时间为("+string(ldt_qzsksj,"yyyy-mm-dd")+")."
    --       // commit;
    --       else
    --       update tbl_sls set t_ajqzsk=0 where t_orno=:ll_orno;
    --       update tbl_sls set t_ajqzsksj=null where t_orno=:ll_orno;
    --       commit;
    --       end if
    --       // end if
    SET @ldt_check      = NULL
    SELECT @ldt_check      = t_ajqzsksj
    FROM tbl_sls
    WHERE t_orno = @ll_orno

    IF @ldt_qzsksj IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_ajqzsksj = @ldt_qzsksj
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ajqzsk = 1
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将权证受控时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')更新为(' + CONVERT(CHAR(10), @ldt_qzsksj, 121) + ').'
            SET @ll_rtn              = @ll_rtn + 1
          END
        ELSE
          BEGIN
            SET @ls_deal_result = @ls_deal_result + '更新权证受控时间为(' + CONVERT(CHAR(10), @ldt_qzsksj, 121) + ').'
          END
      END
    ELSE
      BEGIN
        UPDATE tbl_sls
        SET    t_ajqzsksj = NULL
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ajqzsk = 0
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将权证受控时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')清除为空,请确认是否正确。'
            SET @ll_rtn              = @ll_rtn + 1
          END
      END

    --
    --       // //按揭到账
    --       // setnull(ls_ajdz)
    --       // ls_ajdz=dw_1.getitemstring(ll,"t_ajdz")
    --
    --
    --       date a
    --       //更新按揭到账时间
    --       setnull(ldt_ajdzsj)
    --       ldt_ajdzsj=dw_1.getitemdatetime(ll,"t_ajdzsj")
    --       if not isnull(ldt_ajdzsj) then
    --       update tbl_sls set t_ajdzsj=:ldt_ajdzsj where t_orno=:ll_orno;
    --       update tbl_sls set t_ajdz=1 where t_orno=:ll_orno;
    --       commit;
    --       ls_deal_result=ls_deal_result+"更新按揭到账标志."
    --       ls_deal_result=ls_deal_result+"更新按揭到账时间为("+string(ldt_ajdzsj,"yyyy-mm-dd")+")."
    --       else
    --       update tbl_sls set t_ajdzsj=null where t_orno=:ll_orno;
    --       update tbl_sls set t_ajdz=0 where t_orno=:ll_orno;
    --       commit;
    --       end if

    SET @ldt_check      = NULL
    SELECT @ldt_check      = t_ajdzsj
    FROM tbl_sls
    WHERE t_orno = @ll_orno

    IF @ldt_ajdzsj IS NOT NULL
      BEGIN
        UPDATE tbl_sls
        SET    t_ajdzsj = @ldt_ajdzsj
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ajdz = 1
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将按揭到账时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')更新为(' + CONVERT(CHAR(10), @ldt_ajdzsj, 121) + ').'
            SET @ll_rtn              = @ll_rtn + 1
          END
        ELSE
          BEGIN
            SET @ls_deal_result = @ls_deal_result + '更新按揭到账时间为(' + CONVERT(CHAR(10), @ldt_ajdzsj, 121) + ').'
          END
      END
    ELSE
      BEGIN
        UPDATE tbl_sls
        SET    t_ajdzsj = NULL
        WHERE t_orno = @ll_orno

        UPDATE tbl_sls
        SET    t_ajdz = 0
        WHERE t_orno = @ll_orno

        IF @ldt_check IS NOT NULL
          BEGIN
            SET @ls_deal_result      = @ls_deal_result + '将按揭到账时间由(' + CONVERT(CHAR(10), @ldt_check, 121) +
                                       ')清除为空,请确认是否正确。'
            SET @ll_rtn              = @ll_rtn + 1
          END
      END

    --       // dw_1.setitem(ll,"t_deal_result",ls_deal_result)
    --       // dw_1.accepttext()
    --       //逾期
    --       setnull(ls_yq)
    --       ls_yq=trim(dw_1.getitemstring(ll,"t_yq"))
    --       if isnumber(ls_yq) or ls_yq="" then
    --         if isnull(ls_yq) or ls_yq="" then
    --       ll_yq=0
    --       else
    --       ll_yq=long(ls_yq)
    --       end if
    --       end if

    --       if isnull(ls_yq) then
    --       setnull(ll_yq)
    --       ls_deal_result=ls_deal_result+"逾期期数为空，默认不处理还贷情况，可能会有已还贷未标明，如已还贷，请注明逾期期数为0."
    --       end if
    IF @ll_yh IS NULL
      BEGIN
        SET @ls_deal_result      = @ls_deal_result +
                                   '逾期期数为空，默认不处理还贷情况，可能会有已还贷未标注，如已还贷，请注明逾期期数为0.'
        SET @ll_rtn              = @ll_rtn + 1
      END

    DECLARE
      cur_mort CURSOR FOR
        SELECT   t_sdat, t_hdrq, t_aqhd, t_amnt
        FROM     tbl_backorder
        WHERE    NOT (isnull(t_first, 0) = 1 OR isnull(t_cash, 0) = 1) AND t_orno = @ll_orno AND CONVERT(CHAR(7), t_sdat, 121) <=
                 @ls_ym
        ORDER BY t_sdat DESC

OPEN cur_mort

    WHILE 1 = 1
    BEGIN
      FETCH cur_mort
      INTO @ldt_sdat, @ldt_hdrq, @ll_aqhd, @ldc_amnt

IF @@fetch_status <> 0
BREAK

      UPDATE tbl_backorder
      SET    t_hdrq = getdate()
      WHERE t_orno = @ll_orno AND t_sdat = @ldt_sdat AND t_amnt = @ldc_amnt
    END
--       if ll_yq>=0 then
--       ll_bqhds=0
--       dw_2.reset()
--       ll_row=dw_2.retrieve(ll_orno,ls_ym)
--       if not ll_row>0 then
--       ls_deal_result=ls_deal_result+"没有截止"+ls_ym+"还贷计划!"
--       end if
--       if ll_yq=0 and ll_row>0 then //没有逾期
--       ls_deal_result=ls_deal_result+"没有逾期还贷!"
--       for ll2=1 to ll_row
--       count=count+1
--       ldt_have_hdsj=dw_2.getitemdatetime(ll2,"t_hdrq")
--       /此处代码被注释，还贷计划导不进去。取消注释
--         if isnull(ldt_have_hdsj) then
--         dw_2.setitem(ll2,"t_hdrq",ldt_hdsj)
--         dw_2.setitem(ll2,"t_aqhd",1)
--       ll_bqhds=ll_bqhds+1
--         end if
--               ///by wangq11 2010-08-19
--       next
--       end if
--       if ll_yq>0 and ll_row>0 then //有逾期
--       if ll_row --       ls_deal_result=ls_deal_result+"还贷计划期数少于逾期期数!"
--       ll_error=1
--       else
--       for ll2=1 to ll_row
--       ldt_have_hdsj=dw_2.getitemdatetime(ll2,"t_hdrq")
--       // messagebox("ldt_have_hdsj",string(ldt_have_hdsj))
--       if ll2>ll_yq then //已还款
--       ldt_have_hdsj=dw_2.getitemdatetime(ll2,"t_hdrq")
--       if isnull(ldt_have_hdsj) then
--       dw_2.setitem(ll2,"t_hdrq",ldt_hdsj)
--       dw_2.setitem(ll2,"t_aqhd",1)
--       ll_bqhds=ll_bqhds+1
--       end if //取消注释2010-12-18 wangq11
--       else//逾期款
--       ldt_have_hdsj=dw_2.getitemdatetime(ll2,"t_hdrq")
--       if not isnull(ldt_have_hdsj) then
--       ls_deal_result=ls_deal_result+"逾期还贷款已被标为已还贷!"
--       ll_error=1
--       end if
--       end if
--
--
--       next
--       end if
--       end if
--
--           suc=suc+1
--       dw_1.accepttext()
--       dw_2.update()
--       commit;
--       end if
--       if ll_bqhds>0 then
--       ls_deal_result=ls_deal_result+"本次还贷期数("+string(ll_bqhds)+")."
--       end if
--       dw_1.setitem(ll,"t_deal_result",ls_deal_result)
--       dw_1.setitem(ll,"error",ll_error)
--       dw_1.accepttext()
--       next
--
--
--       //messagebox("提示","导入 "+string(suc)+"条记录，共有"+string(count)+"行数据未导入！")
--       dw_1.selectrow(0,false)
--
END