一个 PB 中动态根据数据库字段产生TreeView视图

 

一个 PB 中动态根据数据库字段产生TreeView视图

 

global type gf_treeview_lv3_sql from function_object

end type

forward prototypes

global subroutine gf_treeview_lv3_sql (treeview tv_list, string table, string col1, string col2, string col3, string ssql)

end prototypes

global subroutine gf_treeview_lv3_sql (treeview tv_list, string table, string col1, string col2, string col3, string ssql);//提供两级

DynamicStagingArea SQLSSA

SQLSSA = CREATE DynamicStagingArea

DynamicStagingArea SQLSSSA

SQLSSSA = CREATE DynamicStagingArea

DECLARE cur_lv1 DYNAMIC CURSOR FOR SQLSA;

DECLARE cur_lv2 DYNAMIC CURSOR FOR SQLSSA;

DECLARE cur_lv3 DYNAMIC CURSOR FOR SQLSSSA;

//得到最大行数

long ll_lev0, ll_lev1, ll_lev2, ll_lev3

string scol_lv1, scol_lv2, scol_lv3

string SQL_lv1, SQL_lv2, SQL_lv3

string SQL

long lloop, lloopin, lloopout

long lcount, lcountin, lountout

SQL = "SELECT COUNT( DISTINCT " + col1 + ") FROM "+table + " WHERE " + sSQL

PREPARE SQLSSSA FROM :SQL ;

OPEN DYNAMIC cur_lv3 ;

FETCH cur_lv3 INTO :lountout;

CLOSE cur_lv3;

SQL_lv1 = "SELECT DISTINCT "+col1+" FROM "+table+ " WHERE "+ sSQL +" ORDER BY " + col1

PREPARE SQLSA FROM :SQL_lv1 ;

OPEN DYNAMIC cur_lv1 ;

lloopout = 0

//插入root节点

ll_lev0 = tv_list.InsertItemFirst(0,"所有",1)

      

DO WHILE lloopout < lountout

       lloopout ++

      

       FETCH cur_lv1 INTO :scol_lv1 ;

       //插入第2个节点

       ll_lev1 = tv_list.InsertItemLast(ll_lev0, scol_lv1, 2)

      

       SQL = "SELECT COUNT(DISTINCT "+col2+") FROM "+table+ " WHERE " + col1 + " = '" + scol_lv1 + "' AND "+ sSQL +" ORDER BY " + col2

       PREPARE SQLSSSA FROM :SQL ;

       OPEN DYNAMIC cur_lv3 ;

       FETCH cur_lv3 INTO :lcount;

       CLOSE cur_lv3;

       SQL_lv2 = "SELECT DISTINCT "+col2+" FROM "+table+ " WHERE " + col1 + " = '" + scol_lv1 + "' AND "+ sSQL +" ORDER BY " + col2

       PREPARE SQLSSA FROM :SQL_lv2 ;

       OPEN DYNAMIC cur_lv2;

              lloop = 0

              DO WHILE lloop < lcount    

                     lloop ++               

                     FETCH cur_lv2 INTO :scol_lv2 ;

                     //做增加操作

                     //插入第2个节点

                     ll_lev2 = tv_list.InsertItemLast(ll_lev1, scol_lv2, 2)

                    

                            SQL = "SELECT COUNT(DISTINCT "+col3+") FROM "+table+ " WHERE " + col1 + " = '" + scol_lv1 + "' AND " + col2 + " = '" + scol_lv2 + "' AND "+ sSQL +" ORDER BY " + col3

                            PREPARE SQLSSSA FROM :SQL ;

                            OPEN DYNAMIC cur_lv3 ;

                            FETCH cur_lv3 INTO :lcountin;

                            CLOSE cur_lv3;

                    

                            SQL_lv3 = "SELECT DISTINCT "+col3+" FROM "+table+ " WHERE " + col1 + " = '" + scol_lv1 + "' AND " + col2 + " = '" + scol_lv2 + "' AND "+ sSQL +" ORDER BY " + col3

                            PREPARE SQLSSSA FROM :SQL_lv3 ;

                            OPEN DYNAMIC cur_lv3;

                           

                            lloopin = 0

                            DO WHILE lloopin < lcountin

                                   lloopin ++

                                   //插入第三级节点

                                   FETCH cur_lv3 INTO :scol_lv3 ;

                                  

                                   ll_lev3 = tv_list.InsertItemLast(ll_lev2, scol_lv3, 2)

                            LOOP

                            CLOSE cur_lv3;

              LOOP

       CLOSE cur_lv2;

LOOP

CLOSE cur_lv1 ;

DESTROY SQLSSA;

DESTROY SQLSSSA;

end subroutine

调用方法:

拧下红(14150285) 14:11:48

gf_treeview_lv3_SQL( tab_resource.tabpage_employee.tv_1_mir, "pv_employee_detail", "dept", "team", "empe||'#'||epid", &

       "epid not in (SELECT SOID FROM pt_taskresource WHERE PSID = '"+ sPsid_retrieve +"') ")

       gf_treeview_lv2_SQL( tab_resource.tabpage_employee.tv_2_mir, "pt_taskresource", "sorc||'#'||soid", "remk", &

       "PSID = " + sPsid_retrieve + " AND CLSS = 'P'")