$PBExportHeader$uo_yfimex.sru forward global type uo_yfimex from nonvisualobject end type end forward global type uo_yfimex from nonvisualobject end type global uo_yfimex uo_yfimex type prototypes Function long GetFullPathName (String lpFileName ,Long nBufferLength,ref string lpBuffer,ref long lpFilePart) library "kernel32" ALIAS FOR "GetFullPathNameA;Ansi" Function long CreateDirectory (string lpNewDirectory ,ref SECURITY_ATTRIBUTES lpSecurityAttributes ) library "kernel32" Alias for "CreateDirectoryA;Ansi" end prototypes type variables private: DataStore FilterData,ds_data int relt=0 string msg="" int i=0,j=0 string importtempfilepath='importtmp' string exporttempfilepath='exporttmp' string exportlogpath='exportlog' string importlogpath='importlog' string inceptpath='inceptdata' string incepttmpfile='_inceptdata.tmp' blob blbdw_data string sys_changetime="sys_changetime" string sys_changeflag="sys_changeflag" string infofilename="_table_info.ifo" //传递的信息文件 string dts_deletelog="dts_deletelog" //删除日志表 int len_headinfo=100 //头长度 end variables forward prototypes public function string iuf_getdwvalue (datastore dw, integer urow, integer ucolumn) private function integer uf_zipfile (string fpath[], string arg_objfilename, ref string a_msg) private function integer pf_writetifofile (string arg_tableinfo, string arg_objfilename, ref string arg_msg) private function string pf_readtifofile (string arg_objfilename, ref string arg_msg) private function integer uf_unzipfile (string arg_zipfilename, string arg_objfilepath, ref string a_msg) public function integer si_newsendout (ref transaction arg_transaction, string arg_objfilename, string arg_dptmname, string arg_dscrp, string arg_emp, ref string arg_msg) public function integer si_autoincept_data (ref transaction arg_incept_transaction, ref transaction arg_import_transaction, ref string arg_msg, ref string arg_log) public function integer f_importdata (ref transaction arg_transaction, string arg_objzipfilename, integer arg_commitstep, boolean arg_if_buildlogfile, ref string arg_log, ref string arg_msg, ref integer arg_errflag) public function integer si_delete_dtrec (transaction arg_transaction, long arg_dt_id, ref string arg_msg) public function integer f_init_syscurpath (ref string arg_msg) public function integer si_incept_data (ref transaction arg_incept_transaction, ref transaction arg_import_transaction, long arg_dt_id, ref string arg_msg, ref string arg_log) public function integer si_reset_dtrec (transaction arg_transaction, long arg_dt_id, ref string arg_msg) public function integer uf_setrsl_trandt (transaction arg_transaction, long arg_scid, string arg_tablename, datetime arg_d_newlydt, datetime arg_dg_newlydt, ref string arg_msg) public function integer si_cnt_database_incept (ref transaction arg_dataincept_sqlca, ref string arg_msg) public function integer uf_getrsl_trandt (transaction arg_transaction, long arg_scid, string arg_tablename, ref datetime arg_d_newlydt, ref datetime arg_dg_newlydt, ref string arg_msg) public function integer f_trandata_online (long arg_scid, ref transaction arg_transaction, ref transaction arg_obj_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, boolean arg_if_usedefaultdt, datetime arg_bg_changetime, datetime arg_ed_changetime, integer arg_commitstep, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log) public function integer f_tranmstpdata_online (ref transaction arg_transaction, ref transaction arg_obj_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log) public function integer f_exportdata (transaction arg_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, datetime arg_bg_changetime, datetime arg_ed_changetime, integer arg_usechangetime, integer arg_getflag, string arg_exportfilename, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log) public function integer f_exportdata_blob (integer arg_usechangetime, datetime arg_bg_changtime, datetime arg_ed_changtime, string arg_exportfilename, string arg_exportbfilename, integer arg_info, ref string arg_msg) public function integer f_delzipfile (string arg_exportfilename, ref string arg_msg) public function integer f_importdata_blob (string arg_importfilename, string arg_zipfile, ref integer arg_info, ref integer arg_usechangetime, ref datetime arg_bg_changetime, ref datetime arg_ed_changtime, ref string arg_msg) end prototypes public function string iuf_getdwvalue (datastore dw, integer urow, integer ucolumn);//iuf_getdwvalue(datastore dw,integer uRow,int uColumn) string reStr choose case trim(left(lower(dw.describe("#"+string(uColumn)+".ColType")),5)) case "char(" //reStr="'"+string(dw.GetItemString(uRow,uColumn))+"'" reStr="'"+f_replacestr(string(dw.GetItemString(uRow,uColumn)),"'","'+char(39)+'")+"'" if isnull(reStr) then reStr="''" case "date" reStr="'"+string(dw.GetItemDate(uRow,uColumn))+"'" if isnull(reStr) then reStr=" NULL " case "datet" reStr="'"+string(dw.GetItemDatetime(uRow,uColumn))+"'" if isnull(reStr) then reStr=" NULL " case "decim" reStr=string(dw.GetItemDecimal(uRow,uColumn)) if isnull(reStr) then reStr="0" case "int","long","numbe","real","ulong" reStr=string(dw.GetItemNumber(uRow,uColumn)) if isnull(reStr) then reStr="0" case "time","times" reStr="'"+string(dw.GetItemTime(uRow,uColumn))+"'" if isnull(reStr) then reStr=" NULL " case else reStr="[err]" end choose return reStr end function private function integer uf_zipfile (string fpath[], string arg_objfilename, ref string a_msg);//uf_zipfile() long ll_status=0,ls_i=0 OLEObject ZIPObject relt=1 ZIPObject = CREATE OLEObject ll_status = ZIPObject.ConnectToNewObject("CGZiplibrary.CGZipfiles") if ll_status<0 then relt=0 choose case ll_status case -1 a_msg="Invalid Call: the argument is the Object property of a control" case -2 a_msg="Class name not found" case -3 a_msg="Object could not be created" case -4 a_msg="Could not connect to object" case -9 a_msg="Other error" end choose goto ext end if arg_objfilename = Trim(arg_objfilename) If Pos(arg_objfilename, ":\") <= 0 Then a_msg= "必须输入完整的目标文件文件路径." relt=0 GoTo ext End If ZIPObject.ZipFileName = arg_objfilename ZIPObject.UpdatingZip = False // ensures a new zip is created if upperbound(fpath)=0 then a_msg= "无压缩文件列表." relt=0 GoTo ext end if For ls_i = 1 To upperbound(fpath) ZIPObject.AddFile(fpath[ls_i]) Next if ZIPObject.MakeZipFile()<>0 then relt=0 a_msg="压缩文件失败!"+ZIPObject.GetLastMessage goto ext end if ext: destroy ZIPObject return relt end function private function integer pf_writetifofile (string arg_tableinfo, string arg_objfilename, ref string arg_msg);//pf_writetifofile(arg_tableinfo,arg_objfilename,arg_msg) integer li_FileNum,rslt=1,ls_jh li_FileNum = FileOpen( arg_objfilename,StreamMode!, Write!, LockWrite!, Replace!) if li_FileNum<=0 then arg_msg="打开文件"+infofilename+"失败" rslt=0 fileclose(li_FileNum) goto ext end if ls_jh=FileWrite(li_FileNum, arg_tableinfo) if ls_jh<=0 then arg_msg="写文件"+infofilename+"失败" rslt=0 fileclose(li_FileNum) goto ext end if fileclose(li_FileNum) ext: return rslt end function private function string pf_readtifofile (string arg_objfilename, ref string arg_msg);//pf_readtifofile(arg_objfilename,arg_msg) integer li_FileNum,rslt=1,ls_jh long ll_FLength string str_objstr="" ll_FLength = FileLength(arg_objfilename) li_FileNum = FileOpen( arg_objfilename,StreamMode!) if li_FileNum<=0 then arg_msg="打开文件"+infofilename+"失败" rslt=0 fileclose(li_FileNum) goto ext end if if not ll_FLength<32767 then arg_msg="读文件"+infofilename+"失败,文件超长" rslt=0 fileclose(li_FileNum) goto ext end if ls_jh=Fileread(li_FileNum, str_objstr) if ls_jh<=0 then arg_msg="读文件"+infofilename+"失败" rslt=0 fileclose(li_FileNum) goto ext end if fileclose(li_FileNum) ext: if rslt=0 then str_objstr="" end if return str_objstr end function private function integer uf_unzipfile (string arg_zipfilename, string arg_objfilepath, ref string a_msg);//uf_unzipfile(arg_zipfilename,arg_objfilepath,a_msg) long ll_status=0,ls_i=0 OLEObject ZIPObject relt=1 ZIPObject = CREATE OLEObject ll_status = ZIPObject.ConnectToNewObject("CGZiplibrary.CGUnzipFiles") if ll_status<0 then relt=0 choose case ll_status case -1 a_msg="Invalid Call: the argument is the Object property of a control" case -2 a_msg="Class name not found" case -3 a_msg="Object could not be created" case -4 a_msg="Could not connect to object" case -9 a_msg="Other error" end choose goto ext end if if not FileExists (arg_zipfilename) then a_msg= "UnZipFiles>>无效的文件名" relt=0 GoTo ext end if If Pos( arg_objfilepath, ".") > 0 Then a_msg= "UnZipFiles>> 无效的文件存放目录路径." relt=0 GoTo ext ElseIf pos( arg_objfilepath, ":") <= 0 Then a_msg= "UnZipFiles>> 必须输入完整的目标文件存放目录路径." relt=0 GoTo ext End If ZIPObject.ZipFileName = arg_zipfilename ZIPObject.ExtractDir = arg_objfilepath // Keep Directory Structure of Zip ZIPObject.HonorDirectories = False // Unzip and Display any errors as required If ZIPObject.Unzip() <> 0 Then relt=0 a_msg="UnZip>> 无法解压文件 " + arg_zipfilename + " :" + ZIPObject.GetLastMessage goto ext End If ext: destroy ZIPObject return relt end function public function integer si_newsendout (ref transaction arg_transaction, string arg_objfilename, string arg_dptmname, string arg_dscrp, string arg_emp, ref string arg_msg);//si_newsendout(arg_transaction,arg_objfilename,arg_dptmname,arg_dscrp,arg_emp,arg_msg) // //读入目标文件 //新建数据传递记录 //取得新记录id //上传数据包 //arg_transaction long rslt=1 blob b_objfiledata,b_part integer li_FileNum,ls_i,bytes_read long flen,loops,new_dt_id,flen_k datetime new_dt long ll_status=0,ls_j,ls_k OLEObject blobcolumnupdateObject char BuffArr[] // blobcolumnupdate.blobupdate if isnull(arg_objfilename) or trim(arg_objfilename)='' then rslt=0 arg_msg='非有效文件名' goto ext end if if isnull(arg_dscrp) then arg_dscrp='' if isnull(arg_dptmname) or trim(arg_dptmname)='' then rslt=0 arg_msg='非有效上传部门名称' goto ext end if if isnull(arg_emp) or trim(arg_emp)='' then rslt=0 arg_msg='非有效操作员名称' goto ext end if if not FileExists(arg_objfilename) then rslt=0 arg_msg='目标文件不存在' goto ext end if SetPointer(HourGlass!) //// Get the file length, and open the file //flen = FileLength(arg_objfilename) //flen_k=flen/1024 //li_FileNum = FileOpen(arg_objfilename,StreamMode!, Read!, LockRead!) //if li_FileNum = -1 then // rslt=0 // arg_msg='目标文件打开失败' // goto ext //end if // // ////// Determine how many times to call FileRead //IF flen > 32765 THEN // IF Mod(flen, 32765) = 0 THEN // loops = flen/32765 // ELSE // loops = (flen/32765) + 1 // END IF //ELSE // loops = 1 //END IF ////// Read the file // //FOR ls_i = 1 to loops // bytes_read = FileRead(li_FileNum, b_part) // b_objfiledata=b_objfiledata+b_part //NEXT // //FileClose(li_FileNum) //使用com: blobcolumnupdate.blobupdate blobcolumnupdateObject = CREATE OLEObject ll_status = blobcolumnupdateObject.ConnectToNewObject("blobcolumnupdate.blobupdate") if ll_status<0 then rslt=0 choose case ll_status case -1 arg_msg="Invalid Call: the argument is the Object property of a control" case -2 arg_msg="Class name not found" case -3 arg_msg="Object could not be created" case -4 arg_msg="Could not connect to object" case -9 arg_msg="Other error" end choose goto ext end if if not blobcolumnupdateObject.connectdb(arg_transaction.Database,arg_transaction.LogID,arg_transaction.LogPass,arg_transaction.ServerName ) then rslt=0 arg_msg='数据库连接失败>blobcolumnupdate.blobupdate' goto ext end if /// SELECT top 1 getdate() as new_dt INTO :new_dt FROM sysobjects using arg_transaction ; if arg_transaction.sqlcode<>0 then rslt=0 arg_msg='提取当前时间操作失败(借用sysobjects)'+arg_transaction.sqlerrtext goto ext end if INSERT INTO Dt_dataincept ( Incepttime, Sendout_emp, Sendout_dptm, Update_flag, Wrongdata_flag, Dscrp) VALUES ( :new_dt, :arg_emp, :arg_dptmname, 0, 0, :arg_dscrp ) using arg_transaction ; if arg_transaction.sqlcode<>0 then rslt=0 arg_msg='插入新数据传送记录失败>'+arg_transaction.sqlerrtext rollback using arg_transaction; goto ext end if select max(dt_id) as dt_id into :new_dt_id from Dt_dataincept where Incepttime=:new_dt using arg_transaction ; if arg_transaction.sqlcode<>0 then rslt=0 arg_msg='查询新唯一码操作失败'+arg_transaction.sqlerrtext rollback using arg_transaction; goto ext end if commit using arg_transaction; if not blobcolumnupdateObject.WriteFileDataToBLOB(arg_msg,'Dt_dataincept', 'dt_data', arg_objfilename , '(dt_id = '+string(new_dt_id)+')') then rslt=0 messagebox('系统提示','写入数据包操作失败>'+arg_msg) goto ext end if //flen_k //UPDATEBLOB Dt_dataincept SET dt_data=:b_objfiledata // WHERE dt_id = :new_dt_id // USING arg_transaction ; // if arg_transaction.sqlnrows=0 then // rslt=0 // arg_msg='写入数据包操作失败>'+arg_transaction.sqlerrtext // rollback using arg_transaction; // // goto ext //end if //messagebox(string(new_dt_id)+' '+string(len(b_objfiledata))+' '+string(arg_transaction.sqlnrows),string(arg_transaction.SQLErrText)) rslt=new_dt_id ext: DESTROY blobcolumnupdateObject return rslt end function public function integer si_autoincept_data (ref transaction arg_incept_transaction, ref transaction arg_import_transaction, ref string arg_msg, ref string arg_log);//si_autoincept_data // //取得临时文件全路径 //提取最早上传的数据包记录ID //提取数据包 //写临时文件 //调用更新函数 long rslt=1,ls_jh long obj_dt_id=0,li_FileNum,fend,fend_t,ls_i,do_arrow=0 string cur_path,ls_str string obj_incept_tmp_file blob b_dtdata, b_dtdata_p int import_err_int=0 datetime ls_dt arg_msg='' setnull(b_dtdata) arg_log='' //取得当前绝对路径 cur_path=sys_cur_path do_arrow=long(ProfileString (sys_inifilename,'si_autoincept_data', "arrow",'0')) obj_incept_tmp_file=cur_path+inceptpath+'\'+incepttmpfile ///////////提取最早上传的数据包记录ID if do_arrow=0 then SELECT min(Dt_id) as dt_id INTO :obj_dt_id FROM Dt_dataincept WHERE ( Dt_dataincept.Update_flag = 0 ) AND ( Dt_dataincept.Wrongdata_flag = 0 ) using arg_incept_transaction ; else SELECT max(Dt_id) as dt_id INTO :obj_dt_id FROM Dt_dataincept WHERE ( Dt_dataincept.Update_flag = 0 ) AND ( Dt_dataincept.Wrongdata_flag = 0 ) using arg_incept_transaction ; end if if arg_incept_transaction.sqlcode<>0 and arg_incept_transaction.sqlcode<>100 then rslt=0 arg_msg='查询未执行更新的数据传送记录操作失败>'+arg_incept_transaction.sqlerrtext goto ext elseif arg_incept_transaction.sqlcode=100 then rslt=0 arg_msg='' goto ext end if if obj_dt_id>0 then SELECT Dt_dataincept.Incepttime , '/'+ltrim(rtrim(Dt_dataincept.Sendout_emp))+'/'+ltrim(rtrim(Dt_dataincept.Sendout_dptm))+'/'+ltrim(rtrim(Dt_dataincept.Dscrp)) as sss INTO :ls_dt,:ls_str FROM Dt_dataincept WHERE ( Dt_dataincept.Dt_id = :obj_dt_id ) using arg_incept_transaction ; if arg_incept_transaction.sqlcode<>0 then rslt=0 arg_msg='查询要更新的数据传送记录操作失败>'+arg_incept_transaction.sqlerrtext goto ext end if arg_log=string(ls_dt,'yyyy-mm-dd h:mm')+ls_str+char(10)+char(13) end if rslt=si_incept_data(arg_incept_transaction,arg_import_transaction,obj_dt_id,arg_msg,arg_log) ext: return rslt end function public function integer f_importdata (ref transaction arg_transaction, string arg_objzipfilename, integer arg_commitstep, boolean arg_if_buildlogfile, ref string arg_log, ref string arg_msg, ref integer arg_errflag);//f_importdata(arg_transaction,arg_objzipfilename,arg_commitstep,arg_if_buildlogfile,arg_log,arg_msg,arg_errflag) //1.取得当前路径等 //2.删除旧有文件 //3.解压缩文件为目标文件 //4.读取传递信息文件,取要更新的DBf列表 //5.,导入删除日志表内容,先执行sql DELete ////6.删除校验DBF文件头临时文件*.if //7.循环:1.数据库读取要更新的DBF信息 // 2.动态建立DW // //3.校验DBF文件头(生成校验文件*.IF(DBF格式,校验文件头)) // 4.取数据 // 5.生成UPdate语句 // 6.更新,失败则生成Insert语句,再插入 // 7.记录日志变量 s_sys_tntblinfo ls_tntblinfo string str_export_table_info='',ls_temppathname,msg_err_sql string DBFfilename[300],str_tableinfo long ls_i,ls_j,ls_k,ls_l,ls_psc,Import_result long rslt=1,rslt_insert_rowcount,rslt_update_rowcount long rowcount_update_ok=0,rowcount_update_false=0,del_fail=0,del_suc=0,DBFfn_ub int li_FileNum,int_tabletype long ins_c,upt_c blob setblbdw_data string cur_path,ls_sc_id_name,ls_Indcolumn_name,ls_msg,ls_thr_Indcolumn_name string char_enter,pk1_type,pk2_type,ls_fou_Indcolumn_name string str_dwSQl,str_SyntaxFromSQL int exporttype,lg_rslt string field_str,value_str string obj_value string SelectPri1='',SelectPri3="" string SelectPri2="",SelectPri4="" string UpdateField string update_string,del_sqlstr int PK1_sit ,PK2_sit,PK3_sit,PK4_sit char_enter=char(13)+char(10) //换行符 //if MessageBox("警告", '确定要开始导入数据吗? 原有的数据即将被覆盖且不可以恢复!', Exclamation!, OKCancel!, 2)=2 then // rslt=0 // goto ext //end if arg_errflag=0 arg_log=arg_log+arg_objzipfilename+char_enter open(w_sys_wait_jdt) //初始化进度条 w_sys_wait_jdt.show() w_sys_wait_jdt.wf_accepttol(1) w_sys_wait_jdt.wf_inc(0) w_sys_wait_jdt.st_msg.text="正在解压缩目标文件..." if not FileExists(arg_objzipfilename) then rslt=0 arg_msg="文件路径错误或目标文件不存在." arg_errflag=1 goto ext end if //取得当前绝对路径 cur_path=sys_cur_path //解压数据文件 rslt=uf_unzipfile(arg_objzipfilename,cur_path+importtempfilepath,arg_msg) if rslt=0 then arg_msg="解压缩>>"+arg_msg arg_errflag=3 goto ext end if w_sys_wait_jdt.st_msg.text="正在读取传递信息表..." //读取传递信息 ls_temppathname=cur_path+importtempfilepath+'\'+infofilename str_tableinfo=pf_readtifofile(ls_temppathname,arg_msg) if str_tableinfo="" then rslt=0 arg_msg="传递信息表>>"+arg_msg arg_errflag=4 goto ext end if ls_k=0 ls_psc=0 ls_psc=pos(str_tableinfo,';') DO WHILE ls_psc>0 if left(str_tableinfo,ls_psc - 1)<>'' then ls_k++ DBFfilename[ls_k]=left(str_tableinfo,ls_psc - 1) end if str_tableinfo=right(str_tableinfo,len(str_tableinfo) - ls_psc) ls_psc=pos(str_tableinfo,';') LOOP DBFfn_ub=ls_k w_sys_wait_jdt.st_msg.text="正在执行删除..." w_sys_wait_jdt.wf_accepttol(DBFfn_ub+3) for ls_i=1 to DBFfn_ub //查找删除日志表 DBFfilename[ls_i]=lower(DBFfilename[ls_i]) if lower(DBFfilename[ls_i])=dts_deletelog then //生成select语句 str_dwSQl="select * from "+DBFfilename[ls_i] //生成DW ls_msg="" //arg_transaction.AutoCommit=True str_SyntaxFromSQL =arg_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) //arg_transaction.AutoCommit=false if ls_msg>'' then arg_msg=DBFfilename[ls_i]+"dw语法生成失败" arg_errflag=5 goto ext end if ds_data.Create(str_SyntaxFromSQL,ls_msg) ds_data.settransobject(arg_transaction) if ls_msg>'' then arg_msg=DBFfilename[ls_i]+"建立相关DW失败" arg_errflag=5 goto ext end if //<4>从DBF导入数据到DW ls_temppathname=cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".DBF" if not FileExists(ls_temppathname) then arg_log=arg_log+"<删除日志表>["+">["+DBFfilename[ls_i]+"]无对应的DBF文件."+char_enter arg_errflag=7 goto ext end if Import_result=ds_data.ImportFile(cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".DBF") if Import_result<-1 then arg_log=arg_log+"<删除日志表>["+DBFfilename[ls_i]+"]导入失败."+char_enter arg_errflag=8 goto ext end if for ls_j=1 to ds_data.rowcount() w_sys_wait_jdt.wf_inc(3*(ls_j/ds_data.rowcount())) w_sys_wait_jdt.st_msg.text="正在执行删除..."+string(ls_j)+"/"+string(ds_data.rowcount()) del_sqlstr=ds_data.object.sql_delete[ls_j] Execute immediate :del_sqlstr using arg_transaction; //先执行delete if arg_transaction.sqlcode<>0 then del_fail++ if len(msg_err_sql)<5000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+del_sqlstr+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if else del_suc++ end if if Mod(ls_j , arg_commitstep)=0 then //够提交步长则提交 commit using arg_transaction; end if next ds_data.reset() commit using arg_transaction; arg_log=arg_log+"<删除日志表更新>成功:"+string(del_suc)+"失败:"+string(del_fail)+char_enter+msg_err_sql end if next w_sys_wait_jdt.wf_inc(3.1) //循环处理读入数据,更新数据库,写日志 for ls_i=1 to DBFfn_ub setnull(blbdw_data) w_sys_wait_jdt.st_msg.text="正在执行更新... >"+DBFfilename[ls_i] msg_err_sql='' rowcount_update_ok=0 rowcount_update_false=0 upt_c=0 ins_c=0 ds_data.reset() //<1>取出该文件信息 if f_get_tntblinfo(DBFfilename[ls_i],ls_tntblinfo)=1 then ls_sc_id_name=trim(ls_tntblinfo.Sc_id_name) ls_Indcolumn_name=trim(ls_tntblinfo.Indcolumn_name) int_tabletype=ls_tntblinfo.tabletype ls_thr_Indcolumn_name=trim(ls_tntblinfo.thrIndcolumn_name) ls_fou_Indcolumn_name=trim(ls_tntblinfo.fouIndcolumn_name) exporttype=ls_tntblinfo.exporttype else ls_msg=DBFfilename[ls_i]+"不在系统记录中;" arg_log=arg_log+ls_msg+char_enter goto nexttable end if //<2>生成select语句 str_dwSQl="select "+DBFfilename[ls_i]+".* from "+DBFfilename[ls_i] //<3>生成DW ls_msg="" //arg_transaction.AutoCommit=True str_SyntaxFromSQL =arg_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) //arg_transaction.AutoCommit=false if ls_msg>'' then arg_msg=DBFfilename[ls_i]+"dw语法生成失败" goto nexttable end if ds_data.Create(str_SyntaxFromSQL,ls_msg) ds_data.settransobject(arg_transaction) if ls_msg>'' then arg_msg=DBFfilename[ls_i]+"建立相关DW失败" goto nexttable end if //<4>从文件导入数据到DW if exporttype=1 then //blob导入 ls_temppathname=cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".blb" if not FileExists(ls_temppathname) then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无对应的blb文件."+char_enter goto nexttable end if lg_rslt=f_getblobfromfile(setblbdw_data,ls_temppathname,arg_msg) if lg_rslt=0 then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]对应的blb文件读取失败.>"+arg_msg+char_enter goto nexttable end if lg_rslt=ds_data.setFullState(setblbdw_data) if lg_rslt=-1 then Import_result=0 else Import_result=ds_data.rowcount() end if setnull(blbdw_data) if Import_result<=0 then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无数据或导入blob失败.>"+string(Import_result)+char_enter goto nexttable end if //blbdw_data elseif exporttype=2 then//txt导入 ls_temppathname=cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".txt" if not FileExists(ls_temppathname) then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无对应的txt文件."+char_enter goto nexttable end if Import_result=ds_data.ImportFile(cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".txt") if Import_result<=0 then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无数据或导入txt失败.>"+string(Import_result)+char_enter goto nexttable end if else //DBF导入 ls_temppathname=cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".DBF" if not FileExists(ls_temppathname) then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无对应的DBF文件."+char_enter goto nexttable end if Import_result=ds_data.ImportFile(cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".DBF") //if lower(DBFfilename[ls_i])='pr_experimentrec_manager' then arg_log=arg_log+string(Import_result) if Import_result<=0 then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]无数据或导入DBF失败.>"+string(Import_result)+char_enter goto nexttable end if end if PK1_sit=0 PK2_sit=0 //定位主关键字段位置,ls_Indcolumn_name为空则为单关键字表 for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_sc_id_name) then PK1_sit=ls_k //有此字段 //PK1_type=ds_data.Describe("#"+string(ls_k)+".type") end if if not ls_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_Indcolumn_name) then PK2_sit=ls_k end if end if if not ls_thr_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_thr_Indcolumn_name) then PK3_sit=ls_k end if end if if not ls_fou_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_fou_Indcolumn_name) then PK4_sit=ls_k end if end if next //ls_k if PK1_sit+PK2_sit=0 then arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]错误主键名称."+char_enter goto nexttable end if //<5>循环生成SQl语句,更新数据库 Import_result=ds_data.rowcount() for ls_j=1 to Import_result // rslt_insert_rowcount=rslt_insert_rowcount+ins_c // rslt_update_rowcount=rslt_update_rowcount+upt_c // ins_c=0 // upt_c=0 field_str="" obj_value="" SelectPri1="" SelectPri2="" SelectPri3="" SelectPri4="" UpdateField="" update_string="" //取主关键字的内容 SelectPri1=iuf_getdwvalue(ds_data,ls_j,PK1_sit) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if if not ls_Indcolumn_name='' then //双主键 SelectPri2=iuf_getdwvalue(ds_data,ls_j,PK2_sit) if SelectPri2="[err]" then rowcount_update_false++ goto nextrow end if end if if not ls_thr_Indcolumn_name='' then //三主键 SelectPri3=iuf_getdwvalue(ds_data,ls_j,PK3_sit) if SelectPri3="[err]" then rowcount_update_false++ goto nextrow end if end if if not ls_fou_Indcolumn_name='' then //四主键 SelectPri4=iuf_getdwvalue(ds_data,ls_j,PK4_sit) if SelectPri4="[err]" then rowcount_update_false++ goto nextrow end if end if //++++++++生成更新子段语句++++++ //先更新记录--------------------------- for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if UpdateField=UpdateField+ds_data.describe("#"+string(ls_k)+".Name")+"="+obj_value+"," next //ls_k UpdateField=left(UpdateField,len(UpdateField) - 1) update_string="Update "+DBFfilename[ls_i]+" set " +UpdateField + " where "+& ls_sc_id_name+"="+SelectPri1 if ls_Indcolumn_name<>'' then //双主键 update_string=update_string+" and "+ls_Indcolumn_name+"="+SelectPri2 end if if ls_thr_Indcolumn_name<>'' then //三主键 update_string=update_string+" and "+ls_thr_Indcolumn_name+"="+SelectPri3 end if if ls_fou_Indcolumn_name<>'' then //四主键 update_string=update_string+" and "+ls_fou_Indcolumn_name+"="+SelectPri4 end if Execute immediate :update_string using arg_transaction; //执行Update if arg_transaction.sqlcode<>0 then if len(msg_err_sql)<30000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rowcount_update_false++ goto nextrow else if arg_transaction.sqlnrows=0 then //更新不成功就插入----------- field_str="" obj_value="" value_str='' update_string="" for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if field_str=field_str+ds_data.describe("#"+string(ls_k)+".Name")+"," value_str=value_str+obj_value+"," next //ls_k field_str=left(field_str,len(field_str)-1) value_str=left(value_str,len(value_str)-1) update_string="Insert into "+DBFfilename[ls_i]+" ("+field_str+& ") values ("+value_str+")" Execute immediate :update_string using arg_transaction; //执行Insert if arg_transaction.sqlcode<>0 then if len(msg_err_sql)<50000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rowcount_update_false++ goto nextrow end if rslt_insert_rowcount++ ins_c++ else rslt_update_rowcount++ upt_c++ end if end if rowcount_update_ok++ if Mod(ls_j , arg_commitstep)=0 then //够提交步长则提交 commit using arg_transaction; end if nextrow: w_sys_wait_jdt.st_msg.text="正在执行更新... >"+DBFfilename[ls_i]+">"+string(rowcount_update_ok) next //ls_j arg_log=arg_log+'<'+string(ls_i)+">["+DBFfilename[ls_i]+"]成功:"+string(rowcount_update_ok)+"(更新-"+string(upt_c)+"/插入-"+string(ins_c)+")失败:"+string(rowcount_update_false)+"."+char_enter+msg_err_sql nexttable: commit using arg_transaction; w_sys_wait_jdt.wf_inc(3+ls_i) next //ls_i w_sys_wait_jdt.st_msg.text="删除文件..." //删除原有文件 string ls_arg_msg for ls_i=1 to DBFfn_ub if trim(DBFfilename[ls_i])<>"" then ls_temppathname=cur_path+importtempfilepath+'\'+DBFfilename[ls_i]+".DBF" if FileExists(ls_temppathname) then if not FileDelete(ls_temppathname) then arg_msg="删除临时文件失败("+ls_temppathname+")! " ls_arg_msg=ls_arg_msg + arg_msg end if end if end if next ls_temppathname=cur_path+importtempfilepath+'\'+infofilename if FileExists(ls_temppathname) then if not FileDelete(ls_temppathname) then arg_msg="删除临时文件失败("+ls_temppathname+")!" ls_arg_msg=ls_arg_msg + arg_msg end if end if w_sys_wait_jdt.wf_inc(ls_i+4) //进度完成 ///// ls_temppathname=cur_path+importlogpath+'\'+"LongJoe_Import"+string(today(),'yyyymmdd')+string(now(),'hhmmss')+".log" arg_msg='总操作表-'+string(DBFfn_ub)+';总插入记录-'+string(rslt_insert_rowcount)+';总更新记录-'+string(rslt_update_rowcount) arg_log=arg_log+char_enter+arg_msg if arg_if_buildlogfile then rslt=pf_writetifofile(arg_log,ls_temppathname,arg_msg) if rslt=0 then arg_msg="写日志文件失败>>"+arg_msg arg_errflag=0 rslt=1 end if end if // ls_arg_msg=ls_arg_msg + arg_msg arg_msg=ls_arg_msg // ext: close(w_sys_wait_jdt) return rslt end function public function integer si_delete_dtrec (transaction arg_transaction, long arg_dt_id, ref string arg_msg);//si_delete_dtrec long rslt=1 if isnull(arg_dt_id) or arg_dt_id<=0 then rslt=0 arg_msg='无效数据传送记录唯一码!' goto ext end if DELETE FROM Dt_dataincept WHERE Dt_dataincept.Dt_id = :arg_dt_id using arg_transaction ; if arg_transaction.sqlcode<>0 then arg_msg='删除数据传送记录操作失败>'+arg_transaction.sqlerrtext rslt=0 rollback using arg_transaction; goto ext end if commit using arg_transaction; ext: return rslt end function public function integer f_init_syscurpath (ref string arg_msg);//f_init_syscurpath long rslt =1 string lpBuffer lpBuffer=Fill('',200) long lpfilepart,li_FileNum security_attributes ls_security_attributes rslt=GetFullPathName(sys_inifilename,196,lpBuffer,lpFilePart) if rslt=0 then arg_msg="提取当前路径失败,可能是因为系统INI文件丢失!" goto ext else sys_cur_path=left(lpBuffer,len(lpBuffer)-len(sys_inifilename)) end if //尝试建立临时目录 li_FileNum=CreateDirectory(sys_cur_path+exportlogpath,ls_security_attributes) li_FileNum=CreateDirectory(sys_cur_path+exporttempfilepath,ls_security_attributes) li_FileNum=CreateDirectory(sys_cur_path+importlogpath,ls_security_attributes) li_FileNum=CreateDirectory(sys_cur_path+importtempfilepath,ls_security_attributes) li_FileNum=CreateDirectory(sys_cur_path+inceptpath,ls_security_attributes) ext: return rslt end function public function integer si_incept_data (ref transaction arg_incept_transaction, ref transaction arg_import_transaction, long arg_dt_id, ref string arg_msg, ref string arg_log);//si_incept_data // //取得临时文件全路径 //提取最早上传的数据包记录ID //提取数据包 //写临时文件 //调用更新函数 long rslt=1,ls_jh long li_FileNum,fend,fend_t,ls_i string cur_path string obj_incept_tmp_file blob b_dtdata, b_dtdata_p int import_err_int=0 arg_msg='' setnull(b_dtdata) if arg_dt_id<=0 then rslt=0 arg_msg='无效传递记录唯一码!' goto ext end if //取得当前绝对路径 cur_path=sys_cur_path obj_incept_tmp_file=cur_path+inceptpath+'\'+incepttmpfile /////////////////提取数据包 SELECTBLOB dt_data INTO :b_dtdata FROM Dt_dataincept WHERE Dt_dataincept.dt_id = :arg_dt_id USING arg_incept_transaction ; if arg_incept_transaction.sqlcode<>0 or arg_incept_transaction.sqlnrows=0 then rslt=0 arg_msg='提取数据包操作失败>'+arg_incept_transaction.sqlerrtext goto ext end if if len(b_dtdata)<10 or isnull(b_dtdata) then rslt=1 arg_msg='空数据包!' update Dt_dataincept set Update_flag=1 ,Dt_update_dt=getdate(), Dt_update_fnsh_dt=getdate(),update_ctime=update_ctime+1, false_rslt=:arg_msg WHERE Dt_dataincept.dt_id = :arg_dt_id using arg_incept_transaction ; if arg_incept_transaction.sqlnrows=0 then rslt=0 arg_msg='更新[数据更新完毕标记]操作失败>'+arg_incept_transaction.sqlerrtext rollback USING arg_incept_transaction ; goto ext end if goto ext end if update Dt_dataincept set Dt_update_dt=getdate() WHERE Dt_dataincept.dt_id = :arg_dt_id using arg_incept_transaction ; //写开始更新时间 commit USING arg_incept_transaction ; //////////////////////////写临时文件 if FileExists(obj_incept_tmp_file) then filedelete(obj_incept_tmp_file) //if len(b_dtdata)32765 fend=len(b_dtdata) if fend<=32765 then fend_t=1 else fend_t=fend/32765 if mod(fend,32765)<>0 then fend_t=fend_t+1 end if end if li_FileNum = FileOpen( obj_incept_tmp_file,StreamMode!, Write!, LockWrite!) if li_FileNum<=0 then arg_msg="打开文件"+obj_incept_tmp_file+"失败" rslt=0 fileclose(li_FileNum) goto ext end if for ls_i = 1 to fend_t b_dtdata_p=blobmid(b_dtdata,1+(ls_i - 1 )*32765,32765) ls_jh=FileWrite(li_FileNum, b_dtdata_p) if ls_jh<=0 then arg_msg="写文件"+obj_incept_tmp_file+"失败" rslt=0 fileclose(li_FileNum) goto ext end if next fileclose(li_FileNum) //调用数据导入函数 rslt =f_importdata(arg_import_transaction,obj_incept_tmp_file,11,true,arg_log,arg_msg,import_err_int) if rslt =0 then if import_err_int=3 then//数据错误 update Dt_dataincept set Dt_dataincept.Wrongdata_flag = 1,Dt_update_fnsh_dt=getdate() ,Dt_dataincept.update_flag=2,Dt_dataincept.false_rslt=:arg_msg,update_ctime=update_ctime+1 WHERE Dt_dataincept.dt_id = :arg_dt_id using arg_incept_transaction ; if arg_incept_transaction.sqlnrows=0 then rslt=0 arg_msg='更新[错误数据包标记]操作失败>'+arg_incept_transaction.sqlerrtext rollback USING arg_incept_transaction ; goto ext end if else update Dt_dataincept set Dt_update_fnsh_dt=getdate(),Dt_dataincept.Wrongdata_flag =9 ,Dt_dataincept.update_flag=2,Dt_dataincept.false_rslt=:arg_msg,update_ctime=update_ctime+1 WHERE Dt_dataincept.dt_id = :arg_dt_id using arg_incept_transaction ; if arg_incept_transaction.sqlnrows=0 then rslt=0 arg_msg='更新[更新操作失败标记]操作失败>'+arg_incept_transaction.sqlerrtext rollback USING arg_incept_transaction ; goto ext end if end if else update Dt_dataincept set Update_flag=1 ,Dt_update_fnsh_dt=getdate(),false_rslt=:arg_msg,update_ctime=update_ctime+1 WHERE Dt_dataincept.dt_id = :arg_dt_id using arg_incept_transaction ; if arg_incept_transaction.sqlnrows=0 then rslt=0 arg_msg='更新[数据更新完毕标记]操作失败>'+arg_incept_transaction.sqlerrtext rollback USING arg_incept_transaction ; goto ext end if end if commit USING arg_incept_transaction ; ext: return rslt end function public function integer si_reset_dtrec (transaction arg_transaction, long arg_dt_id, ref string arg_msg);//si_reset_dtrec long rslt=1,no_day=0 if isnull(arg_dt_id) or arg_dt_id<=0 then rslt=0 arg_msg='无效数据传送记录唯一码!' goto ext end if SELECT DATEDIFF(day, Dt_dataincept.Dt_update_fnsh_dt, getdate()) as no_day INTO :no_day FROM Dt_dataincept WHERE Dt_dataincept.Dt_id = :arg_dt_id using arg_transaction ; if arg_transaction.sqlcode<>0 then arg_msg='查询数据传送记录操作失败>'+arg_transaction.sqlerrtext rslt=0 goto ext end if if no_day>7 then rslt=0 arg_msg='该更新在早于现在7天前['+string(no_day)+']执行成功，不允许重新执行!' goto ext end if update Dt_dataincept set Dt_dataincept.update_flag=0, Dt_dataincept.wrongdata_flag=0 WHERE Dt_dataincept.Dt_id = :arg_dt_id using arg_transaction ; if arg_transaction.sqlcode<>0 then arg_msg='删除数据传送记录操作失败>'+arg_transaction.sqlerrtext rslt=0 rollback using arg_transaction; goto ext end if commit using arg_transaction; ext: return rslt end function public function integer uf_setrsl_trandt (transaction arg_transaction, long arg_scid, string arg_tablename, datetime arg_d_newlydt, datetime arg_dg_newlydt, ref string arg_msg);//uf_setrsl_trandt(arg_transaction,arg_scid,arg_tablename,arg_d_newlydt,arg_dg_newlydt,arg_msg) long rslt=1 if isnull(arg_d_newlydt) then arg_d_newlydt=datetime(1900-01-01) if isnull(arg_dG_newlydt) then arg_dG_newlydt=datetime(1900-01-01) if year(date(arg_d_newlydt))<=2000 and year(date(arg_dG_newlydt))<=2000 then arg_msg='不合理时间!' rslt=0 goto ext elseif year(date(arg_d_newlydt))<=2000 then UPDATE sys_tran_newlydt SET DG_newlydt = :arg_dg_newlydt where scid=:arg_scid and ltrim(rtrim(Tablename))=ltrim(rtrim(:arg_tablename)) using arg_transaction; elseif year(date(arg_dG_newlydt))<=2000 then UPDATE sys_tran_newlydt SET D_newlydt = :arg_d_newlydt where scid=:arg_scid and ltrim(rtrim(Tablename))=ltrim(rtrim(:arg_tablename)) using arg_transaction; else UPDATE sys_tran_newlydt SET D_newlydt = :arg_d_newlydt, DG_newlydt = :arg_dG_newlydt where scid=:arg_scid and ltrim(rtrim(Tablename))=ltrim(rtrim(:arg_tablename)) using arg_transaction; end if if arg_transaction.sqlcode<>0 then arg_msg='更新最近数据传递时间失败>'+arg_transaction.sqlerrtext rollback using arg_transaction; rslt=0 goto ext elseif arg_transaction.sqlnrows=0 then INSERT INTO sys_tran_newlydt ( scid, Tablename, D_newlydt, DG_newlydt ) VALUES ( :arg_scid, :arg_tablename, :arg_d_newlydt, :arg_dg_newlydt ) using arg_transaction ; if arg_transaction.sqlcode<>0 then arg_msg='新建最近数据传递时间失败>'+arg_transaction.sqlerrtext rollback using arg_transaction; rslt=0 goto ext end if end if commit using arg_transaction; ext: return rslt end function public function integer si_cnt_database_incept (ref transaction arg_dataincept_sqlca, ref string arg_msg);// arg_dataincept_sqlca.DBMS = ProfileString (sys_inifilename, "database_incept", "dbms", "") arg_dataincept_sqlca.database = ProfileString (sys_inifilename, "database_incept", "database", "") arg_dataincept_sqlca.userid = ProfileString (sys_inifilename, "database_incept", "userid", "") arg_dataincept_sqlca.dbpass = f_psw_bczh(ProfileString (sys_inifilename, "database_incept", "dbpass", ""),1,sys_power_key) arg_dataincept_sqlca.logid = ProfileString (sys_inifilename, "database_incept", "logid", "") arg_dataincept_sqlca.logpass = f_psw_bczh(ProfileString (sys_inifilename, "database_incept", "LogPassWord", ""),1,sys_power_key) arg_dataincept_sqlca.servername = ProfileString (sys_inifilename, "database_incept", "servername", "") arg_dataincept_sqlca.dbparm = f_psw_bczh(ProfileString (sys_inifilename, "database_incept", "dbparm", ""),1,sys_power_key) if arg_dataincept_sqlca.database='' or arg_dataincept_sqlca.servername='' then Messagebox("系统提示","连接数据传递数据库失败!") // openWITHPARM(w_set_sqlca,1) //0 只设连接信息 1 并允许自动新建数据库（启动首次调用） return 0 end if connect using arg_dataincept_sqlca; If SQLCA.SQLCode<0 Then Messagebox("系统提示","连接数据传递数据库失败!") // openWITHPARM(w_set_sqlca,1) //0 只设连接信息 1 并允许自动新建数据库（启动首次调用） return 0 End if return 1 end function public function integer uf_getrsl_trandt (transaction arg_transaction, long arg_scid, string arg_tablename, ref datetime arg_d_newlydt, ref datetime arg_dg_newlydt, ref string arg_msg);//uf_Getrsl_trandt(arg_transaction,arg_scid,arg_tablename,arg_d_newlydt,arg_dg_newlydt,arg_msg) long rslt=1 datetime d_newlydt,dG_newlydt SELECT D_newlydt ,dG_newlydt INTO :D_newlydt,:dG_newlydt FROM sys_tran_newlydt WHERE ( scid =:arg_scid ) AND ( Tablename =:arg_tablename ) using arg_transaction ; if arg_transaction.sqlcode=100 then D_newlydt=datetime(1900-01-01,time(0)) dG_newlydt=datetime(1900-01-01,time(0)) elseif arg_transaction.sqlcode<>0 then arg_msg='查询最近数据传递时间失败>'+arg_transaction.sqlerrtext rslt=0 goto ext end if arg_d_newlydt=D_newlydt arg_dg_newlydt=dG_newlydt ext: return rslt end function public function integer f_trandata_online (long arg_scid, ref transaction arg_transaction, ref transaction arg_obj_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, boolean arg_if_usedefaultdt, datetime arg_bg_changetime, datetime arg_ed_changetime, integer arg_commitstep, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log);//f_trandata_online(arg_transaction,arg_obj_transaction,arg_zipfileinfo[300],arg_tableno,arg_if_usedefaultdt,arg_bg_changetime,arg_ed_changetime,arg_usechangetime,arg_commitstep,arg_if_buildlogfilearg_msg,arg_log) //arg_zipfileinfo[300] :要导出的表的名称列表 //arg_tableno //arg_if_usedefaultdt:true -自动时间范围 //arg_bg_changetime :时间范围提取-开始时间 //arg_ed_changetime :时间范围提取-结束时间 //arg_commitstep //arg_if_buildlogfile //arg_msg //arg_log :返回日志内容 // //1.循环:(源事务)动态建立DW\retrieve //2. (目标事务)循环生成更新语句 //3. 执行更新(注意不定时访问源事务,保证连接,如果连接失败,重试,如果也失败,就终止下一表数据提取) string str_export_table_info='' long exprot_suc=0,export_fail=0,rowc,del_fail,del_suc,rowcount_update_false,rowcount_update_ok long rslt_insert_rowcount,rslt_update_rowcount,ins_c,upt_c long rslt=1,l_rslt=1 int li_FileNum,int_tabletype int exporttype=0 s_sys_tntblinfo ls_tntblinfo string cur_path,ls_filepathname,ls_sc_id_name,ls_Indcolumn_name,ls_msg,msg_err_sql,ls_temppathname long ls_i,ls_j,ls_k,lg_rowcount,lg_rslt string char_enter string str_dwSQl,str_SyntaxFromSQL,del_sqlstr int i_cgt,i_udf long PK1_sit,PK2_sit,PK3_sit,PK4_sit string ls_thr_Indcolumn_name,ls_fou_Indcolumn_name arg_log="" datetime datatrans_timeline,D_newlydt,bg_changetime,ed_changetime,dg_newlydt string field_str,obj_value,SelectPri1,SelectPri2,SelectPri3,SelectPri4,UpdateField,update_string,value_str char_enter=char(13)+char(10) //换行符 open(w_sys_wait_jdt) //初始化进度条 w_sys_wait_jdt.show() w_sys_wait_jdt.wf_accepttol(arg_tableno+2) //取得当前绝对路径 cur_path=sys_cur_path select top 1 getdate() into :datatrans_timeline from sysusers using arg_obj_transaction; if arg_obj_transaction.sqlcode<>0 then arg_msg='取得服务器时间操作失败!' rslt=0 goto ext end if if not arg_if_usedefaultdt then bg_changetime=arg_bg_changetime ed_changetime=arg_ed_changetime else ed_changetime=datatrans_timeline end if arg_log='>>'+string(datatrans_timeline)+'<<'+char_enter ls_k=0 rslt=1 //////////////////////优先处理删除日志表 dts_deletelog for ls_i=1 to arg_tableno //查找删除日志表 rowcount_update_false=0 arg_zipfileinfo[ls_i].table_name=lower(arg_zipfileinfo[ls_i].table_name) if arg_zipfileinfo[ls_i].table_name=dts_deletelog then w_sys_wait_jdt.st_msg.text="预处理[删除日志表]:"+arg_zipfileinfo[ls_i].table_name //进度信息 if arg_if_usedefaultdt then//应用自动提取更新时间范围功能 rslt=uf_Getrsl_trandt(arg_transaction,arg_scid,arg_zipfileinfo[ls_i].table_name,d_newlydt,dg_newlydt,msg_err_sql) if rslt=0 then arg_log=arg_log+"[删除日志表]同步时间读取失败>>"+msg_err_sql+char_enter export_fail++ exit end if bg_changetime=d_newlydt end if //生成select语句 str_dwSQl="select "+arg_zipfileinfo[ls_i].table_name+".* from "+arg_zipfileinfo[ls_i].table_name //生成DW ls_msg="" ////////////////////////////////////////////////////////////////////////////////////// //str_SyntaxFromSQL =arg_obj_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) str_SyntaxFromSQL =sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"dw语法生成失败" arg_log=arg_log+arg_msg+char_enter export_fail++ exit end if ds_data.Create(str_SyntaxFromSQL,ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"建立相关DW失败" arg_log=arg_log+arg_msg+char_enter export_fail++ exit end if //检查字段,修正select语句的where子句 i_udf=0 i_cgt=0 for ls_j=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_j)+".Name"))=Lower(sys_changetime) then i_cgt=1 //有此字段 end if next if i_cgt=1 then //生成sys_changetime where 子句 str_dwSQl=str_dwSQl+" where "+& sys_changetime+">='"+string(bg_changetime)+"' and "+& sys_changetime+"<='"+string(ed_changetime)+"'" end if if trim(arg_zipfileinfo[ls_i].filter_string)<>'' then if pos(lower(str_dwSQl)," where ")>0 then str_dwSQl=str_dwSQl+" and "+arg_zipfileinfo[ls_i].filter_string else str_dwSQl=str_dwSQl+" where "+arg_zipfileinfo[ls_i].filter_string end if end if ds_data.Modify("datawindow.table.select = ~"" + str_dwSQl+ "~"") //DW取数 ds_data.settransobject(arg_transaction) w_sys_wait_jdt.st_msg.text=arg_zipfileinfo[ls_i].table_name+" 正在取数据[删除日志表]..." //进度信息 lg_rowcount=ds_data.retrieve() if lg_rowcount=0 then arg_log=arg_log+">0 删除日志["+arg_zipfileinfo[ls_i].table_name+"]"+char_enter exit end if ////////////// for ls_j=1 to ds_data.rowcount() w_sys_wait_jdt.wf_inc(3*(ls_j/ds_data.rowcount())) w_sys_wait_jdt.st_msg.text="正在执行删除..."+string(ls_j)+"/"+string(ds_data.rowcount()) del_sqlstr=ds_data.object.sql_delete[ls_j] Execute immediate :del_sqlstr using arg_obj_transaction; //先执行delete if arg_obj_transaction.sqlcode<>0 then del_fail++ if len(msg_err_sql)<35000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+del_sqlstr+'}'+char_enter+'['+arg_transaction.sqlerrtext+']'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if arg_log=arg_log+" \"+string(ls_j)+"失败{"+del_sqlstr+"}"+char_enter else del_suc++ // arg_log=arg_log+" \"+string(ls_j)+"成功{"+del_sqlstr+"}"+char_enter end if if Mod(ls_j , arg_commitstep)=0 then //够提交步长则提交 commit using arg_obj_transaction; end if next ds_data.reset() commit using arg_obj_transaction; arg_log=arg_log+"<删除日志表更新>成功:"+string(del_suc)+"失败:"+string(del_fail)+char_enter+msg_err_sql if del_fail=0 then rslt=uf_setrsl_trandt(arg_transaction,arg_scid,arg_zipfileinfo[ls_i].table_name,ed_changetime,datetime(1900-01-01),arg_msg) if rslt=0 then end if end if exit end if next w_sys_wait_jdt.wf_inc(1) //进度[完成删除日志处理] /////////////////////////// for ls_i=1 to arg_tableno rowcount_update_ok=0 rowcount_update_false=0 w_sys_wait_jdt.st_msg.text="预处理[更新数据]:"+arg_zipfileinfo[ls_i].table_name //进度信息 arg_zipfileinfo[ls_i].table_name=lower(arg_zipfileinfo[ls_i].table_name) ds_data.reset() if trim(arg_zipfileinfo[ls_i].table_name)<>"" and arg_zipfileinfo[ls_i].table_name <> dts_deletelog then //<1>取出该文件信息 if f_get_tntblinfo(arg_zipfileinfo[ls_i].table_name,ls_tntblinfo)=1 then ls_sc_id_name=trim(ls_tntblinfo.Sc_id_name) ls_Indcolumn_name=trim(ls_tntblinfo.Indcolumn_name) ls_thr_Indcolumn_name=trim(ls_tntblinfo.thrindcolumn_name) ls_fou_Indcolumn_name=trim(ls_tntblinfo.fouindcolumn_name) int_tabletype=ls_tntblinfo.tabletype exporttype=ls_tntblinfo.exporttype else ls_msg=arg_zipfileinfo[ls_i].table_name+"不在系统记录中;" arg_log=arg_log+ls_msg+char_enter export_fail++ goto nexttable end if if arg_if_usedefaultdt then //应用自动提取更新时间范围功能 rslt=uf_Getrsl_trandt(arg_transaction,arg_scid,arg_zipfileinfo[ls_i].table_name,d_newlydt,dg_newlydt,msg_err_sql) if rslt=0 then arg_log=arg_log+"["+arg_zipfileinfo[ls_i].table_name+"]同步时间读取失败>>"+msg_err_sql+char_enter export_fail++ goto nexttable end if bg_changetime=d_newlydt end if //<2>生成select语句 str_dwSQl="select "+arg_zipfileinfo[ls_i].table_name+".* from "+arg_zipfileinfo[ls_i].table_name //<3>生成DW ls_msg="" //////////////////////////////////////////////////// //str_SyntaxFromSQL =arg_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) str_SyntaxFromSQL =sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"dw语法生成失败" arg_log=arg_log+arg_msg+char_enter export_fail++ goto nexttable end if ds_data.Create(str_SyntaxFromSQL,ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"建立相关DW失败" arg_log=arg_log+arg_msg+char_enter export_fail++ goto nexttable end if //<4>检查字段,修正select语句的where子句 i_udf=0 i_cgt=0 for ls_j=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_j)+".Name"))=Lower(sys_changetime) then i_cgt=1 //有此字段 end if next if i_cgt=1 then //生成sys_changetime where 子句 str_dwSQl=str_dwSQl+" where "+& sys_changetime+">='"+string(bg_changetime)+"' and "+& sys_changetime+"<='"+string(ed_changetime)+"'" end if if trim(arg_zipfileinfo[ls_i].filter_string)<>'' then if pos(lower(str_dwSQl)," where ")>0 then str_dwSQl=str_dwSQl+" and "+arg_zipfileinfo[ls_i].filter_string else str_dwSQl=str_dwSQl+" where "+arg_zipfileinfo[ls_i].filter_string end if end if ds_data.Modify("datawindow.table.select = ~"" + str_dwSQl+ "~"") //<5>DW取数 ds_data.settransobject(arg_transaction) w_sys_wait_jdt.st_msg.text=arg_zipfileinfo[ls_i].table_name+" 正在取数据..." //进度信息 lg_rowcount=ds_data.retrieve() if lg_rowcount>0 then //如果有数据,开始更新 ///////////////////////////////////////////////////////////////////////////////////////// PK1_sit=0 PK2_sit=0 //定位主关键字段位置,ls_Indcolumn_name为空则为单关键字表 for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_sc_id_name) then PK1_sit=ls_k //有此字段 //PK1_type=ds_data.Describe("#"+string(ls_k)+".type") end if if not ls_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_Indcolumn_name) then PK2_sit=ls_k end if end if if not ls_thr_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_thr_Indcolumn_name) then PK3_sit=ls_k end if end if if not ls_fou_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_fou_Indcolumn_name) then PK4_sit=ls_k end if end if next //ls_k if PK1_sit+PK2_sit=0 then arg_log=arg_log+'<'+string(ls_i)+">["+arg_zipfileinfo[ls_i].table_name+"]错误主键名称."+char_enter goto nexttable end if //<5>循环生成SQl语句,更新数据库 msg_err_sql='' ins_c=0 upt_c=0 for ls_j=1 to lg_rowcount field_str="" obj_value="" SelectPri1="" SelectPri2="" SelectPri3="" SelectPri4="" UpdateField="" update_string="" //取主关键字的内容 SelectPri1=iuf_getdwvalue(ds_data,ls_j,PK1_sit) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if if not ls_Indcolumn_name='' then //双主键 SelectPri2=iuf_getdwvalue(ds_data,ls_j,PK2_sit) if SelectPri2="[err]" then rowcount_update_false++ goto nextrow end if end if if not ls_thr_Indcolumn_name='' then //三主键 SelectPri3=iuf_getdwvalue(ds_data,ls_j,PK3_sit) if SelectPri3="[err]" then rowcount_update_false++ goto nextrow end if end if if not ls_fou_Indcolumn_name='' then //四主键 SelectPri4=iuf_getdwvalue(ds_data,ls_j,PK4_sit) if SelectPri4="[err]" then rowcount_update_false++ goto nextrow end if end if //++++++++生成更新子段语句++++++ //先更新记录--------------------------- for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if UpdateField=UpdateField+ds_data.describe("#"+string(ls_k)+".Name")+"="+obj_value+"," next //ls_k UpdateField=left(UpdateField,len(UpdateField) - 1) update_string="Update "+arg_zipfileinfo[ls_i].table_name+" set " +UpdateField + " where "+& ls_sc_id_name+"="+SelectPri1 if ls_Indcolumn_name<>'' then //双主键 update_string=update_string+" and "+ls_Indcolumn_name+"="+SelectPri2 end if if ls_thr_Indcolumn_name<>'' then //三主键 update_string=update_string+" and "+ls_thr_Indcolumn_name+"="+SelectPri3 end if if ls_fou_Indcolumn_name<>'' then //四主键 update_string=update_string+" and "+ls_fou_Indcolumn_name+"="+SelectPri4 end if Execute immediate :update_string using arg_obj_transaction; //执行Update if arg_obj_transaction.sqlcode<>0 then if len(msg_err_sql)<65000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter+'['+arg_obj_transaction.sqlerrtext+']'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rowcount_update_false++ goto nextrow else if arg_obj_transaction.sqlnrows=0 then //更新不成功就插入----------- field_str="" obj_value="" value_str='' update_string="" for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rowcount_update_false++ goto nextrow end if field_str=field_str+ds_data.describe("#"+string(ls_k)+".Name")+"," value_str=value_str+obj_value+"," next //ls_k field_str=left(field_str,len(field_str)-1) value_str=left(value_str,len(value_str)-1) update_string="Insert into "+arg_zipfileinfo[ls_i].table_name+" ("+field_str+& ") values ("+value_str+")" Execute immediate :update_string using arg_obj_transaction; //执行Insert if arg_obj_transaction.sqlcode<>0 then if len(msg_err_sql)<65000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rowcount_update_false++ goto nextrow end if rslt_insert_rowcount++ ins_c++ else rslt_update_rowcount++ upt_c++ end if end if rowcount_update_ok++ if Mod(ls_j , arg_commitstep)=0 then //够提交步长则提交 commit using arg_obj_transaction; end if nextrow: w_sys_wait_jdt.st_msg.text="正在执行更新... >"+arg_zipfileinfo[ls_i].table_name+">"+string(rowcount_update_ok)+'/'+string(ls_j) next //ls_j arg_log=arg_log+'<'+string(ls_i)+">["+arg_zipfileinfo[ls_i].table_name+"]成功:"+string(rowcount_update_ok)+"(更新-"+string(upt_c)+"/插入-"+string(ins_c)+")失败:"+string(rowcount_update_false)+"."+char_enter if trim(msg_err_sql)<>'' then arg_log=arg_log+msg_err_sql+char_enter commit using arg_obj_transaction; ///////////////////////////////////////////////////////////////////////////////////////// else arg_log=arg_log+'<'+string(ls_i)+">0 ["+arg_zipfileinfo[ls_i].table_name+"]"+char_enter end if end if exprot_suc++ nexttable: commit using arg_obj_transaction; //记录最近完成的时间 if rowcount_update_false=0 and rslt=1 then rslt=uf_setrsl_trandt(arg_transaction,arg_scid,arg_zipfileinfo[ls_i].table_name,ed_changetime,datetime(1900-01-01),arg_msg) if rslt=0 then arg_log=arg_log+'更新完成时间失败>'+arg_msg+char_enter end if end if w_sys_wait_jdt.wf_inc(ls_i+1) //进度 //end if next//ls_i arg_log=arg_log+"数据表导出:成功表-"+string(exprot_suc)+",失败-"+string(export_fail)+'[记录合计:插入-'+string(rslt_insert_rowcount)+'/更新-'+string(rslt_update_rowcount)+']'+char_enter arg_log=arg_log+"导出记录数合计:"+string(rowc)+char_enter w_sys_wait_jdt.wf_inc(arg_tableno+1) //进度完成 w_sys_wait_jdt.st_msg.text="正在建立日志文件..." ls_temppathname=cur_path+importlogpath+'\'+"YF_Import_online"+string(today(),'yyyymmdd')+string(now(),'hhmmss')+".log" if arg_if_buildlogfile then rslt=pf_writetifofile(arg_log,ls_temppathname,arg_msg) if rslt=0 then arg_msg="写日志文件失败>>"+arg_msg rslt=1 end if end if ext: close(w_sys_wait_jdt) return rslt end function public function integer f_tranmstpdata_online (ref transaction arg_transaction, ref transaction arg_obj_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log);//f_tranmstpdata_online(arg_transaction,arg_obj_transaction,arg_zipfileinfo[300],arg_tableno,arg_if_buildlogfile,arg_msg,arg_log) //在线取未结清应收应付单据 //arg_zipfileinfo[300] :要导出的表的名称列表 //arg_tableno //arg_if_buildlogfile //arg_msg //arg_log :返回日志内容 // //1.循环:(源事务)动态建立DW\retrieve //2. (目标事务)循环生成更新语句,更新不成功则新建,(源事务)删除或变为历史数据,两事务同时成功,同时失败 //3. 执行更新(注意不定时访问源事务,保证连接,如果连接失败,重试,如果也失败,就终止下一表数据提取) string str_export_table_info='' long exprot_suc=0,export_fail=0,rowc,del_fail,del_suc,rowcount_update_false,rowcount_update_ok long rslt_insert_rowcount,rslt_update_rowcount,ins_c,upt_c long rslt=1,l_rslt=1 int li_FileNum,int_tabletype int exporttype=0 s_sys_tntblinfo ls_tntblinfo string cur_path,ls_filepathname,ls_sc_id_name,ls_Indcolumn_name,ls_msg,msg_err_sql,ls_temppathname long ls_i,ls_j,ls_k,lg_rowcount,lg_rslt string char_enter string str_dwSQl,str_SyntaxFromSQL,del_sqlstr long PK1_sit,PK2_sit,PK3_sit,PK4_sit string ls_thr_Indcolumn_name,ls_fou_Indcolumn_name arg_log="" string field_str,obj_value,SelectPri1,SelectPri2,SelectPri3,SelectPri4,UpdateField,update_string,value_str char_enter=char(13)+char(10) //换行符 open(w_sys_wait_jdt) //初始化进度条 w_sys_wait_jdt.show() w_sys_wait_jdt.wf_accepttol(arg_tableno+2) //取得当前绝对路径 cur_path=sys_cur_path /////////////////////////////(目标事务)处理转移单据 for ls_i=1 to arg_tableno rowcount_update_ok=0 rowcount_update_false=0 w_sys_wait_jdt.st_msg.text="预处理[更新数据]:"+arg_zipfileinfo[ls_i].table_name //进度信息 arg_zipfileinfo[ls_i].table_name=lower(arg_zipfileinfo[ls_i].table_name) ds_data.reset() if trim(arg_zipfileinfo[ls_i].table_name)<>"" then //<1>取出该文件信息 if f_get_tntblinfo(arg_zipfileinfo[ls_i].table_name,ls_tntblinfo)=1 then ls_sc_id_name=trim(ls_tntblinfo.Sc_id_name) ls_Indcolumn_name=trim(ls_tntblinfo.Indcolumn_name) ls_thr_Indcolumn_name=trim(ls_tntblinfo.thrindcolumn_name) ls_fou_Indcolumn_name=trim(ls_tntblinfo.fouindcolumn_name) int_tabletype=ls_tntblinfo.tabletype exporttype=ls_tntblinfo.exporttype else ls_msg=arg_zipfileinfo[ls_i].table_name+"不在系统记录中;" arg_log=arg_log+ls_msg+char_enter rslt=0 goto ext end if //<2>生成select语句 str_dwSQl="select "+arg_zipfileinfo[ls_i].table_name+".* from "+arg_zipfileinfo[ls_i].table_name //<3>生成DW ls_msg="" ////////////////////////////////////////////////////////////////////////// //str_SyntaxFromSQL =arg_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) str_SyntaxFromSQL =sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"dw语法生成失败" arg_log=arg_log+arg_msg+char_enter rslt=0 goto ext end if ds_data.Create(str_SyntaxFromSQL,ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"建立相关DW失败" arg_log=arg_log+arg_msg+char_enter rslt=0 goto ext end if //<4>检查字段,修正select语句的where子句 if trim(arg_zipfileinfo[ls_i].filter_string)<>'' then if pos(lower(str_dwSQl)," where ")>0 then str_dwSQl=str_dwSQl+" and "+arg_zipfileinfo[ls_i].filter_string else str_dwSQl=str_dwSQl+" where "+arg_zipfileinfo[ls_i].filter_string end if end if ds_data.Modify("datawindow.table.select = ~"" + str_dwSQl+ "~"") //<5>DW取数 ds_data.settransobject(arg_transaction) w_sys_wait_jdt.st_msg.text=arg_zipfileinfo[ls_i].table_name+" 正在取数据..." //进度信息 lg_rowcount=ds_data.retrieve() if lg_rowcount>0 then //如果有数据,开始更新 ///////////////////////////////////////////////////////////////////////////////////////// PK1_sit=0 PK2_sit=0 //定位主关键字段位置,ls_Indcolumn_name为空则为单关键字表 for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_sc_id_name) then PK1_sit=ls_k //有此字段 end if if not ls_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_Indcolumn_name) then PK2_sit=ls_k end if end if if not ls_thr_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_thr_Indcolumn_name) then PK3_sit=ls_k end if end if if not ls_fou_Indcolumn_name='' then if Lower(ds_data.Describe("#"+string(ls_k)+".Name"))=Lower(ls_fou_Indcolumn_name) then PK4_sit=ls_k end if end if next //ls_k if PK1_sit+PK2_sit=0 then arg_log=arg_log+'<'+string(ls_i)+">["+arg_zipfileinfo[ls_i].table_name+"]错误主键名称."+char_enter rslt=0 goto ext end if //<5>循环生成SQl语句,更新数据库 msg_err_sql='' ins_c=0 upt_c=0 for ls_j=1 to lg_rowcount field_str="" obj_value="" SelectPri1="" SelectPri2="" SelectPri3="" SelectPri4="" UpdateField="" update_string="" //取主关键字的内容 SelectPri1=iuf_getdwvalue(ds_data,ls_j,PK1_sit) if SelectPri1="[err]" then rslt=0 goto ext end if if not ls_Indcolumn_name='' then //双主键 SelectPri2=iuf_getdwvalue(ds_data,ls_j,PK2_sit) if SelectPri2="[err]" then rslt=0 goto ext end if end if if not ls_thr_Indcolumn_name='' then //三主键 SelectPri3=iuf_getdwvalue(ds_data,ls_j,PK3_sit) if SelectPri3="[err]" then rslt=0 goto ext end if end if if not ls_fou_Indcolumn_name='' then //四主键 SelectPri4=iuf_getdwvalue(ds_data,ls_j,PK4_sit) if SelectPri4="[err]" then rslt=0 goto ext end if end if //++++++++生成更新子段语句++++++ //先更新记录--------------------------- for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rslt=0 goto ext end if UpdateField=UpdateField+ds_data.describe("#"+string(ls_k)+".Name")+"="+obj_value+"," next //ls_k UpdateField=left(UpdateField,len(UpdateField) - 1) update_string="Update "+arg_zipfileinfo[ls_i].table_name+" set " +UpdateField + " where "+& ls_sc_id_name+"="+SelectPri1 if ls_Indcolumn_name<>'' then //双主键 update_string=update_string+" and "+ls_Indcolumn_name+"="+SelectPri2 end if if ls_thr_Indcolumn_name<>'' then //三主键 update_string=update_string+" and "+ls_thr_Indcolumn_name+"="+SelectPri3 end if if ls_fou_Indcolumn_name<>'' then //四主键 update_string=update_string+" and "+ls_fou_Indcolumn_name+"="+SelectPri4 end if Execute immediate :update_string using arg_obj_transaction; //执行Update if arg_obj_transaction.sqlcode<>0 then if len(msg_err_sql)<65000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter+'['+arg_obj_transaction.sqlerrtext+']'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rslt=0 goto ext else if arg_obj_transaction.sqlnrows=0 then //更新不成功就插入----------- field_str="" obj_value="" value_str='' update_string="" for ls_k=1 to integer(ds_data.Object.DataWindow.Column.Count) obj_value=iuf_getdwvalue(ds_data,ls_j,ls_k) if SelectPri1="[err]" then rslt=0 goto ext end if field_str=field_str+ds_data.describe("#"+string(ls_k)+".Name")+"," value_str=value_str+obj_value+"," next //ls_k field_str=left(field_str,len(field_str)-1) value_str=left(value_str,len(value_str)-1) update_string="Insert into "+arg_zipfileinfo[ls_i].table_name+" ("+field_str+& ") values ("+value_str+")" Execute immediate :update_string using arg_obj_transaction; //执行Insert if arg_obj_transaction.sqlcode<>0 then if len(msg_err_sql)<65000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rslt=0 goto ext end if rslt_insert_rowcount++ ins_c++ else rslt_update_rowcount++ upt_c++ end if end if rowcount_update_ok++ w_sys_wait_jdt.st_msg.text="正在执行更新... >"+arg_zipfileinfo[ls_i].table_name+">"+string(rowcount_update_ok)+'/'+string(ls_j) next //ls_j arg_log=arg_log+'<'+string(ls_i)+">["+arg_zipfileinfo[ls_i].table_name+"]成功:"+string(rowcount_update_ok)+"(更新-"+string(upt_c)+"/插入-"+string(ins_c)+")失败:"+string(rowcount_update_false)+"."+char_enter if trim(msg_err_sql)<>'' then arg_log=arg_log+msg_err_sql+char_enter ///////////////////////////////////////////////////////////////////////////////////////// else arg_log=arg_log+'<'+string(ls_i)+">0 ["+arg_zipfileinfo[ls_i].table_name+"]"+char_enter end if end if exprot_suc++ w_sys_wait_jdt.wf_inc(ls_i+1) //进度 next//ls_i /////////////////////////////(原事务)删除转移单据 for ls_i=1 to arg_tableno w_sys_wait_jdt.st_msg.text="预处理[删除数据]:"+arg_zipfileinfo[ls_i].table_name //进度信息 del_sqlstr="" //更新或新建成功则在源事务中删除或设为历史//生成删除语句 del_sqlstr="delete from "+arg_zipfileinfo[ls_i].table_name + " where "+arg_zipfileinfo[ls_i].filter_string Execute immediate :del_sqlstr using arg_transaction; //执行delete if arg_transaction.sqlcode<>0 then if len(msg_err_sql)<65000 then msg_err_sql=msg_err_sql+' /'+string(ls_j)+' Error SQL{'+update_string+'}'+char_enter else if left(right(msg_err_sql,5),3)<>'...' then msg_err_sql=msg_err_sql+'...'+char_enter end if rslt=0 goto ext end if w_sys_wait_jdt.wf_inc(ls_i+1) //进度 next//ls_i arg_log=arg_log+"数据表导出:成功表-"+string(exprot_suc)+",失败-"+string(export_fail)+'[记录合计:插入-'+string(rslt_insert_rowcount)+'/更新-'+string(rslt_update_rowcount)+']'+char_enter w_sys_wait_jdt.wf_inc(arg_tableno+1) //进度完成 w_sys_wait_jdt.st_msg.text="正在建立日志文件..." ls_temppathname=cur_path+importlogpath+'\'+"YF_Import_online"+string(today(),'yyyymmdd')+string(now(),'hhmmss')+".log" if arg_if_buildlogfile then l_rslt=pf_writetifofile(arg_log,ls_temppathname,arg_msg) if l_rslt=0 then arg_msg="写日志文件失败>>"+arg_msg end if ext: if rslt=0 then rollback using arg_obj_transaction; rollback using arg_transaction; else commit using arg_obj_transaction; commit using arg_transaction; end if close(w_sys_wait_jdt) return rslt end function public function integer f_exportdata (transaction arg_transaction, s_exporttableinfo arg_zipfileinfo[300], integer arg_tableno, datetime arg_bg_changetime, datetime arg_ed_changetime, integer arg_usechangetime, integer arg_getflag, string arg_exportfilename, boolean arg_if_buildlogfile, ref string arg_msg, ref string arg_log);//f_exportdata //arg_zipfileinfo[] :要导出的表的名称列表 //arg_bg_changetime :时间范围提取-开始时间 //arg_ed_changetime :时间范围提取-结束时间 //arg_usechangetime :是否应用时间范围提取(除了没有改变时间字段的)记录,1-是,0否 //arg_getflag :是否只取更新标记的(除了没有更新标记字段的)记录1-是,0否 //arg_exportfilename:生成的目标文件名称 //arg_log :返回日志内容 //1.删除临时目录中的相应DBF //2.循环:动态建立DW\retrieve\导出DBF(文件名称为表名称) //3.压缩文件为目标文件 string str_export_table_info='' string DBFfilename[] long exprot_suc=0,export_fail=0,rowc long rslt=1,l_rslt=1 int li_FileNum,int_tabletype int exporttype=0 s_sys_tntblinfo ls_tntblinfo string cur_path,ls_filepathname,ls_sc_id_name,ls_Indcolumn_name,ls_msg long ls_i,ls_j,ls_k,lg_rowcount,lg_rslt string char_enter string str_dwSQl,str_SyntaxFromSQL int i_cgt,i_udf arg_log="" char_enter=char(13)+char(10) //换行符 open(w_sys_wait_jdt) //初始化进度条 If Pos(arg_exportfilename, ":\") <= 0 Then arg_msg= "必须输入完整的目标文件文件路径." rslt=0 GoTo ext End If w_sys_wait_jdt.show() w_sys_wait_jdt.wf_accepttol(arg_tableno+2) //取得当前绝对路径 cur_path=sys_cur_path //删除原有文件 for ls_i=1 to arg_tableno if trim(arg_zipfileinfo[ls_i].table_name)<>"" then ls_filepathname=cur_path+exporttempfilepath+'\'+trim(arg_zipfileinfo[ls_i].table_name)+".dbf" if FileExists(ls_filepathname) then if not FileDelete(ls_filepathname) then rslt=0 arg_msg="删除临时文件失败("+ls_filepathname+"),请先手动清除!" goto ext end if end if end if next ls_filepathname=cur_path+exporttempfilepath+'\'+infofilename if FileExists(ls_filepathname) then if not FileDelete(ls_filepathname) then rslt=0 arg_msg="删除临时文件失败("+ls_filepathname+"),请先手动清除!" goto ext end if end if //循环生成DBF或blb ls_k=0 for ls_i=1 to arg_tableno setnull(blbdw_data) //if trim(arg_zipfileinfo[ls_i].table_name)<>"" then w_sys_wait_jdt.st_msg.text="预处理:"+arg_zipfileinfo[ls_i].table_name //进度信息 arg_zipfileinfo[ls_i].table_name=lower(arg_zipfileinfo[ls_i].table_name) ds_data.reset() if trim(arg_zipfileinfo[ls_i].table_name)<>"" then //<1>取出该文件信息 if f_get_tntblinfo(arg_zipfileinfo[ls_i].table_name,ls_tntblinfo)=1 then ls_sc_id_name=trim(ls_tntblinfo.Sc_id_name) ls_Indcolumn_name=trim(ls_tntblinfo.Indcolumn_name) int_tabletype=ls_tntblinfo.tabletype exporttype=ls_tntblinfo.exporttype else ls_msg=arg_zipfileinfo[ls_i].table_name+"不在系统记录中;" arg_log=arg_log+ls_msg+char_enter export_fail++ goto nexttable end if //<2>生成select语句 if arg_zipfileinfo[ls_i].dw_creattype = 1 then //直接用filter_string作str_dwSQl str_dwSQl=arg_zipfileinfo[ls_i].filter_string else str_dwSQl="select "+arg_zipfileinfo[ls_i].table_name+".* from "+arg_zipfileinfo[ls_i].table_name end if //<3>生成DW ls_msg="" str_SyntaxFromSQL =arg_transaction.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"dw语法生成失败" arg_log=arg_log+arg_msg+char_enter export_fail++ goto nexttable end if ds_data.Create(str_SyntaxFromSQL,ls_msg) if ls_msg>'' then arg_msg=arg_zipfileinfo[ls_i].table_name+"建立相关DW失败" arg_log=arg_log+arg_msg+char_enter export_fail++ goto nexttable end if //<4>检查字段,修正select语句的where子句 if not arg_zipfileinfo[ls_i].dw_creattype = 1 then i_udf=0 i_cgt=0 for ls_j=1 to integer(ds_data.Object.DataWindow.Column.Count) if Lower(ds_data.Describe("#"+string(ls_j)+".Name"))=Lower(SYS_CHANGEFLAG) then i_udf=1 //有此字段 end if if Lower(ds_data.Describe("#"+string(ls_j)+".Name"))=Lower(sys_changetime) then i_cgt=1 //有此字段 end if next if i_udf=1 and arg_getflag=1 then //通过更新标记提取 if i_cgt=1 and arg_usechangetime=1 then //通过 str_dwSQl=str_dwSQl+" where "+SYS_CHANGEFLAG+"=1 and "+& sys_changetime+">='"+string(arg_bg_changetime)+"' and "+& sys_changetime+"<='"+string(arg_ed_changetime)+"'" else str_dwSQl=str_dwSQl+" where "+SYS_CHANGEFLAG+"=1 " end if else if i_cgt=1 and arg_usechangetime=1 then str_dwSQl=str_dwSQl+" where "+& sys_changetime+">='"+string(arg_bg_changetime)+"' and "+& sys_changetime+"<='"+string(arg_ed_changetime)+"'" else end if end if if trim(arg_zipfileinfo[ls_i].filter_string)<>'' then if pos(lower(str_dwSQl)," where ")>0 then str_dwSQl=str_dwSQl+" and "+arg_zipfileinfo[ls_i].filter_string else str_dwSQl=str_dwSQl+" where "+arg_zipfileinfo[ls_i].filter_string end if end if ds_data.Modify("datawindow.table.select = ~"" + str_dwSQl+ "~"") end if //<5>DW取数 ds_data.settransobject(arg_transaction) w_sys_wait_jdt.st_msg.text=arg_zipfileinfo[ls_i].table_name+" 正在取数据..." //进度信息 lg_rowcount=ds_data.retrieve() if lg_rowcount>0 then //如果有数据 //<6>导出文件 if exporttype=1 then //导出blob ds_data.SetItemStatus(1, 0, Primary!, dataModified!) ls_filepathname=cur_path+exporttempfilepath+'\'+trim(arg_zipfileinfo[ls_i].table_name+'.blb') l_rslt=ds_data.getFullState(blbdw_data) if l_rslt>0 then lg_rslt=1 ds_data.reset() else lg_rslt=0 export_fail++ goto nexttable end if lg_rslt=f_saveblobtofile(blbdw_data,ls_filepathname,arg_msg) if lg_rslt=1 then //如果成功,写日志内容 ls_k++ DBFfilename[ls_k]=ls_filepathname arg_log=arg_log+">"+string(lg_rowcount)+" ["+str_dwSQl+"]"+char_enter rowc=rowc+lg_rowcount str_export_table_info=str_export_table_info+arg_zipfileinfo[ls_i].table_name+';' else arg_log=arg_log+"["+arg_zipfileinfo[ls_i].table_name+"]导出blob失败."+char_enter export_fail++ goto nexttable end if elseif exporttype=2 then //导出txt ls_filepathname=cur_path+exporttempfilepath+'\'+trim(arg_zipfileinfo[ls_i].table_name+'.txt') lg_rslt=ds_data.saveas(ls_filepathname,text!, false) if lg_rslt=1 then //如果成功,写日志内容 ls_k++ DBFfilename[ls_k]=ls_filepathname arg_log=arg_log+">"+string(lg_rowcount)+" ["+str_dwSQl+"]"+char_enter rowc=rowc+lg_rowcount str_export_table_info=str_export_table_info+arg_zipfileinfo[ls_i].table_name+';' else arg_log=arg_log+"["+arg_zipfileinfo[ls_i].table_name+"]导出txt失败."+char_enter export_fail++ goto nexttable end if else //DW Save as DBF ls_filepathname=cur_path+exporttempfilepath+'\'+trim(arg_zipfileinfo[ls_i].table_name+'.DBF') lg_rslt=ds_data.saveas(ls_filepathname,dBASE3!, true) if lg_rslt=1 then //如果成功,写日志内容 ls_k++ DBFfilename[ls_k]=ls_filepathname arg_log=arg_log+">"+string(lg_rowcount)+" ["+str_dwSQl+"]"+char_enter rowc=rowc+lg_rowcount str_export_table_info=str_export_table_info+arg_zipfileinfo[ls_i].table_name+';' else arg_log=arg_log+"["+arg_zipfileinfo[ls_i].table_name+"]导出DBF失败."+char_enter export_fail++ goto nexttable end if end if else arg_log=arg_log+">0 ["+arg_zipfileinfo[ls_i].table_name+"]"+char_enter end if end if exprot_suc++ nexttable: w_sys_wait_jdt.wf_inc(ls_i) //进度 //end if next arg_log=arg_log+"数据表导出:成功-"+string(exprot_suc)+",失败-"+string(export_fail)+char_enter arg_log=arg_log+"导出记录数合计:"+string(rowc)+char_enter w_sys_wait_jdt.st_msg.text="写信息文件..." //写信息文件 ls_filepathname=cur_path+exporttempfilepath+'\'+infofilename if str_export_table_info<>"" then rslt=pf_writetifofile(str_export_table_info,ls_filepathname,arg_msg) if rslt=0 then arg_msg="写信息文件失败>>"+arg_msg goto ext end if else arg_log="[无提取内容]"+char_enter+arg_log rslt=1 goto ext end if ls_k++ DBFfilename[ls_k]=ls_filepathname w_sys_wait_jdt.st_msg.text="压缩文件..." // 压缩文件 rslt=uf_zipfile(DBFfilename,arg_exportfilename,arg_msg) if rslt=0 then arg_msg="文件压缩失败>>"+arg_msg goto ext end if arg_log="生成目标文件:"+arg_exportfilename+char_enter+arg_log w_sys_wait_jdt.wf_inc(arg_tableno+1) //进度完成 w_sys_wait_jdt.st_msg.text="删除文件..." //删除原有文件 string ls_arg_msg for ls_i=1 to arg_tableno if trim(arg_zipfileinfo[ls_i].table_name)<>"" then ls_filepathname=cur_path+exporttempfilepath+'\'+trim(arg_zipfileinfo[ls_i].table_name)+".dbf" if FileExists(ls_filepathname) then if not FileDelete(ls_filepathname) then arg_msg="删除临时文件失败("+ls_filepathname+")! " ls_arg_msg=ls_arg_msg + arg_msg end if end if end if next ls_filepathname=cur_path+exporttempfilepath+'\'+infofilename if FileExists(ls_filepathname) then if not FileDelete(ls_filepathname) then arg_msg="删除临时文件失败("+ls_filepathname+")!" ls_arg_msg=ls_arg_msg + arg_msg end if end if w_sys_wait_jdt.wf_inc(arg_tableno+2) //进度完成 ///// ls_filepathname=cur_path+exportlogpath+'\'+"LongJoe_EXP"+string(today(),'yyyymmdd')+string(now(),'hhmmss')+".log" if arg_if_buildlogfile then rslt=pf_writetifofile(arg_log,ls_filepathname,arg_msg) if rslt=0 then arg_msg="写日志文件失败>>"+arg_msg rslt=1 end if end if // ls_arg_msg=ls_arg_msg + arg_msg arg_msg=ls_arg_msg // ext: close(w_sys_wait_jdt) return rslt end function public function integer f_exportdata_blob (integer arg_usechangetime, datetime arg_bg_changtime, datetime arg_ed_changtime, string arg_exportfilename, string arg_exportbfilename, integer arg_info, ref string arg_msg);//将导出文件处理转blob再写入文件 //f_exportdata_blob(arg_usechangetime,arg_bg_changtime,arg_ed_changtime,arg_exportfilename,arg_exportbfilename,arg_info,arg_msg) //headinfo=sys_system_id + arg_info + arg_usechangetime + arg_bg_changtime + arg_ed_changtime //arg_info 0:定义数据包 // 1:分部数据包 // -1:历史数据包 int rslt=1 blob blob_zipfile,blob_r_headinfo,blob_exportfilename string headinfo,trailinfo,r_headinfo if FileExists(arg_exportbfilename) then if not FileDelete(arg_exportbfilename) then rslt=0 arg_msg="删除临时文件失败("+arg_exportbfilename+"),请先手动清除!" goto ext end if end if headinfo=headinfo + sys_system_id +';' headinfo=headinfo + string(arg_info) +';' headinfo=headinfo + string(arg_usechangetime) +';' headinfo=headinfo + string(arg_bg_changtime,'yyyy-mm-dd hh:mm:ss') +';' headinfo=headinfo + string(arg_ed_changtime,'yyyy-mm-dd hh:mm:ss') +';' trailinfo=fill('*',len_headinfo - len(headinfo)) r_headinfo=headinfo + trailinfo r_headinfo=f_psw_bczh(r_headinfo,0,sys_power_key) //加密 blob_r_headinfo=blob(r_headinfo) //1.zipfile -> blob if f_getblobfromfile(blob_zipfile,arg_exportfilename,arg_msg)=0 then rslt=0 goto ext end if //2.blob_exportfilename -> file blob_exportfilename=blob_r_headinfo + blob_zipfile if f_saveblobtofile(blob_exportfilename,arg_exportbfilename,arg_msg)=0 then rslt=0 goto ext end if ext: return rslt end function public function integer f_delzipfile (string arg_exportfilename, ref string arg_msg);//删除临时建立的压缩文件 int rslt=1 if FileExists(arg_exportfilename) then if not FileDelete(arg_exportfilename) then rslt=0 arg_msg="删除临时压缩文件失败("+arg_exportfilename+"),请手动清除!" goto ext end if end if ext: return rslt end function public function integer f_importdata_blob (string arg_importfilename, string arg_zipfile, ref integer arg_info, ref integer arg_usechangetime, ref datetime arg_bg_changetime, ref datetime arg_ed_changtime, ref string arg_msg);//将文件导入分解出headinfo ,zipfile //f_importdata_blob(arg_importfilename,arg_zipfile,arg_info,arg_usechangetime,arg_bg_changetime,arg_ed_changtime,arg_msg) //1.导入文件变blob型 //2.取headinfo,检查sys_system_id //3.生成zipfile,返回导入数据类型,时间等参数 int rslt=1 blob blob_zipfile,blob_r_headinfo,blob_importfilename string bczh_headinfo,headinfo,ls_system_id,info[] int ls_k,ls_psc //1.arg_importfilename -> blob if f_getblobfromfile(blob_importfilename,arg_importfilename,arg_msg)=0 then rslt=0 goto ext end if //2.取headinfo,检查sys_system_id bczh_headinfo=left(string(blob_importfilename),len_headinfo * 3) headinfo=f_psw_bczh(bczh_headinfo,1,sys_power_key) //解密 ls_k=0 ls_psc=0 ls_psc=pos(headinfo,';') DO WHILE ls_psc>0 if left(headinfo,ls_psc - 1)<>'' then ls_k++ info[ls_k]=left(headinfo,ls_psc - 1) end if headinfo=right(headinfo,len(headinfo) - ls_psc) ls_psc=pos(headinfo,';') LOOP if ls_k <> 5 then arg_msg='信息段检查失败,可能数据包已破坏' rslt=0 goto ext end if ls_system_id=info[1] arg_info=integer(info[2]) arg_usechangetime=integer(info[3]) arg_bg_changetime=datetime(date(left(info[4],10)),time(right(info[4],8))) arg_ed_changtime=datetime(date(left(info[5],10)),time(right(info[5],8))) if ls_system_id <> sys_system_id then arg_msg='导入的不是本系统的数据包,请检查' rslt=0 goto ext end if //3.生成zipfile,返回导入数据类型,时间等参数 blob_zipfile=BlobMid ( blob_importfilename,len(bczh_headinfo) + 1 ) if f_saveblobtofile(blob_zipfile,arg_zipfile,arg_msg)=0 then rslt=0 goto ext end if ext: return rslt end function on uo_yfimex.create TriggerEvent( this, "constructor" ) end on on uo_yfimex.destroy TriggerEvent( this, "destructor" ) end on event constructor;ds_data=create datastore end event