- 论坛徽章:
- 0
|
关于数据库日志的问题
//建临时表
isJejzTable="#CHJEJZ"+userid
isSlTable="#CHSLJZ"+userid
isHead="#DJHEAD"+userid
isBody="#DJBODY"+userid
Sqlca.autocommit=TRUE
vsSql="drop table "+isJejzTable
EXECUTE IMMEDIATE :vsSql;
vsSql="create table "+isJejzTable+" "+&
" (F_CLNM char(9) null,"+&
" F_JE u001 default 0 null,"+&
" F_JHJE u001 default 0 null) "
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
setmicrohelp("创建代入库金额临时表出错:"+Sqlca.SqlErrtext)
vsSql="drop table "+isJejzTable
EXECUTE IMMEDIATE :vsSql;
close(this)
Sqlca.autocommit=false
return
END IF
vsSql="drop table "+isSlTable
EXECUTE IMMEDIATE :vsSql;
vsSql="create table "+isSlTable+" "+&
" (F_CKNM char(9) null,"+&
" F_CLNM char(9) null,"+&
" F_SL u001 default 0 null) "
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
setmicrohelp("创建入库数量临时表出错:"+Sqlca.SqlErrtext)
vsSql="drop table "+isSlTable
EXECUTE IMMEDIATE :vsSql;
close(this)
Sqlca.autocommit=false
return
END IF
vsSql="drop table "+isHead
EXECUTE IMMEDIATE :vsSql;
vsSql="create table "+isHead+"("+&
"F_DATE char( not null,"+&
"F_DJBH varchar(20) not null,"+&
"F_CKNM char(9) null)"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
setmicrohelp("创建单据临时表出错:"+Sqlca.SqlErrtext)
vsSql="drop table "+isHead
EXECUTE IMMEDIATE :vsSql;
close(this)
Sqlca.autocommit=false
return
END IF
vsSql="drop table "+isBody
EXECUTE IMMEDIATE :vsSql;
vsSql="create table "+isBody+" ("+&
"F_DJBH varchar(20) not null,"+&
"F_CLNM char(9) not null,"+&
"F_SL u001 DEFAULT 0 not null,"+&
"F_JHJE u001 DEFAULT 0 not null,"+&
"F_JE u001 DEFAULT 0 not null)"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
setmicrohelp("创建单据临时表出错:"+Sqlca.SqlErrtext)
vsSql="drop table "+isBody
EXECUTE IMMEDIATE :vsSql;
close(this)
Sqlca.autocommit=false
return
END IF
Sqlca.autocommit=false
//记帐
string vsSql,vsMsg,vsdate,vsclnm,vsclbh,vsjs,vsCknm
string ls_clnm
int i,viJs,viLen
long vljhje,ld_thisnum,llcnt
setPointer(HourGlass!)
//出库单
sle_1.text = '出库单记帐 . . .'
uo_1.uf_set_Position(0)
//形成临时表的数据
vsSql="trancate table "+isJejzTable
EXECUTE IMMEDIATE :vsSql;
vsSql="trancate table "+isSlTable
EXECUTE IMMEDIATE :vsSql;
vsSql="trancate table "+isHead
EXECUTE IMMEDIATE :vsSql;
vsSql="trancate table "+isBody
EXECUTE IMMEDIATE :vsSql;
uo_1.uf_set_Position(2)
Sqlca.autocommit=FALSE
//把未记帐的出库单插入临时出库单中
vsSql="INSERT "+isHead+"("+&
" F_DATE,F_DJBH,F_CKNM )"+&
" SELECT F_DATE,"+&
" F_CKDH,"+&
" F_CKNM"+&
" FROM GYCKD1"+&
" WHERE F_DATE LIKE '"+lsYear+lsMon+"%' "+&
" AND F_SH = 1 AND F_JZ=0 "
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.SqlCode= -1 THEN
vsMsg="1形成出库金额数据出错"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
uo_1.uf_set_Position(5)
//形成出库数量临时表数据
vsSql="INSERT "+isBody+"("+&
" F_DJBH,F_CLNM,F_SL,F_JHJE,F_JE)"+&
" SELECT GYCKD2.F_CKDH,GYCKD2.F_CLNM,GYCKD2.F_SL,"+&
" GYCKD2.F_JHJE,GYCKD2.F_JE"+&
" FROM GYCKD2,"+isHead+&
" WHERE "+isHead+".F_DJBH=GYCKD2.F_CKDH"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="2正在形成出库数量:"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
uo_1.uf_set_Position(10)
//形成出库单临时金额记帐表数据
vsSql="INSERT "+isJeJzTable+&
" (F_CLNM,F_JE,F_JHJE) "+&
"SELECT F_CLNM,"+&
" ROUND(SUM(F_JE),"+string(giJedecn)+", "+&
" ROUND(SUM(F_JHJE),"+string(giJedecn)+" "+&
" FROM "+isBody+&
" GROUP BY F_CLNM"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="3形成出库金额出错:"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
uo_1.uf_set_Position(1
//形成出库单临时数量记帐表数据
vsSql="INSERT "+isSlTable+&
" (F_CKNM,F_CLNM,F_SL) "+&
"SELECT "+isHead+".F_CKNM,"+&
isBody+".F_CLNM,"+&
" ROUND(SUM("+isBody+".F_SL),"+string(giSldecn)+" "+&
" FROM "+isHead+","+isBody+&
" WHERE "+isHead+".F_DJBH="+isBody+".F_DJBH "+&
" GROUP BY "+isHead+".F_CKNM,"+isBody+".F_CLNM"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="4正在形成出库数量:"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
//登记数量帐
vsSql="select F_CKNM,F_CLNM,F_SL from "+isSlTable
DECLARE cur_sl DYNAMIC CURSOR FOR sqlsa;
PREPARE sqlsa FROM :vsSql;
OPEN DYNAMIC cur_sl;
FETCH cur_sl INTO :vsCknm,:vsclnm,:vljhje;
//把临时数量记帐表中的数据更新到帐面上
DO WHILE sqlca.sqlcode<>;100
SELECT count(*) INTO :llcnt FROM GYCLSL WHERE F_CKNM=:vscknm AND F_CLNM=:vsclnm;
IF isnull(llcnt) THEN llcnt=0
vscknm=trim(vscknm)
vsclnm=trim(vsclnm)
IF llcnt=0 THEN
vsSql=" INSERT GYCLSL(F_CKNM,F_CLNM,F_SLYE,F_SL"+lsMon+",F_DSLJ,F_DS"+lsMon+" "+&
"VALUES('"+vscknm+"','"+vsclnm+"',"+string(vljhje)+","+string(vljhje)+","+&
string(vljhje)+","+string(vljhje)+""
ELSE
vsSql="UPDATE GYCLSL SET "+&
" F_SLYE=F_SLYE-"+string(vljhje)+","+&
" F_SL"+lsMon+"=F_SL"+lsMon+" - "+string(vljhje)+","+&
" F_DSLJ=F_DSLJ+"+string(vljhje)+","+&
" F_DS"+lsMon+"=F_DS"+lsMon+"+"+string(vljhje)+&
" FROM GYCLSL"+&
" WHERE F_CKNM='"+vsCknm+"'"+&
" AND F_CLNM='"+vsclnm+"'"
END IF
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="5入库记数量帐出错。"+Sqlca.Sqlerrtext
GOTO forout
END IF
FETCH cur_sl INTO :vsCknm,:vsclnm,:vljhje;
LOOP
CLOSE cur_sl;
uo_1.uf_set_Position(60)
vsSql="select F_CLNM,F_JHJE from "+isJeJzTable
DECLARE cur_je DYNAMIC CURSOR FOR sqlsa;
PREPARE sqlsa FROM :vsSql;
OPEN DYNAMIC cur_je;
FETCH cur_je INTO :vsclnm,:vljhje;
llcnt=0
//把临时金额记帐表中的数据更新到帐面上
DO WHILE sqlca.sqlcode<>;100
SELECT F_CLBH,F_JS INTO :vsclbh,:vsjs FROM GYCLZD WHERE F_CLNM=:vsclnm;
vsclbh=trim(vsclbh)
vijs=integer(vsjs) - 1
vsSql=" Update GYCLZD Set "+&
"F_JEYE=F_JEYE-"+string(vljhje)+","+&
"F_YE"+lsMon+"=F_YE"+lsmon+"-"+string(vljhje)+","+&
"F_DFLJ=F_DFLJ+"+string(vljhje)+","+&
"F_DF"+lsMon+"=F_DF"+lsmon+"+"+string(vljhje)+&
" From GYCLZD"+&
" Where F_CLBH='"+vsclbh+"'"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="6出库记金额帐出错。"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
FOR i=vijs TO 1 STEP -1
vsclbh=left(vsclbh,giclpos[i+1] - 1)
viLen=len(vsclbh)
vsclbh=vsclbh+fill('0',iiCllen - viLen)
vsSql=" Update GYCLZD Set "+&
"F_JEYE=F_JEYE-"+string(vljhje)+","+&
"F_YE"+lsMon+"=F_YE"+lsmon+"-"+string(vljhje)+","+&
"F_DFLJ=F_DFLJ+"+string(vljhje)+","+&
"F_DF"+lsMon+"=F_DF"+lsmon+"+"+string(vljhje)+&
" From GYCLZD"+&
" Where F_CLBH='"+vsclbh+"'"
EXECUTE IMMEDIATE :vsSql;
IF Sqlca.Sqlcode= -1 THEN
vsMsg="61出库记金额帐出错。"+Sqlca.SqlErrtext
GOTO FOROUT
END IF
NEXT
FETCH cur_je INTO :vsclnm,:vljhje;
LOOP
CLOSE cur_je;
uo_1.uf_set_Position(100)
)
//出库单记帐标志
sle_1.text = '出库单记帐:记帐人签名'
UPDATE GYCKD1 SET F_JZ=1,F_JZXM=:userName,F_JZBH=:zgbh
WHERE F_JZ=0 AND F_SH=1 AND F_DATE LIKE :lsYear+:lsMon+'%';
//AND F_PJLX IN ('O','P','Q')
if sqlca.sqlcode <>; 0 then
vsMsg = "作记帐标志出错:"+sqlca.sqlerrtext
goTo ForOut
end if
return 1
FOROUT:
gfErrorMess("记明细帐失败。",vsMsg)
return - 1 |
|