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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/75527/viewspace-730650/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/75527/viewspace-730650/