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

global type uo_xls_backup from nonvisualobject
end type
global uo_xls_backup uo_xls_backup

type variables
transaction xls_trans

private transaction acc_trans

uo_win32api uo_api

uo_xls_constant uo_const
end variables

forward prototypes
public function integer createaccessdb (string arg_filedir, string arg_filename, boolean arg_ifkeepconnect, ref string arg_msg)
private function string create_export_string (string arg_filepath, string arg_uid, string arg_pwd, string arg_tablename, string arg_accesstablename)
public function transaction create_access_transaction (boolean arg_autocommit, string arg_uid, string arg_pwd, string arg_defaultdir, string arg_filename, boolean arg_autoconnect, ref string arg_msg)
public function integer exporttoaccess (string arg_filedir, string arg_filename, boolean arg_ifcommit, ref string arg_msg)
private function string create_import_string (string arg_filepath, string arg_uid, string arg_pwd, string arg_tablename, string arg_accesstablename)
public function integer importfromacess (string arg_filedir, string arg_filename, boolean arg_ifcommit, ref string arg_msg)
public function integer createaccesstable (boolean arg_ifcommit, boolean arg_ifkeepconnect, ref string arg_msg)
public function integer createtable (boolean arg_ifcommit, ref string arg_msg)
end prototypes

public function integer createaccessdb (string arg_filedir, string arg_filename, boolean arg_ifkeepconnect, ref string arg_msg);String filepath = ''
//long rslt = 1
filepath = arg_filedir+'\'+arg_filename
IF FileExists(filepath) THEN
	IF MessageBox('系统提示' , '数据库‘' + filepath + ' ’已经存在，是否覆盖？' , question! , yesno! , 2) = 1 THEN
		IF NOT FileDelete(filepath) THEN
			arg_msg = '替换文件'+arg_filename+'失败，'+arg_filename+'可能正在被使用'
			RETURN 0
		END IF
	ELSE
		RETURN 2
	END IF
END IF

OLEObject appAccess
appAccess = CREATE OLEObject
IF appAccess.ConnectToNewObject("Access.Application") <> 0 THEN
	arg_msg = 'OLE无法连接!'
	RETURN 0
END IF
appAccess.Visible = FALSE
appAccess.NewCurrentDatabase(filepath)
appAccess.quit()
appAccess.DisconnectObject()
DESTROY appAccess

acc_trans = create_access_transaction(FALSE,'admin','',arg_filedir,arg_filename,TRUE,arg_msg)
IF  IsNull(acc_trans) THEN
	IF NOT FileDelete(filepath) THEN
		arg_msg += '~n删除临时文件'+arg_filename+'失败，'+arg_filename+'可能正在被使用，请手动删除'
		RETURN 0
	END IF
	
	RETURN 0
END IF

IF createaccesstable(TRUE , arg_ifkeepconnect,arg_msg) <> 1 THEN
	DISCONNECT USING acc_trans ;
	DESTROY acc_trans
	SetNull(acc_trans)
	IF NOT FileDelete(filepath) THEN
		arg_msg += '~n删除临时文件'+arg_filename+'失败，'+arg_filename+'可能正在被使用，请手动删除'
		RETURN 0
	END IF
	RETURN 0
END IF


//ext:
IF NOT arg_ifkeepconnect THEN
	DISCONNECT USING acc_trans ;
	DESTROY acc_trans
END IF
SetNull(acc_trans)
RETURN 1



end function

private function string create_export_string (string arg_filepath, string arg_uid, string arg_pwd, string arg_tablename, string arg_accesstablename);string str

str = "insert into openrowset('Microsoft.Jet.OLEDB.4.0', '"+arg_filepath+"';'"+arg_uid+"';'"+arg_pwd+"','SELECT * FROM "+arg_accesstablename+"') SELECT * FROM "+arg_tablename
return str
end function

public function transaction create_access_transaction (boolean arg_autocommit, string arg_uid, string arg_pwd, string arg_defaultdir, string arg_filename, boolean arg_autoconnect, ref string arg_msg);transaction acc

IF NOT FileExists(arg_DefaultDir + "\" + arg_filename) THEN
	arg_msg = '目标数据库文件：'+arg_DefaultDir + "\" + arg_filename +'不存在'
	SetNull(acc)
	GOTO ext
END IF

acc = CREATE Transaction
acc.DBMS = "ODBC"
acc.AutoCommit = arg_AutoCommit
acc.DBParm = "ConnectString='DRIVER=Driver do Microsoft Access (*.mdb);" + &
	"UID="+arg_uid+";" + &
	"PWD="+arg_pwd+";" + &
	"UserCommitSync=Yes;"+ &
	"Threads=3;"+ &
	"SafeTransactions=0;"+ &
	"PageTimeout=5;"+ &
	"MaxScanRows=8;"+ &
	"MaxBufferSize=2048;"+ &
	"FIL=MS Access;"+ &
	"DriverId=25;"+ &
	"DefaultDir= " + arg_DefaultDir + ";"+ &
	"DBQ=" + arg_DefaultDir + "\" + arg_filename



CONNECT USING acc ;
IF acc.SQLCode <> 0 THEN
	arg_msg = acc.SQLErrText
	DESTROY acc
	SetNull(acc)
ELSE
	IF NOT arg_autoconnect THEN
		DISCONNECT USING acc;
	END IF
END IF
ext:
RETURN acc



end function

public function integer exporttoaccess (string arg_filedir, string arg_filename, boolean arg_ifcommit, ref string arg_msg);long rslt
rslt = createaccessdb(arg_filedir,arg_filename,false,arg_msg)
if  rslt  <> 1 then
	return rslt
end if

string exporttr[]
long i
xls_trans.autocommit = true
EXECUTE IMMEDIATE 'SET ANSI_NULLS ON' using xls_trans;
EXECUTE IMMEDIATE 'SET ANSI_WARNINGS ON' using xls_trans;

for i = 1 to upperbound(uo_const.xls_tablelist )
	exporttr[i] = create_export_string(arg_filedir+'\'+arg_filename,'admin','',uo_const.xls_tablelist[i],uo_const.xls_tablelist[i])
	EXECUTE IMMEDIATE :exporttr[i] USING xls_trans;
	IF xls_trans.SQLCode <> 0 THEN
		arg_msg = "导出表"+uo_const.xls_tablelist[i]+"时错误:"+xls_trans.SQLErrText+'~n' + exporttr[i]
		rollback using xls_trans;
		xls_trans.autocommit = false
		RETURN 0
	END IF
next
xls_trans.autocommit = false


if arg_ifcommit then
	commit using xls_trans;
end if

return 1
end function

private function string create_import_string (string arg_filepath, string arg_uid, string arg_pwd, string arg_tablename, string arg_accesstablename);string str
str = "insert into "+arg_tablename+" SELECT * from openrowset('Microsoft.Jet.OLEDB.4.0', '"+arg_filepath+"';'"+arg_uid+"';'"+arg_pwd+"','SELECT * FROM "+arg_accesstablename+"') "
return str
end function

public function integer importfromacess (string arg_filedir, string arg_filename, boolean arg_ifcommit, ref string arg_msg);string exporttr[]
long i
i = createtable(false , arg_msg)
IF  i <> 1 THEN
	rollback using xls_trans;
	RETURN i
END IF


EXECUTE IMMEDIATE 'SET ANSI_NULLS ON' using xls_trans;
EXECUTE IMMEDIATE 'SET ANSI_WARNINGS ON' using xls_trans;

for i = 1 to upperbound(uo_const.xls_tablelist )
	exporttr[i] = create_import_string(arg_filedir+'\'+arg_filename,'admin','',uo_const.xls_tablelist[i],uo_const.xls_tablelist[i])
	EXECUTE IMMEDIATE :exporttr[i] USING xls_trans;
	IF xls_trans.SQLCode <> 0 THEN
		arg_msg = "导入表"+uo_const.xls_tablelist[i]+"时错误:"+xls_trans.SQLErrText+'~n' + exporttr[i]
		rollback using xls_trans;
		RETURN 0
	END IF
next



if arg_ifcommit then
	commit using xls_trans;
end if

return 1
end function

public function integer createaccesstable (boolean arg_ifcommit, boolean arg_ifkeepconnect, ref string arg_msg);
SetPointer(hourglass!)
Integer i , i_row

//执行语句
i_row = UpperBound(uo_const.acc_create_str )
FOR i = 1 TO i_row
	EXECUTE IMMEDIATE :uo_const.acc_create_str[i] USING acc_trans;
	IF acc_trans.SQLCode <> 0 THEN
		arg_msg = "创建表时错误:"+acc_trans.SQLErrText+'~n' + uo_const.acc_create_str[i]
		rollback using acc_trans;	
		RETURN 0
	END IF
NEXT
if arg_ifcommit then 
	COMMIT USING acc_trans ;
end if
if not arg_ifkeepconnect then 
	DISCONNECT USING acc_trans ;
end if
//CONNECT USING acc_trans;
Yield()

//创建成功
//DISCONNECT USING acc_trans ;
//DESTROY acc_trans
RETURN 1



end function

public function integer createtable (boolean arg_ifcommit, ref string arg_msg);
SetPointer(hourglass!)
Integer i , i_row
//执行语句
i_row = UpperBound(uo_const.xls_drop_str )
FOR i = 1 TO i_row
	EXECUTE IMMEDIATE :uo_const.xls_drop_str[i] USING xls_trans;	
NEXT 
i_row = UpperBound(uo_const.xls_create_str )
FOR i = 1 TO i_row
	EXECUTE IMMEDIATE :uo_const.xls_create_str[i] USING xls_trans;
	IF xls_trans.SQLCode <> 0 THEN
		arg_msg = "创建表时错误:"+xls_trans.SQLErrText+'~n' + uo_const.xls_create_str[i]
		rollback using xls_trans;	
		RETURN 0
	END IF
NEXT 
if arg_ifcommit then 
	COMMIT USING xls_trans ;
end if
Yield()
RETURN 1



end function

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

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

event constructor;uo_api = create uo_win32api

 uo_const = create uo_xls_constant
end event