Q5/ws_objects/Ew_datatran_uo.pbl.src/uo_yfct.sru

$PBExportHeader$uo_yfct.sru
forward
global type uo_yfct from nonvisualobject
end type
end forward

global type uo_yfct from nonvisualobject
end type
global uo_yfct uo_yfct

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

end variables

forward prototypes
public function integer check_import_dt (integer arg_xporttype, datetime arg_bgdt, datetime arg_eddt, ref string arg_msg)
public function integer check_no_finish (datetime arg_ctdt, ref string arg_log, ref string arg_msg)
public function integer pf_writetifofile (string arg_tableinfo, string arg_objfilename, ref string arg_msg)
public function integer add_ct_datamx (long arg_ct_id, datetime arg_bg_dt, datetime arg_ed_dt, long arg_err_num, long arg_ct_delerrnum, ref string arg_msg, integer arg_type, transaction arg_sqlca)
public function integer init_ct (boolean arg_ifcheck_dt, datetime arg_ctdt, ref string arg_msg, ref string arg_log, boolean arg_ifcommit)
public function integer del_ct_data (datetime arg_edctdt, long arg_ct_id, long arg_bg_int, long arg_ed_int, integer arg_commit_step, ref string arg_msg, ref string arg_log, boolean arg_if_buildlogfile)
private function integer p_update_ctmint (string arg_etbname, string arg_ctbname, string arg_wherestr, string arg_fromstr, string arg_linkstr, long arg_value, ref string arg_msg, ref long arg_sucnum)
end prototypes

public function integer check_import_dt (integer arg_xporttype, datetime arg_bgdt, datetime arg_eddt, ref string arg_msg);//检查导入导出日期
//check_import_dt(arg_xporttype,arg_bgdt,arg_eddt,arg_msg)
//arg_xporttype 0:导出 1:导入
int rslt=1
long cnt 
datetime max_eddt

select max(ct_eddt) into :max_eddt from sys_ct_datamx ;
if sqlca.sqlcode = - 1 then
	if arg_xporttype=0 then
		arg_msg='查询最后导出日期失败'
	else
		arg_msg='查询最后导入日期失败'
	end if
	rslt=0
	goto ext
elseif sqlca.sqlcode=100 then
	rslt=1
	goto ext
end if

if arg_xporttype=0 then
	if arg_bgdt > max_eddt then
		select count(*) into :cnt from sys_ct_data where ct_datetime > :max_eddt and ct_datetime < :arg_bgdt;
		if sqlca.sqlcode<>0 then
			arg_msg='查询日期失败'
			rslt=0
			goto ext
		end if
		if cnt > 0 then
			arg_msg='当前导出开始时间:'+string(arg_bgdt,'yyyy-mm-dd hh:mm:ss')+'与历史导出最大时间:'+string(max_eddt,'yyyy-mm-dd hh:mm:ss') + '之间还有数据未导出,不能导出数据'
			rslt=0
			goto ext
		end if
	end if
end if
	
ext:
return rslt
end function

public function integer check_no_finish (datetime arg_ctdt, ref string arg_log, ref string arg_msg);//检查截数前未完成事项
//check_no_finish(arg_ctdt,arg_log,arg_msg)
Int rslt = 1
Int arr_cnt
Long exprot_suc = 0,export_fail = 0,lg_rowcount
String str_dwSQl,str_SyntaxFromSQL
String ls_msg
String char_enter
DataStore ds_data
ds_data = CREATE DataStore
Long arg_bgint,arg_edint

char_enter = Char(13)+Char(10) //换行符

s_yfct s_ctmx[]


f_init_cttbinfo(arg_ctdt,arg_bgint,arg_edint,s_ctmx[])
arr_cnt = UpperBound(s_ctmx)

Open(w_sys_wait_jdt) //初始化进度条

w_sys_wait_jdt.Show()
w_sys_wait_jdt.wf_accepttol(arr_cnt)

Int i

FOR i = 1 TO arr_cnt
	ds_data.Reset()
	
	if s_ctmx[i].ctbname = '' then continue
	
	w_sys_wait_jdt.st_msg.Text = "正在检查 "+s_ctmx[i].ctbname+"..." //进度信息
	
	str_dwSQl = "select * "+s_ctmx[i].fromstr + s_ctmx[i].checkstr + s_ctmx[i].linkstr
	
	ls_msg = ""
	str_SyntaxFromSQL = sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg)
	IF ls_msg > '' THEN
		arg_msg = "["+s_ctmx[i].etbname+"]"+"dw语法生成失败  ("+str_dwSQl+')'
		arg_log = arg_log+arg_msg+char_enter
		GOTO nexttable
	END IF
	
	ds_data.Create(str_SyntaxFromSQL,ls_msg)
	IF ls_msg > '' THEN
		arg_msg = "["+s_ctmx[i].etbname+"]"+"建立相关DW失败  ("+str_dwSQl+')'
		arg_log = arg_log+arg_msg+char_enter
		GOTO nexttable
	END IF
	
	ds_data.SetTransObject(sqlca)
	lg_rowcount = ds_data.Retrieve()
	arg_log = arg_log+">"+String(lg_rowcount)+" {"+s_ctmx[i].ctbname+"}"+" ["+str_dwSQl+"]"+char_enter
	
nexttable:
w_sys_wait_jdt.wf_inc(i) //进度
NEXT

Close(w_sys_wait_jdt)
RETURN rslt


end function

public 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="打开文件"+arg_objfilename+"失败"
	rslt=0
	fileclose(li_FileNum)
	goto ext
end if
ls_jh=FileWrite(li_FileNum, arg_tableinfo)
if ls_jh<=0 then
	arg_msg="写文件"+arg_objfilename+"失败"
	rslt=0
	fileclose(li_FileNum)
	goto ext
end if

fileclose(li_FileNum)
ext:
return rslt
end function

public function integer add_ct_datamx (long arg_ct_id, datetime arg_bg_dt, datetime arg_ed_dt, long arg_err_num, long arg_ct_delerrnum, ref string arg_msg, integer arg_type, transaction arg_sqlca);//新建/更新导出明细
//add_ct_datamx(arg_ct_id,arg_bg_dt,arg_ed_dt,arg_err_num,arg_ct_delerrnum,arg_msg,arg_type,arg_sqlca)
//arg_ct_id 0:新建,>0:更新
//arg_type  0:文件导入导出,1:管道导入导入

if arg_ct_id=0 then
	insert into sys_ct_datamx(ct_stdt,ct_eddt,ct_errnum,ct_delerrnum,ct_type) 
	values(:arg_bg_dt,:arg_ed_dt,:arg_err_num,0,:arg_type) using arg_sqlca;
else
	update sys_ct_datamx set ct_delerrnum=:arg_ct_delerrnum where ct_id=:arg_ct_id using arg_sqlca;
end if
if arg_sqlca.sqlcode <> 0 then
	rollback;
	arg_msg='更新截数提取日志失败'
	return 0
else
	commit;
	return 1
end if

end function

public function integer init_ct (boolean arg_ifcheck_dt, datetime arg_ctdt, ref string arg_msg, ref string arg_log, boolean arg_ifcommit);//设定截数时间
//init_ct(arg_ctdt,arg_msg)
Int rslt = 1,cnt
Long ls_ct_dateint
Long arg_sucnum
String char_enter
char_enter = Char(13)+Char(10) //换行符


IF arg_ifcheck_dt THEN
	//检查当日是否已截数
	SELECT count(*) INTO :cnt FROM sys_ct_data
		WHERE year(ct_datetime) = year(:arg_ctdt)
		AND month(ct_datetime) = month(:arg_ctdt)
		AND Day(ct_datetime) = Day(:arg_ctdt);
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '查询日期 '+String(arg_ctdt,'yyyy-mm-dd') +'是否已设定为截数日期失败'
		rslt = 0
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		arg_msg = '日期 '+String(arg_ctdt,'yyyy-mm-dd') +'已设定为截数日期,不能重复设定'
		rslt = 0
		GOTO ext
	END IF
	
	//检查截数
	cnt = 0
	SELECT count(*) INTO :cnt FROM sys_ct_data
		Where ct_datetime > :arg_ctdt;
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '查询日期 '+String(arg_ctdt,'yyyy-mm-dd') +'是否已设定为截数日期失败'
		rslt = 0
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		arg_msg = '日期 '+String(arg_ctdt,'yyyy-mm-dd') +'之后已有截数日期,不能重复设定'
		rslt = 0
		GOTO ext
	END IF
	//
	
	ls_ct_dateint = Long(String(arg_ctdt,'yyyymmdd'))
	
	INSERT INTO sys_ct_data(ct_dateint,ct_datetime,ct_num)
		Values(:ls_ct_dateint,:arg_ctdt,0);
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '新建截数时间记录失败>>'+sqlca.SQLErrText
		rslt = 0
		GOTO ext
	END IF
ELSE
	ls_ct_dateint = Long(String(arg_ctdt,'yyyymmdd'))
END IF

//更新表wagemth字段
//1.财务表[已结清]
//2.工资表[已审核]
//3.进出仓表[已结存,财务已审]
//4.不参与结存的in_out表

Int arr_cnt
Long arg_bgint,arg_edint
s_yfct s_ctmx[]
f_init_cttbinfo(arg_ctdt,arg_bgint,arg_edint,s_ctmx[])

arr_cnt = UpperBound(s_ctmx)

Open(w_sys_wait_jdt) //初始化进度条

w_sys_wait_jdt.Show()
w_sys_wait_jdt.wf_accepttol(arr_cnt)

long i

FOR i = 1 TO arr_cnt
	if s_ctmx[i].etbname = '' then continue
	w_sys_wait_jdt.st_msg.Text = s_ctmx[i].ctbname+" 正在更新标记..." //进度信息
	IF p_update_ctmint(s_ctmx[i].etbname,s_ctmx[i].ctbname,s_ctmx[i].wherestr,s_ctmx[i].fromstr,s_ctmx[i].linkstr,ls_ct_dateint,arg_msg,arg_sucnum) = 0 THEN
		rslt = 0
		GOTO ext
	END IF
	arg_log = arg_log + "> "+String(arg_sucnum)+ " ["+s_ctmx[i].ctbname+"] 截数成功"+char_enter
	
	
	w_sys_wait_jdt.wf_inc(i) //进度
NEXT

ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSEIF rslt = 1 AND arg_ifcommit THEN
	COMMIT;
END IF
Close(w_sys_wait_jdt)
RETURN rslt


end function

public function integer del_ct_data (datetime arg_edctdt, long arg_ct_id, long arg_bg_int, long arg_ed_int, integer arg_commit_step, ref string arg_msg, ref string arg_log, boolean arg_if_buildlogfile);//删除已成功截数且已成功导出的数据
//del_ct_data(arg_bg_int,arg_ed_int,arg_commit_step,arg_msg,arg_log)
Int rslt = 1
Int arr_cnt
Long suc = 0,fail = 0,lg_rowcount
String str_dwSQl,str_SyntaxFromSQL
String ls_msg
String char_enter
DataStore ds_data
ds_data = CREATE DataStore

Long max_delid,min_delid,tmp_maxid,tmp_minid
String ls_ptbname //主表名
String ls_delid //删除ID
String err_tbname,etbname
String del_comm,delstring,wherestring

char_enter = Char(13)+Char(10) //换行符

s_yfct s_ctmx[]


f_init_cttbinfo(arg_edctdt,arg_bg_int,arg_ed_int,s_ctmx[])

arr_cnt = UpperBound(s_ctmx)

Open(w_sys_wait_2jdt)

w_sys_wait_2jdt.Show()
w_sys_wait_2jdt.wf_accepttol(arr_cnt) //初始化进度条1
w_sys_wait_2jdt.wf_accepttol2(0) //初始化进度条2

Int i

FOR i = 1 TO arr_cnt
	IF s_ctmx[i].etbname <> '' THEN
		ds_data.Reset()
		ls_ptbname = ''
		
		w_sys_wait_2jdt.st_msg.Text = "正在删除 "+s_ctmx[i].ctbname+"..." //进度信息
		
		etbname = s_ctmx[i].etbname
		//1.取表名		
		ls_ptbname = s_ctmx[i].etbname
		ls_delid = s_ctmx[i].delid
		
		//2.取最大、最小删除ID
		str_dwSQl = "select max("+ls_delid+") as maxid,min("+ls_delid+") as minid from "+ls_ptbname + s_ctmx[i].delstr
		ls_msg = ""
		str_SyntaxFromSQL = sqlca.SyntaxFromSQL(str_dwSQl,"style(type=grid)",ls_msg)
		IF ls_msg > '' THEN
			arg_msg = "> 0 ["+ls_ptbname+"]dw语法生成失败"
			err_tbname = ls_ptbname
			arg_log = arg_log+arg_msg+char_enter
			GOTO nexttable
		END IF
		
		ds_data.Create(str_SyntaxFromSQL,ls_msg)
		IF ls_msg > '' THEN
			arg_msg = "> 0 ["+ls_ptbname+"]建立相关DW失败"
			err_tbname = ls_ptbname
			arg_log = arg_log+arg_msg+char_enter
			GOTO nexttable
		END IF
		
		ds_data.SetTransObject(sqlca)
		lg_rowcount = ds_data.Retrieve()
		
		max_delid = ds_data.Object.maxid[1]
		min_delid = ds_data.Object.minid[1]
		
		IF IsNull(max_delid) THEN max_delid = 0
		IF IsNull(min_delid) THEN min_delid = 0
		
		IF max_delid = 0 AND min_delid = 0 THEN
			arg_msg = "> 0 ["+etbname+"]没有可删除的数据"
			arg_log = arg_log+arg_msg+char_enter
			GOTO nexttable
		END IF
		
		//3.根据删除步长删除
		IF max_delid - min_delid < arg_commit_step THEN
			
			delstring = "delete from " + etbname  + s_ctmx[i].delstr
			wherestring = " and "+ls_delid+" >= "+String(min_delid)+" and "+ls_delid+" <= "+String(max_delid)
			del_comm = delstring + wherestring
			
			
			
			EXECUTE IMMEDIATE :del_comm ;
			IF sqlca.SQLCode <> 0 THEN
				arg_msg = "> 0 ["+etbname+"]删除数据失败 ["+del_comm+"]"
				err_tbname = ls_ptbname
				arg_log = arg_log+arg_msg+char_enter
				fail++
				GOTO nexttable
			ELSE
				suc = suc + sqlca.SQLNRows
				arg_msg = "> "+String(sqlca.SQLNRows)+" ["+etbname+"]"+"删除数据成功"+" ["+del_comm+"]"
				arg_log = arg_log+arg_msg+char_enter
			END IF
		ELSE
			tmp_maxid = min_delid + arg_commit_step
			tmp_minid = min_delid
			
			w_sys_wait_2jdt.wf_accepttol2(Ceiling((max_delid - min_delid)/arg_commit_step)) //初始化进度条2
			
			DO WHILE tmp_maxid <= max_delid
				
				delstring = "delete from " + etbname  + s_ctmx[i].delstr
				wherestring = " and "+ls_delid+" >= "+String(tmp_minid)+" and "+ls_delid+" <= "+String(tmp_maxid)
				
				
				del_comm = delstring + wherestring
				
				
				EXECUTE IMMEDIATE :del_comm ;
				IF sqlca.SQLCode <> 0 THEN
					arg_msg = "> 0 ["+etbname+"]"+"删除数据失败"+" ["+del_comm+"]"
					
					err_tbname = ls_ptbname
					
					arg_log = arg_log+arg_msg+char_enter
					fail++
					GOTO nexttable
				ELSE
					suc = suc + sqlca.SQLNRows
					arg_msg = "> "+String(sqlca.SQLNRows)+" ["+etbname+"]删除数据成功 ["+del_comm+"]"
					arg_log = arg_log+arg_msg+char_enter
				END IF
				
				COMMIT;
				w_sys_wait_2jdt.wf_inc2(Ceiling((tmp_maxid - min_delid)/arg_commit_step)) //进度1
				
				IF tmp_maxid = max_delid THEN GOTO nexttable
				
				tmp_minid = tmp_maxid
				IF tmp_maxid + arg_commit_step >= max_delid THEN
					tmp_maxid = max_delid
				ELSE
					tmp_maxid = tmp_maxid + arg_commit_step
				END IF
			LOOP
		END IF
	nexttable:
	COMMIT;
END IF
w_sys_wait_2jdt.wf_inc(i) //进度1
NEXT
arg_log = arg_log+"数据删除:成功-"+String(suc)+",失败-"+String(fail)+char_enter

//更新删除表
IF fail > 0 AND arg_ct_id > 0 THEN
	DateTime arg_bg_changetime,arg_ed_changetime
	IF add_ct_datamx(arg_ct_id,arg_bg_changetime,arg_ed_changetime,0,fail,arg_msg,0,sqlca) = 0 THEN
		arg_log = arg_log+arg_msg+char_enter
	END IF
END IF


//建立日志
String ct_path,ls_filepathname
Long li_FileNum
security_attributes ls_security_attributes
String filelog


ct_path = "ctdellog"
filelog = '删除'+String(arg_bg_int)+' - '+String(arg_ed_int)+char_enter + arg_log

li_FileNum = CreateDirectory(sys_cur_path+ct_path,ls_security_attributes)

ls_filepathname = sys_cur_path+ct_path+'\'+"LongJoe_CTD"+String(Today(),'yyyymmdd')+String(Now(),'hhmmss')+".log"

IF arg_if_buildlogfile THEN
	rslt = pf_writetifofile(filelog,ls_filepathname,arg_msg)
	IF rslt = 0 THEN
		arg_msg = "写日志文件失败>>"+arg_msg
		rslt = 1
	END IF
END IF

Close(w_sys_wait_2jdt)
RETURN rslt



end function

private function integer p_update_ctmint (string arg_etbname, string arg_ctbname, string arg_wherestr, string arg_fromstr, string arg_linkstr, long arg_value, ref string arg_msg, ref long arg_sucnum);//更新截数标记
//p_update_ctmint(arg_etbname,arg_ctbname,arg_wherestr,arg_value,arg_msg,arg_sucnum)
int rslt=1 
string update_comm

update_comm='update '+ arg_etbname + ' set ' + arg_etbname + '.ctmint = ' + string(arg_value)
update_comm=update_comm + arg_fromstr + arg_wherestr + arg_linkstr

//if arg_etbname = 'u_inware' then messagebox('',update_comm)

Execute immediate :update_comm ; 
if sqlca.sqlcode <> 0 then
	arg_msg='更新表:' + arg_ctbname + ' 截数标记失败>>'+sqlca.sqlerrtext
	rslt=0
	goto ext
end if
arg_sucnum=sqlca.sqlnrows
ext:
return rslt
end function

on uo_yfct.create
call super::create
TriggerEvent( this, "constructor" )
end on

on uo_yfct.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on