本页目录:
- 1、开窗查询程序
- 2、配置
- 3、测试
开窗查询程序
画面档
- q_apa10.4fd
<?xml version="1.0" encoding="UTF-8" ?>
<ManagedForm databaseName="ds" gstVersion="22800" name="Root" uid="{d773f83d-0000-4000-9784-d57674278765}">
<AGSettings/>
<Record additionalTables="" joinLeft="" joinOperator="" joinRight="" name="s_apy" order="" uid="{e5fa620e-0000-4000-893a-f6ac57956817}" where="">
<RecordField colName="" fieldIdRef="1" name="check" sqlTabName="" table_alias_name="" uid="{f8218c5c-0000-4000-a478-dfc71f3e3377}"/>
<RecordField colName="apyslip" fieldIdRef="2" fieldType="NON_DATABASE" name="one" sqlTabName="apy_file" sqlType="VARCHAR" table_alias_name="" uid="{561af4fa-0000-4000-afa5-b84165a61ea8}"/>
</Record>
<Form gridHeight="11" gridWidth="22" name="q_apy" spacing="normal" text="Qry Document Type">
<Grid gridHeight="11" gridWidth="22" lstrcomment="false" name="gr3730" posX="0" posY="0">
<Table gridHeight="11" gridWidth="20" lstrcomment="false" name="s_apy" posX="1" posY="0" totalRows="10">
<CheckBox aggregateColName="" aggregateName="" aggregateTableAliasName="" aggregateTableName="" colName="" fieldId="1" gridHeight="1" gridWidth="6" lstrcomment="false" lstrtext="false" lstrtitle="false" name="check" notNull="true" posX="0" posY="0" required="true" sqlTabName="" sqlType="CHAR" tabIndex="1" table_alias_name="" title="Sel" valueChecked="Y" valueUnchecked="N" widget="CheckBox"/>
<Edit aggregateColName="" aggregateName="" aggregateTableAliasName="" aggregateTableName="" colName="apyslip" fieldId="2" fieldType="NON_DATABASE" gridHeight="1" gridWidth="11" length="1" lstrcomment="false" lstrtitle="false" name="one" noEntry="true" posX="6" posY="0" sqlTabName="apy_file" sqlType="VARCHAR" tabIndex="2" table_alias_name="" title="Document" widget="Edit"/>
</Table>
</Grid>
</Form>
<DiagramLayout>
<![CDATA[AAAAAgAAAEwAewA1ADYAMQBhAGYANABmAGEALQAwADAAMAAwAC0ANAAwADAAMAAtAGEAZgBhADUALQBiADgANAAxADYANQBhADYAMQBlAGEAOAB9QCQAAAAAAABATYAAAAAAAAAAAAAAAAAAAQAAAEwAewBlADUAZgBhADYAMgAwAGUALQAwADAAMAAwAC0ANAAwADAAMAAtADgAOQAzAGEALQBmADYAYQBjADUANwA5ADUANgA4ADEANwB9AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAEwAewBmADgAMgAxADgAYwA1AGMALQAwADAAMAAwAC0ANAAwADAAMAAtAGEANAA3ADgALQBkAGYAYwA3ADEAZgAzAGUAMwAzADcANwB9QCQAAAAAAABAQoAAAAAAAAAAAAAAAAAAAQ==]]>
</DiagramLayout>
</ManagedForm>
# 注意:输入框带搜索按钮主要参数是:
- widget:buttonedit
- image type:select file
- image:zoom
- action:CONTROLP
- 放到:/u1/topprod/tiptop/qry/4fd
# 使用sftp
cd /u1/topprod/tiptop/qry/4fd
put q_apa10.4fd
# 使用ssh
cd /u1/topprod/tiptop/qry/4fd
r.f2 q_apa10 2 c
后台程序
- q_apa10.4gl
# Program name : q_apa10.4gl
# Description : 單據彈頭第一個字母查詢
# Date & Author : 2023/05/24 DKLi 2665252078@qq.com
# 調用:q_apa10(TRUE, FALSE) 第一個參數是true是復選,是false是單選
DATABASE ds
GLOBALS "../../config/top.global"
DEFINE ma_qry DYNAMIC ARRAY OF RECORD
check LIKE type_file.chr1,
apyslip LIKE apy_file.apyslip
END RECORD
DEFINE ma_qry_tmp DYNAMIC ARRAY OF RECORD
check LIKE type_file.chr1,
apyslip LIKE apy_file.apyslip
END RECORD
DEFINE mi_multi_sel LIKE type_file.num5 #是否需要複選資料(TRUE/FALSE).
DEFINE mi_need_cons LIKE type_file.num5 #是否需要CONSTRUCT(TRUE/FALSE).
DEFINE ms_cons_where STRING #暫存CONSTRUCT區塊的WHERE條件.
DEFINE mi_page_count LIKE type_file.num10 #每頁顯現資料筆數.
DEFINE ms_ret1 STRING
FUNCTION q_apa10(pi_multi_sel,pi_need_cons)
DEFINE pi_multi_sel LIKE type_file.num5,
pi_need_cons LIKE type_file.num5
WHENEVER ERROR CALL cl_err_msg_log
OPEN WINDOW w_qry WITH FORM "qry/42f/q_apa10" ATTRIBUTE(STYLE="create_qry")
CALL cl_ui_locale("q_apa10")
LET mi_multi_sel = pi_multi_sel
LET mi_need_cons = pi_need_cons
IF NOT (mi_multi_sel) THEN
CALL cl_set_comp_visible("check",FALSE)
END IF
IF (mi_multi_sel) THEN
CALL cl_set_comp_font_color("apyslip", "red")
END IF
CALL apa10_qry_sel()
CLOSE WINDOW w_qry
IF (mi_multi_sel) THEN
RETURN ms_ret1
ELSE
RETURN ms_ret1
END IF
END FUNCTION
##################################################
# Description : 畫面顯現與資料的選擇.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : none
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_sel()
DEFINE ls_hide_act STRING
DEFINE li_hide_page LIKE type_file.num5, #是否隱藏'上下頁'的按鈕.
li_reconstruct LIKE type_file.num5, #是否重新CONSTRUCT.預設為TRUE.
li_continue LIKE type_file.num5 #是否繼續.
DEFINE li_start_index LIKE type_file.num10,
li_end_index LIKE type_file.num10
DEFINE li_curr_page LIKE type_file.num5
DEFINE li_count LIKE ze_file.ze03,
li_page LIKE ze_file.ze03
LET mi_page_count = 100
LET li_reconstruct = TRUE
WHILE TRUE
CLEAR FORM
LET INT_FLAG = FALSE
LET ls_hide_act = ""
LET ms_cons_where = " 1=1"
IF (li_reconstruct) THEN
MESSAGE ""
IF (mi_need_cons) THEN
CONSTRUCT BY NAME ms_cons_where ON apyslip
ON ACTION controlg
CALL cl_cmdask()
ON IDLE g_idle_seconds
CALL cl_on_idle()
CONTINUE CONSTRUCT
ON ACTION about
CALL cl_about()
ON ACTION help
CALL cl_show_help()
END CONSTRUCT
IF (INT_FLAG) THEN
LET INT_FLAG = FALSE
EXIT WHILE
END IF
END IF
CALL apa10_qry_prep_result_set()
# 2003/07/14 by Hiko : 如果沒有設定'每頁顯現資料筆數',則預設為所有資料一起顯現.
IF (mi_page_count = 0) THEN
LET mi_page_count = ma_qry.getLength()
END IF
# 2003/07/14 by Hiko : 如果所設定的'每頁顯現資料筆數'超過/等於所有資料,則要隱藏'上下頁'的按鈕.
IF (mi_page_count >= ma_qry.getLength()) THEN
LET ls_hide_act = "prevpage,nextpage"
END IF
LET li_start_index = 1
LET li_reconstruct = FALSE
END IF
LET li_end_index = li_start_index + mi_page_count - 1
IF (li_end_index > ma_qry.getLength()) THEN
LET li_end_index = ma_qry.getLength()
END IF
CALL apa10_qry_set_display_data(li_start_index, li_end_index)
LET li_curr_page = li_end_index / mi_page_count
IF (li_end_index MOD mi_page_count) > 0 THEN
LET li_curr_page = li_curr_page + 1
END IF
SELECT ze03 INTO li_count FROM ze_file WHERE ze01 = 'qry-001' AND ze02 = g_lang
SELECT ze03 INTO li_page FROM ze_file WHERE ze01 = 'qry-002' AND ze02 = g_lang
MESSAGE li_count CLIPPED || " : " || ma_qry.getLength() || " " || li_page CLIPPED || " : " || li_curr_page
IF (mi_multi_sel) THEN
CALL apa10_qry_input_array(ls_hide_act, li_start_index, li_end_index) RETURNING li_continue,li_reconstruct,li_start_index
ELSE
CALL apa10_qry_display_array(ls_hide_act, li_start_index, li_end_index) RETURNING li_continue,li_reconstruct,li_start_index
END IF
IF (NOT li_continue) THEN
EXIT WHILE
END IF
END WHILE
END FUNCTION
##################################################
# Description : 準備查詢畫面的資料集.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : none
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_prep_result_set()
DEFINE l_filter_cond STRING
DEFINE ls_sql STRING,
ls_where STRING
DEFINE li_i LIKE type_file.num10
DEFINE lr_qry RECORD
check LIKE type_file.chr1,
apyslip LIKE apy_file.apyslip
END RECORD
LET l_filter_cond = cl_get_extra_cond_for_qry('q_apa10', 'apy_file')
IF NOT cl_null(l_filter_cond) THEN
LET ms_cons_where = ms_cons_where,l_filter_cond
END IF
LET ls_sql = "SELECT 'N',substr(apa01,1,1) apyslip",
" FROM apa_file ",
" WHERE apaacti = 'Y' ",
" AND apa42='N' ",
" GROUP BY substr(apa01,1,1) ORDER BY substr(apa01,1,1)"
DECLARE lcurs_qry CURSOR FROM ls_sql
CALL ma_qry.clear()
LET li_i = 1
FOREACH lcurs_qry INTO lr_qry.*
IF (SQLCA.SQLCODE) THEN
CALL cl_err(ls_sql, SQLCA.SQLCODE, 1)
EXIT FOREACH
END IF
LET ma_qry[li_i].* = lr_qry.*
LET li_i = li_i + 1
END FOREACH
END FUNCTION
##################################################
# Description : 設定查詢畫面的顯現資料.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : pi_start_index LIKE type_file.num10 所要顯現的查詢資料起始位置
# : pi_end_index LIKE type_file.num10 所要顯現的查詢資料結束位置
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_set_display_data(pi_start_index, pi_end_index)
DEFINE pi_start_index LIKE type_file.num10,
pi_end_index LIKE type_file.num10
DEFINE li_i LIKE type_file.num10,
li_j LIKE type_file.num10
CALL ma_qry_tmp.clear()
FOR li_i = pi_start_index TO pi_end_index
LET ma_qry_tmp[li_j+1].* = ma_qry[li_i].*
LET li_j = li_j + 1
END FOR
CALL SET_COUNT(ma_qry_tmp.getLength())
END FUNCTION
##################################################
# Description : 採用INPUT ARRAY的方式來顯現查詢過後的資料.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : ps_hide_act STRING 所要隱藏的Action Button
# : pi_start_index LIKE type_file.num10 所要顯現的查詢資料起始位置
# : pi_end_index LIKE type_file.num10 所要顯現的查詢資料結束位置
# Return : SMALLINT 是否繼續
# : SMALLINT 是否重新查詢
# : INTEGER 改變後的起始位置
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_input_array(ps_hide_act, pi_start_index, pi_end_index)
DEFINE ps_hide_act STRING,
pi_start_index LIKE type_file.num10,
pi_end_index LIKE type_file.num10
DEFINE li_continue LIKE type_file.num5,
li_reconstruct LIKE type_file.num5
DEFINE li_i LIKE type_file.num5
INPUT ARRAY ma_qry_tmp WITHOUT DEFAULTS FROM s_apy.* ATTRIBUTE(INSERT ROW=FALSE, DELETE ROW=FALSE,APPEND ROW=FALSE, UNBUFFERED)
BEFORE INPUT
CALL cl_set_act_visible("prevpage,nextpage,reconstruct",TRUE)
IF (ps_hide_act IS NOT NULL) THEN
CALL cl_set_act_visible(ps_hide_act, FALSE)
END IF
ON ACTION prevpage
CALL GET_FLDBUF(s_apy.check) RETURNING ma_qry_tmp[ARR_CURR()].check
CALL apa10_qry_reset_multi_sel(pi_start_index, pi_end_index)
IF ((pi_start_index - mi_page_count) >= 1) THEN
LET pi_start_index = pi_start_index - mi_page_count
END IF
LET li_continue = TRUE
EXIT INPUT
ON ACTION nextpage
CALL GET_FLDBUF(s_apy.check) RETURNING ma_qry_tmp[ARR_CURR()].check
CALL apa10_qry_reset_multi_sel(pi_start_index, pi_end_index)
IF ((pi_start_index + mi_page_count) <= ma_qry.getLength()) THEN
LET pi_start_index = pi_start_index + mi_page_count
END IF
LET li_continue = TRUE
EXIT INPUT
ON ACTION refresh
CALL apa10_qry_refresh()
LET pi_start_index = 1
LET li_continue = TRUE
EXIT INPUT
ON ACTION reconstruct
LET li_reconstruct = TRUE
LET li_continue = TRUE
EXIT INPUT
ON ACTION accept
IF ARR_CURR()>0 THEN
CALL GET_FLDBUF(s_apy.check) RETURNING ma_qry_tmp[ARR_CURR()].check
CALL apa10_qry_reset_multi_sel(pi_start_index, pi_end_index)
CALL apa10_qry_accept(pi_start_index+ARR_CURR()-1)
ELSE
LET ms_ret1 = NULL
END IF
LET li_continue = FALSE
EXIT INPUT
ON ACTION cancel
LET INT_FLAG = 0
IF (NOT mi_multi_sel) THEN
LET ms_ret1 = NULL
END IF
LET li_continue = FALSE
EXIT INPUT
ON ACTION exporttoexcel
CALL cl_export_to_excel(ui.Interface.getRootNode(),base.TypeInfo.create(ma_qry),'','')
ON IDLE g_idle_seconds
CALL cl_on_idle()
CONTINUE INPUT
ON ACTION qry_string
CALL cl_qry_string("detail")
ON ACTION selectall
FOR li_i = 1 TO ma_qry_tmp.getLength()
LET ma_qry_tmp[li_i].check = "Y"
END FOR
ON ACTION select_none
FOR li_i = 1 TO ma_qry_tmp.getLength()
LET ma_qry_tmp[li_i].check = "N"
END FOR
END INPUT
RETURN li_continue,li_reconstruct,pi_start_index
END FUNCTION
##################################################
# Description : 重設查詢資料關於'check'欄位的值.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : pi_start_index LIKE type_file.num10 所要顯現的查詢資料起始位置 #No.FUN-680131 INTEGER
# : pi_end_index LIKE type_file.num10 所要顯現的查詢資料結束位置 #No.FUN-680131 INTEGER
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_reset_multi_sel(pi_start_index, pi_end_index)
DEFINE pi_start_index LIKE type_file.num10,
pi_end_index LIKE type_file.num10
DEFINE li_i LIKE type_file.num10,
li_j LIKE type_file.num10
FOR li_i = pi_start_index TO pi_end_index
LET ma_qry[li_i].check = ma_qry_tmp[li_j+1].check
LET li_j = li_j + 1
END FOR
END FUNCTION
##################################################
# Description : 採用DISPLAY ARRAY的方式來顯現查詢過後的資料.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : ps_hide_act STRING 所要隱藏的Action Button
# : pi_start_index LIKE type_file.num10 所要顯現的查詢資料起始位置
# : pi_end_index LIKE type_file.num10 所要顯現的查詢資料結束位置
# Return : SMALLINT 是否繼續
# : SMALLINT 是否重新查詢
# : INTEGER 改變後的起始位置
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_display_array(ps_hide_act, pi_start_index, pi_end_index)
DEFINE ps_hide_act STRING,
pi_start_index LIKE type_file.num10,
pi_end_index LIKE type_file.num10
DEFINE li_continue LIKE type_file.num5,
li_reconstruct LIKE type_file.num5
DISPLAY ARRAY ma_qry_tmp TO s_apy.*
BEFORE DISPLAY
CALL cl_set_act_visible("prevpage,nextpage,reconstruct",TRUE)
IF (ps_hide_act IS NOT NULL) THEN
CALL cl_set_act_visible(ps_hide_act, FALSE)
END IF
ON ACTION prevpage
IF ((pi_start_index - mi_page_count) >= 1) THEN
LET pi_start_index = pi_start_index - mi_page_count
END IF
LET li_continue = TRUE
EXIT DISPLAY
ON ACTION nextpage
IF ((pi_start_index + mi_page_count) <= ma_qry.getLength()) THEN
LET pi_start_index = pi_start_index + mi_page_count
END IF
LET li_continue = TRUE
EXIT DISPLAY
ON ACTION refresh
LET pi_start_index = 1
LET li_continue = TRUE
EXIT DISPLAY
ON ACTION reconstruct
LET li_reconstruct = TRUE
LET li_continue = TRUE
LET mi_need_cons = TRUE
EXIT DISPLAY
ON ACTION accept
CALL apa10_qry_accept(pi_start_index+ARR_CURR()-1)
LET li_continue = FALSE
EXIT DISPLAY
ON ACTION cancel
LET INT_FLAG = 0
IF (NOT mi_multi_sel) THEN
LET ms_ret1 = NULL
END IF
LET li_continue = FALSE
EXIT DISPLAY
ON ACTION exporttoexcel
CALL cl_export_to_excel(ui.Interface.getRootNode(),base.TypeInfo.create(ma_qry),'','')
ON IDLE g_idle_seconds
CALL cl_on_idle()
CONTINUE DISPLAY
ON ACTION qry_string
CALL cl_qry_string("detail")
END DISPLAY
CALL cl_set_act_visible("accept,cancel", TRUE)
RETURN li_continue,li_reconstruct,pi_start_index
END FUNCTION
##################################################
# Description : 重設查詢資料.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : none
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_refresh()
DEFINE li_i LIKE type_file.num10
FOR li_i = 1 TO ma_qry.getLength()
LET ma_qry[li_i].check = 'N'
END FOR
END FUNCTION
##################################################
# Description : 選擇並確認資料.
# Date & Author : {%格式為:xxxx/xx/xx by xxx}
# Parameter : pi_sel_index LIKE type_file.num10 所選擇的資料索引 #No.FUN-680131 INTEGER
# Return : void
# Memo :
# Modify :
##################################################
FUNCTION apa10_qry_accept(pi_sel_index)
DEFINE pi_sel_index LIKE type_file.num10
DEFINE lsb_multi_sel base.StringBuffer,
li_i LIKE type_file.num10
IF pi_sel_index = 0 THEN
RETURN
END IF
IF (mi_multi_sel) THEN
LET lsb_multi_sel = base.StringBuffer.create()
FOR li_i = 1 TO ma_qry.getLength()
IF (ma_qry[li_i].check = 'Y') THEN
IF (lsb_multi_sel.getLength() = 0) THEN
CALL lsb_multi_sel.append(ma_qry[li_i].apyslip CLIPPED)
ELSE
CALL lsb_multi_sel.append("|" || ma_qry[li_i].apyslip CLIPPED)
END IF
END IF
END FOR
LET ms_ret1 = lsb_multi_sel.toString()
ELSE
LET ms_ret1 = ma_qry[pi_sel_index].apyslip CLIPPED
END IF
END FUNCTION
- 放到:/u1/topprod/tiptop/qry/4gl
# 使用sftp
cd /u1/topprod/tiptop/qry/4gl
put q_apa10.4gl
# 使用ssh
cd /u1/topprod/tiptop/qry/4gl
r.c2 q_apa10s
配置
p_qry 新增开窗查询
p_link 新增连接
# 使用ssh
cd /u1/topprod/tiptop/qry/4gl
r.l2 qry
测试
4gl引用
# 調用:q_apa10(TRUE, FALSE) 第一個參數是true是復選,是false是單選
CONSTRUCT BY NAME tm.wc ON apyslip
ON ACTION locale
CALL cl_show_fld_cont()
LET g_action_choice = "locale"
EXIT CONSTRUCT
ON ACTION CONTROLP
CASE
WHEN INFIELD(apyslip)
CALL q_apa10(TRUE, FALSE) RETURNING g_qryparam.multiret
DISPLAY g_qryparam.multiret TO apyslip
NEXT FIELD apyslip
END CASE
ON IDLE g_idle_seconds
CALL cl_on_idle()
CONTINUE CONSTRUCT
ON ACTION help
CALL cl_show_help()
ON ACTION controlg
CALL cl_cmdask()
ON ACTION about
CALL cl_about()
ON ACTION exit
LET INT_FLAG = 1
EXIT CONSTRUCT
END CONSTRUCT
IF g_action_choice = "locale" THEN
LET g_action_choice = ""
CALL cl_dynamic_locale()
CONTINUE WHILE
END IF