QAD 实现批量导入菜单(GUI版) -Index1-BaseCls
QAD 实现批量导入菜单(GUI版)
自己实现的简化导入的cimload功能菜单 适用于GUI的版本(EB/SE) SE/EE版可以由此改编为WEBUI版来实现
点击template按钮 输出Excel导入模板 在模板里填写好数据后 点击loaddata按钮读取Excel数据
读取的数据会显示在Detail表格里
点Execute按钮 进行执行 经过自定义逻辑检查后，会调用原始UI菜单进行cimload导入(如果有错会回滚)
并把结果输出到Excel里
从而可以大批量快速导入数据到相应菜单里
程序结构：
xxgcodeloadc(入口程序)=>xxcimgeneral.p(通用 读取配置)=>cimframe(显示 执行操作)=>cimgcode(继承cimbase 实现Icimode 核心是拼接cimload字符串)
1.base类 提供通用的方法和字段 后续model类均需继承此类
/*------------------------------------------------------------------------
File : cimbase
Purpose : A SUPER CLASS FOR GUI CIMLOAD
Syntax :
Description : Compatibility with PROGRESS OPENEDGE 10.1A and above
Author(s) : TERRENCE ZHANG
Created : Fri Oct 28 08:29:16 CST 2016
Notes :
----------------------------------------------------------------------*/
CLASS cimbase:
DEFINE PUBLIC VARIABLE global_domain AS CHARACTER NO-UNDO. /*取global_domain*/
DEFINE PUBLIC VARIABLE global_user_lang_dir AS CHARACTER NO-UNDO. /*取global_user_lang_dir*/
DEFINE PUBLIC VARIABLE global_userid AS CHARACTER NO-UNDO. /*global_userid*/
DEFINE PROTECTED VARIABLE effdate AS DATE INIT TODAY NO-UNDO. /*日期*/
DEFINE PROTECTED VARIABLE picked AS LOGICAL NO-UNDO. /*勾选*/
DEFINE PROTECTED VARIABLE SPACES AS CHARACTER NO-UNDO.
DEFINE PROTECTED VARIABLE choice AS LOGICAL NO-UNDO.
DEFINE PROTECTED VARIABLE tptbhd AS HANDLE NO-UNDO.
DEFINE PUBLIC VARIABLE checkerr AS LOGICAL NO-UNDO.
METHOD PUBLIC CHARACTER substrbyte(INPUT estr AS CHARACTER,INPUT st AS INTEGER,INPUT ln AS INTEGER):
DEFINE VARIABLE strpt AS CHARACTER NO-UNDO.
DEFINE VARIABLE ost AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE oln AS INTEGER NO-UNDO.
DEFINE VARIABLE sln AS INTEGER NO-UNDO.
IF st LE 1 THEN ASSIGN oln = ln.
ELSE ASSIGN oln = st - 1.
ASSIGN sln = oln.
ASSIGN strpt = SUBSTRING(estr,1,sln).
DO WHILE LENGTH(strpt,'RAW') > sln:
oln = oln - 1.
ASSIGN strpt = SUBSTRING(strpt,1,oln).
END.
IF st GT 1 THEN DO:
ASSIGN ost = oln + 1.
ASSIGN oln = ln.
ASSIGN strpt = SUBSTRING(estr,ost).
DO WHILE LENGTH(strpt,'RAW') > ln:
oln = oln - 1.
ASSIGN strpt = SUBSTRING(strpt,1,oln).
END.
END.
RETURN SUBSTRING(estr,ost,oln).
END METHOD.
/*METHOD 截取字符串 按字节*/
METHOD PROTECTED CHARACTER setd(INPUT echar AS CHARACTER):
IF echar = "" OR echar = ? THEN RETURN "-".
ELSE RETURN TRIM(echar).
END METHOD. /*取excel数据和格式化日期数值时用*/
METHOD PROTECTED CHARACTER setq(INPUT echar AS CHARACTER):
IF echar = "-" THEN RETURN echar.
ELSE RETURN QUOTER(TRIM(echar)).
END METHOD. /*组合STRING时用*/
METHOD PROTECTED CHARACTER setdsp(INPUT echar AS CHARACTER):
IF echar = ? THEN RETURN SPACES.
ELSE RETURN TRIM(echar).
END METHOD.
METHOD PUBLIC VOID choset(INPUT edate AS DATE,INPUT epick AS LOGICAL): /*frame 界面 改变日期和勾选项*/
ASSIGN effdate = edate picked = epick.
END METHOD.
METHOD PUBLIC DATE getdate(): /*取得生效日期*/
RETURN effdate.
END METHOD.
METHOD PUBLIC VOID emptytb():
tptbhd:DEFAULT-BUFFER-HANDLE:EMPTY-TEMP-TABLE().
END METHOD.
METHOD PUBLIC VOID putplt(): /*导出模板*/
DEFINE VARIABLE dhbf AS HANDLE NO-UNDO.
DEFINE VARIABLE t AS CLASS pexcel NO-UNDO.
DEFINE VARIABLE i AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
dhbf = tptbhd:DEFAULT-BUFFER-HANDLE.
IF VALID-HANDLE(dhbf) THEN
DO:
t = NEW pexcel().
IF t:newexcel(1) THEN
DO:
DO n = 2 TO (dhbf:NUM-FIELDS - 2):
IF dhbf:BUFFER-FIELD(n):COLUMN-LABEL EQ 'NA' THEN NEXT. /*NA的略过*/
t:writecell(1,1,i,dhbf:BUFFER-FIELD(n):COLUMN-LABEL).
i = i + 1.
END.
t:setsheetname(1,tptbhd:NAME).
t:showexcel().
END.
DELETE OBJECT t.
t = ?.
END.
END METHOD.
METHOD PUBLIC VOID showlog():
DEFINE VARIABLE p AS pexcel NO-UNDO.
DEFINE VARIABLE i AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE j AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE dhbf AS HANDLE NO-UNDO.
dhbf = tptbhd:DEFAULT-BUFFER-HANDLE.
DEFINE VARIABLE q AS HANDLE NO-UNDO.
DEFINE VARIABLE querystr AS CHARACTER NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
IF VALID-HANDLE(dhbf) THEN
DO:
p = NEW pexcel().
IF p:newexcel(1) THEN
DO:
j = 1.
DO n = 2 TO (dhbf:NUM-FIELDS - 2):
IF dhbf:BUFFER-FIELD(n):COLUMN-LABEL EQ 'NA' THEN NEXT. /*NA的略过*/
p:writecell(1,i,j,dhbf:BUFFER-FIELD(n):COLUMN-LABEL).
j = j + 1.
END.
p:writecell(1,i,j,"Success").
p:writecell(1,i,j + 1,"Message").
i = i + 1.
querystr = "FOR EACH " + dhbf:TABLE.
CREATE QUERY q.
q:SET-BUFFERS(dhbf).
q:QUERY-PREPARE(querystr).
q:QUERY-OPEN.
REPEAT:
q:GET-NEXT().
IF q:QUERY-OFF-END THEN LEAVE.
j = 1.
DO n = 2 TO (dhbf:NUM-FIELDS - 2): /*n = 1 是GUID 不用显示的*/
IF dhbf:BUFFER-FIELD(n):COLUMN-LABEL EQ 'NA' THEN NEXT. /*NA的略过*/
p:writecell(1,i,j,setdsp(STRING(dhbf:BUFFER-FIELD(n):BUFFER-VALUE))).
j = j + 1.
END.
IF dhbf:BUFFER-FIELD("tpisok"):BUFFER-VALUE NE ? THEN
p:writecell(1,i,j,STRING(dhbf:BUFFER-FIELD("tpisok"):BUFFER-VALUE)).
IF dhbf:BUFFER-FIELD("tpisok"):BUFFER-VALUE EQ NO AND dhbf:BUFFER-FIELD("tpmsg"):BUFFER-VALUE NE SPACES THEN
p:writecell(1,i,j + 1,setdsp(STRING(dhbf:BUFFER-FIELD("tpmsg"):BUFFER-VALUE))).
i = i + 1.
END.
p:setsheetname(1,"Results").
p:autofit(1).
p:showexcel().
END.
DELETE OBJECT p.
p = ?.
dhbf:EMPTY-TEMP-TABLE().
END.
END METHOD.
METHOD PUBLIC VOID getdata(INPUT filepath AS CHARACTER,INPUT tbhd AS HANDLE): /*tbhd是显示BROWSE用途*/
DEFINE VARIABLE g AS pexcel NO-UNDO.
DEFINE VARIABLE i AS INTEGER INITIAL 2 NO-UNDO.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
DEFINE VARIABLE dhbf AS HANDLE NO-UNDO.
DEFINE VARIABLE hverr AS LOGICAL NO-UNDO.
IF effdate = ? THEN effdate = TODAY.
IF SEARCH(filepath) EQ ? THEN RETURN.
dhbf = tptbhd:DEFAULT-BUFFER-HANDLE.
IF VALID-HANDLE(dhbf) THEN
DO:
g = NEW pexcel().
IF g:startexcel(filepath,1) THEN
DO:
REPEAT WHILE g:charcell(1,i,1) NE SPACES: /*取数据*/
dhbf:BUFFER-CREATE().
dhbf:BUFFER-FIELD("tpguid"):BUFFER-VALUE = GUID.
j = 1.
DO n = 2 TO (dhbf:NUM-FIELDS - 2): /*除定义里的第一个GUID字段和最后的ISOK MSG字段外*/
IF dhbf:BUFFER-FIELD(n):COLUMN-LABEL EQ 'NA' THEN NEXT. /*NA的略过*/
dhbf:BUFFER-FIELD(n):BUFFER-VALUE = g:charcell(1,i,j) NO-ERROR. /*因为模板里的不必要字段也一起省略了 用变量j增长*/
IF ERROR-STATUS:ERROR THEN DO:
hverr = YES.
LEAVE.
END.
j = j + 1.
END.
i = i + 1.
END.
IF hverr THEN DO:
MESSAGE "An error occurred while reading the data,please check your data format in the template" VIEW-AS ALERT-BOX ERROR.
dhbf:EMPTY-TEMP-TABLE().
END.
g:rlsexcel().
END.
DELETE OBJECT g.
g = ?.
IF tptbhd:HAS-RECORDS THEN
vnewtemppv(INPUT tbhd). /*更新BROWER临时表*/
END.
END METHOD.
METHOD PROTECTED VOID vnewtemppv(INPUT tbhd AS HANDLE): /*简化用 默认按顺序显示*/
/*子类可重写 browse显示用*/
/*
tbhd的原始定义(cimframe.cls里):
DEFINE PROTECTED TEMP-TABLE temppv NO-UNDO
FIELD pvfld AS CHARACTER FORMAT "x(36)" EXTENT 7. /*显示的最大宽度*/
*/
DEFINE VARIABLE q AS HANDLE NO-UNDO.
DEFINE VARIABLE dhbf AS HANDLE NO-UNDO.
DEFINE VARIABLE thbf AS HANDLE NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
dhbf = tptbhd:DEFAULT-BUFFER-HANDLE.
thbf = tbhd:DEFAULT-BUFFER-HANDLE.
IF VALID-HANDLE(dhbf) THEN
DO:
CREATE QUERY q.
q:SET-BUFFERS(dhbf).
q:QUERY-PREPARE("FOR EACH " + dhbf:TABLE).
q:QUERY-OPEN.
REPEAT:
q:GET-NEXT().
IF q:QUERY-OFF-END THEN LEAVE.
thbf:BUFFER-CREATE().
i = 1.
DO n = 2 TO (dhbf:NUM-FIELDS - 2):
IF dhbf:BUFFER-FIELD(n):COLUMN-LABEL EQ 'NA' THEN NEXT. /*NA的略过*/
IF i > 7 THEN LEAVE. /*字段最多7*/
thbf:BUFFER-FIELD(1):BUFFER-VALUE[(i)] = STRING(dhbf:BUFFER-FIELD(n):BUFFER-VALUE).
i = i + 1.
END.
END.
q:QUERY-CLOSE().
DELETE OBJECT q.
q = ?.
END.
END METHOD.
METHOD PROTECTED VOID gentemppv(INPUT tbbf AS HANDLE,
INPUT field1 AS CHARACTER,INPUT field2 AS CHARACTER,INPUT field3 AS CHARACTER,
INPUT field4 AS CHARACTER,INPUT field5 AS CHARACTER,INPUT field6 AS CHARACTER,INPUT field7 AS CHARACTER): /*自定义排显示列 和 顺序*/
tbbf:BUFFER-CREATE().
ASSIGN
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(1)] = field1
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(2)] = field2
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(3)] = field3
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(4)] = field4
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(5)] = field5
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(6)] = field6
tbbf:BUFFER-FIELD(1):BUFFER-VALUE[(7)] = field7.
END METHOD.
METHOD PROTECTED VOID vcheckdata(): /*简化用*/
/*空方法 子类可重写 options*/
END METHOD.
METHOD PROTECTED VOID vbfload():
/*空方法 子类可重写 导入前执行*/
END METHOD.
METHOD PROTECTED VOID vafload():
/*空方法 子类可重写 导入后执行 此时临时表记录还存在*/
END METHOD.
METHOD PUBLIC VOID vexecload(INPUT cimexec AS CHARACTER,INPUT testmode AS LOGICAL):
/*空方法 子类重写 执行导入*/
END METHOD.
METHOD PUBLIC VOID vmainblock(INPUT cimexec AS CHARACTER,INPUT testmode AS LOGICAL):
DEFINE VARIABLE dhbf AS HANDLE NO-UNDO.
dhbf = tptbhd:DEFAULT-BUFFER-HANDLE.
IF tptbhd:HAS-RECORDS THEN /*临时表有数据*/
DO:
vcheckdata().
dhbf:FIND-FIRST("WHERE tpmsg NE ''") NO-ERROR.
IF dhbf:AVAILABLE THEN
DO:
checkerr = YES.
END.
ELSE
DO:
MESSAGE "Confirm to import?" VIEW-AS ALERT-BOX QUESTION BUTTONS YES-NO UPDATE choice.
IF NOT choice THEN RETURN.
ELSE
DO:
vbfload().
vexecload(cimexec,testmode).
vafload().
END.
END.
showlog().
END.
ELSE MESSAGE "NO DATA!" VIEW-AS ALERT-BOX WARNING.
dhbf:EMPTY-TEMP-TABLE().
/*清空临时表*/
END METHOD.
END CLASS.
2.接口 后续model须实现的接口 xxcimgeneral.p / cimframe类里 定义model的类型
/*------------------------------------------------------------------------
File : Icimode
Purpose :
Syntax :
Description :
Author(s) : TERRENCE ZHANG
Created : Thu Oct 27 16:09:10 CST 2016
Notes :
----------------------------------------------------------------------*/
INTERFACE Icimode:
METHOD PUBLIC VOID mainblock(). /*主方法 给RUN使用*/
METHOD PUBLIC VOID putplt(). /*数据模板*/
METHOD PUBLIC VOID getdata(INPUT filepath AS CHARACTER,INPUT tbhd AS HANDLE). /*取数据*/
METHOD PUBLIC VOID emptytb(). /*清空临时表*/
METHOD PUBLIC VOID choset(INPUT edate AS DATE,INPUT epick AS LOGICAL). /*传递日期等*/
METHOD PUBLIC DATE getdate(). /*取得默认日期*/
END INTERFACE.
/*实现此接口的类为GUI CIMLOAD通用菜单使用*/
浙公网安备 33010602011771号