Search

Code Filter User Object for Powerbuilder Apps

Posted on Friday, August 21st, 2009 at 12:06 pm in

When working with the end users who would be using the Purchasing System I was tasked with developing, a requirement came out very early in the process which might otherwise have caused concern; they wanted to sift their data based on any number of various codes assigned to it.  Initially there were six different code types they wanted to use, in time this increased to ten.  I wanted to implement as generic a solution as possible since there were four windows (initially) this type of behavior was going to apply to.

The various code values themselves were pretty typical, (part class, material type, product group, etc.) but their numbers varied greatly from six to over twenty thousand.  I wanted to offer the users the ability to enter both individual code values and code ranges at the same time to maximize flexibility.  Due to the nature of the data involved, I used these objects to insert data into a series of tables which were subsequently referenced by a stored procedure which selected the desired records.  However, the process can easily be used to manipulate a specific datawindow sql statement or to apply a filter to the results within a datawindow.

The code is PFC based and makes use of the error service.

Datawindow Object d_code_filter (external data source)
table(column=(type=char(50) updatewhereclause=no name=codeline dbname="codeline" )
column=(type=char(30) updatewhereclause=no name=tablename dbname="tablename" )
column=(type=char(1) updatewhereclause=no name=singledelim dbname="singledelim" initial=";" )
column=(type=char(1) updatewhereclause=no name=rangedelim dbname="rangedelim" initial="-" )
)

Only the ‘codeline’ column is visible on the datawindow object.
 n_cst_filterstring User Object (PFC Based),

global type n_cst_filterstring from n_srv
//Instance variables
n_cst_string    inv_string // string checking service

forward prototypes
public function long of_parsefilter (string as_filterstring, string as_delimiter, ref string as_filter[])
public function string of_buildfilter (string as_params, string as_delim, string as_replacestring)
public function integer of_checkrange (string as_source, string as_rangedelim, string as_singledelim)
end prototypes

public function long of_parsefilter (string as_filterstring, string as_delimiter, ref string as_filter[]);
long    ll_return
// parse the filter string into an array and return the array size
long      ll_arraylength
string    ls_filterarray[]
long      ll_return = 1

// turn as_filtersting into an array
ll_arraylength = inv_string.of_parsetoarray(as_filterstring,as_delimiter,ls_filterarray)
IF IsNull(ll_arraylength) THEN
   ll_return = 0
ELSE
   as_filter = ls_filterarray
   ll_return = ll_arraylength
END IF
RETURN ll_return
end function
public function string of_buildfilter (string as_params, string as_delim, string as_replacestring);
string    ls_return
ls_return = ''
ls_return = inv_string.of_globalreplace(as_params,as_delim,as_replacestring)RETURN ls_return
end function
public function integer of_checkrange (string as_source, string as_rangedelim, string as_singledelim);
// checks string 'as_source', which should be a
// range of codes, to make sure that it is a
// valid range designation and the first code
// is of lesser or equal value to the second code.
//
// example code range: 0100-0200
//         '0100' and '0200' are the codes
//         '-' is the range delimiter
//
// returns -1 for error condition
//                1 for success
//
integer     li_return
long        ll_singleoc
string    ls_range[]
string    ls_error[]
li_return = 1

//how many 'elements' are in the string
ll_singleoc = inv_string.of_countoccurrences(as_source,as_singledelim)
IF (ll_singleoc = 0) THEN
   inv_string.of_parsetoarray(as_source,as_rangedelim,ls_range)
   IF (Upperbound(ls_range) <> 2) then
      // ERROR CONDITION
     // should have exactly two elements in a range
      ls_error[1] = ''
      gnv_app.inv_error.of_Message("1101", ls_error)
      li_return = -1
   ELSE
      IF (Trim(Upper(ls_range[1])) > Trim(Upper(ls_range[2]))) THEN
        // ERROR CONDITION
        // element 1 cannot be greater than element 2
        ls_error[1] = Trim(Upper(ls_range[1]))
        ls_error[2] = Trim(Upper(ls_range[2]))
        gnv_app.inv_error.of_Message("1101", ls_error)
        li_return = -1
      END IF
   END IF
END IF
RETURN li_return
end function
 uo_code_filter object - User object

//This control is placed on the form.
global type uo_code_filter from u_dw
integer width = 859
integer height = 104
string dataobject = "d_code_filter"
boolean vscrollbar = false
boolean ib_isupdateable = false
string is_updatesallowed = ""
event ue_mousemove pbm_mousemove
end type
global uo_code_filter uo_code_filter
type variables
Protected:
string    is_filter[]
end variables

forward prototypes
public function integer of_settablename (string as_tablename)
public function integer of_setdefault (string as_default)
public function integer of_setdelim (string as_delimtype, string as_delim)
public function string of_gettablename ()
public function integer of_getfilter (ref string as_filter[])
public function string of_buildfilter (string as_columnname, string as_columntype)
public function string of_buildfilter (string as_columnname, string as_columntype, string as_prefixchar, long an_padamount)
end prototypes
public function integer of_settablename (string as_tablename);
// set the database table name the codes reside in
int li_return
li_return = 0
IF IsNull(as_tablename) OR as_tablename = '' THEN
 li_return = -1
ELSE
 this.setitem(1,'tablename',as_tablename)
END IF
RETURN li_return
end function
public function integer of_setdefault (string as_default);
// sets the 'default' value of the code which is
// displayed when the window is opened or set to the defaults
end function

public function integer of_setdelim (string as_delimtype, string as_delim);
// external datawindow has default values of semi-colon(;) as single value delimiter
// and dash(-) as the range delimiter.  Use this method if different delimiters
// are desired.
//
// sets the delimiters for the data entry
// return -1 - bad delim type
// return -2 - bad delim
// return -3 - unknown delim type
// return 0 - a okay
integer    li_return
li_return = 0
IF (IsNull(as_delimtype)) OR (as_delimtype = '') THEN
   li_return = -1
END IF
IF (IsNull(as_delim)) OR (as_delim = '') THEN
   li_return = -2
END IF
IF (li_return = 0) THEN
   CHOOSE CASE upper(as_delimtype)
      CASE 'SINGLE'
         this.setitem(1,'singledelim',as_delim)
      CASE 'RANGE'
         this.setitem(1,'rangedelim',as_delim)
      CASE ELSE
         li_return = -3
   END CHOOSE
END IF
RETURN li_return
end function
public function string of_gettablename ();
// gets the database table name the codes reside in
RETURN getitemstring(1,'tablename')
end function
public function integer of_getfilter (ref string as_filter[]);
// sets the filter array
integer li_return
li_return = 1
as_filter = is_filter
RETURN li_return
end function

public function string of_buildfilter (string as_columnname, string as_columntype);
// build the filter string (SQL syntax)
long        ll_i, ll_ac
string    ls_rangedelim
string    ls_range
string     ls_filterstring = ''
string    ls_array[]
string    ls_quote
string    ls_replace
n_cst_filterstring    lnv_filterstring
lnv_filterstring = CREATE n_cst_filterstring
CHOOSE CASE as_columntype
   CASE 'S' //strings
      ls_quote = "'"
   CASE ELSE // non strings
      ls_quote = ''
END CHOOSE
this.event trigger pfc_accepttext(TRUE)
// sets the is_filter array on the uo
IF (this.event trigger pfc_validation() > 0) THEN
   this.of_getfilter(ls_array)
   ls_rangedelim = this.getitemstring(1,'rangedelim')
  ll_ac = Upperbound(ls_array)
  FOR ll_i = 1 to ll_ac
      IF (Upper(ls_array[ll_i]) = 'ALL') THEN
         ls_range = ''
         ls_filterstring = 'ALL'
         EXIT
      END IF
      IF (as_columnname = '') THEN
         ls_range = ''
         ls_filterstring = ls_array[ll_i]
         EXIT
      END IF
      IF (lnv_filterstring.inv_string.of_countoccurrences(ls_array[ll_i],ls_rangedelim) > 0) THEN
         // found a range
         IF (Len(ls_range) > 1) THEN // second range found
            IF (Len(ls_filterstring) > 1) THEN
              ls_filterstring = ls_filterstring + " OR (" + ls_range + ")"
            ELSE
              ls_filterstring =  ls_range
            END IF
              ls_range = ''
         END IF
            ls_replace = ls_quote +" and " + as_columnname + " <= "+ ls_quote
            ls_range = lnv_filterstring.of_buildfilter(ls_array[ll_i],ls_rangedelim,ls_replace)
            ls_range =  " " + as_columnname + " >= " + ls_quote + ls_range + ls_quote
      ELSE
         IF (Len(ls_filterstring) > 1) THEN
            ls_filterstring = ls_filterstring + " OR " +as_columnname+" = "+ ls_quote + ls_array[ll_i] +ls_quote
         ELSE
            ls_filterstring = " "+as_columnname+" = " + ls_quote + ls_array[ll_i] + ls_quote
         END IF
      END IF
   NEXT
   IF (Len(ls_range) > 1) THEN
      IF (Len(ls_filterstring) > 1) THEN
         ls_filterstring = ls_filterstring + " OR  (" + ls_range + ")"
      ELSE
         ls_filterstring = ls_range
         ls_range = ''
      END IF
   END IF
END IF
DESTROY lnv_filterstring
RETURN ls_filterstring
end function
public function string of_buildfilter (string as_columnname, string as_columntype, string as_prefixchar, long an_padamount);
// Polymorph to allow for prefix padding of values
long        ll_i, ll_ac
long        ll_p
string    ls_rangedelim
string    ls_range
string     ls_filterstring = ''
string    ls_array[]
string    ls_quote
string    ls_replace
n_cst_filterstring    lnv_filterstring
lnv_filterstring = CREATE n_cst_filterstring
CHOOSE CASE as_columntype
   CASE 'S' //strings
      ls_quote = '"'
   CASE ELSE // non strings
      ls_quote = ''
END CHOOSE
this.event trigger pfc_accepttext(TRUE)
// sets the is_filter array on the uo
IF (this.event trigger pfc_validation() > 0) THEN
   this.of_getfilter(ls_array)
   ls_rangedelim = this.getitemstring(1,'rangedelim')
   ll_ac = Upperbound(ls_array)
   FOR ll_i = 1 to ll_ac
      IF (Upper(ls_array[ll_i]) = 'ALL') THEN
         ls_range = ''
         ls_filterstring = 'ALL'
         EXIT
      ELSE // pad entry with prefix
         FOR ll_p = 1 to an_padamount
            ls_array[ll_i] = as_prefixchar + ls_array[ll_i]
         NEXT
      END IF
      IF (as_columnname = '') THEN
         ls_range = ''
         ls_filterstring = ls_array[ll_i]
         EXIT
      END IF
      IF (lnv_filterstring.inv_string.of_countoccurrences(ls_array[ll_i],ls_rangedelim) > 0) THEN
      // found a range
         IF (Len(ls_range) > 1) THEN // second range found
            IF (Len(ls_filterstring) > 1) THEN
               ls_filterstring = ls_filterstring + ' OR (' + ls_range + ')'
            ELSE
               ls_filterstring =  ls_range
            END IF
            ls_range = ''
         END IF
         ls_replace = ls_quote +' and ' + as_columnname + ' <= '+ ls_quote
         FOR ll_p = 1 to an_padamount // pad end range item
            ls_replace = ls_replace + as_prefixchar
         NEXT
         ls_range = lnv_filterstring.of_buildfilter(ls_array[ll_i],ls_rangedelim,ls_replace)
         ls_range =  ' ' + as_columnname + ' >= ' + ls_quote + ls_range + ls_quote
      ELSE
         IF (Len(ls_filterstring) > 1) THEN
            ls_filterstring = ls_filterstring + ' OR ' +as_columnname+' = '+ ls_quote + ls_array[ll_i] +ls_quote
         ELSE
            ls_filterstring = ' '+as_columnname+' = ' + ls_quote + ls_array[ll_i] + ls_quote
         END IF
      END IF
   NEXT
   IF (Len(ls_range) > 1) THEN
      IF (Len(ls_filterstring) > 1) THEN
         ls_filterstring = ls_filterstring + ' OR  (' + ls_range + ')'
      ELSE
         ls_filterstring = ls_range
         ls_range = ''
      END IF
   END IF
END IF
DESTROY lnv_filterstring
RETURN ls_filterstring
end function

event constructor;call super::constructor;
// basic stuff
this.of_setbase(TRUE)
this.insertrow(0) // needs a row
this.setrowfocusindicator(Off!)
end event

event type integer pfc_validation();call super::pfc_validation;
// validate the filter and parse it into an array
// if a code range is involved it will be verified
//
// example:  filterstring is '0100-0200;0300;0400'
//                    '-' is the range delimiter
//                    ';' is the single delimiter
//                resulting array is:
//                element[1] = '0100-0200' < a valid range
//                element[2] = '0300'
//                element[3] = '0400'
integer    li_return
integer    li_elements
integer    li_i
string    ls_singledelim
string    ls_rangedelim
string    ls_filterstring
string    ls_range[]
string    ls_filter[]
n_cst_filterstring    lnv_filterstring
lnv_filterstring = CREATE n_cst_filterstring
li_return = 1
ls_filterstring = this.getitemstring(1,'codeline')
ls_singledelim = this.getitemstring(1,'singledelim')
ls_rangedelim = this.getitemstring(1,'rangedelim')
li_elements = lnv_filterstring.of_parsefilter(ls_filterstring,ls_singledelim,ls_filter)
IF (li_elements = 0) THEN
   li_return = -1
   //invalid or no entry
   //ERROR CONDITION
ELSE
   FOR li_i = 1 to li_elements
   // is this element a range?
      IF (lnv_filterstring.inv_string.of_countoccurrences(ls_filter[li_i],ls_rangedelim) > 0) THEN
         // make sure range is in order
         IF (lnv_filterstring.of_checkrange(ls_filter[li_i],ls_rangedelim,ls_singledelim) < 0) THEN
           //ERROR CONDITION
           li_return = -1
           EXIT
        END IF
      END IF
   NEXT
END IF
IF (li_return > 0) THEN
   is_filter = ls_filter
END IF
DESTROY lnv_filterstring
RETURN li_return
end event

Code from windows with the object

User object placed on window is called ‘dw_category’

pfc_postopen event
dw_category.of_setdefault('ALL')

Sample code called from function ‘wf_getfilter’ which returns ls_filterstring.

string    ls_filterstring
string    ls_categories
string    ls_replace
string    ls_array[]
string    ls_sql
integer    li_rc
n_cst_filterstring lnv_filterstring
lnv_filterstring = CREATE n_cst_filterstring
ls_filterstring = ''
ls_categories = dw_category.of_buildfilter('categoryCode','S')
CHOOSE CASE ls_categories
   CASE 'ALL'
      // no real filter on the column
      ls_categories = ''
      ls_sql = 'insert into wrkInvActionListCategory values ( @@SPID, -1 )'
      EXECUTE IMMEDIATE :ls_sql;
   CASE ''
      //ERROR
   CASE ELSE
      // got a filter so you can do something with it.
      li_rc = wf_setwrktable('cprCategoryMaster','categoryKey', ' WHERE '+ls_categories)
      IF (li_rc < 0) THEN
         //ERROR CONDITION
         Messagebox(this.title,'Error setting Category filter.')
      END IF
   END CHOOSE
// call the same methods on any other filter datawindows, capturing the filter string
// into separate variables then combine them (or whatever)// category
RETURN ls_categories

Samples
Data entered in field is: 0100-0120;0300;0500
Filter string returned is:
categoryCode = ‘0300’ OR categoryCode = ‘0500’ OR  ( categoryCode >= ‘0100’ and categoryCode <= ‘0120’)

Tags:

Top