The problem with Pagelet Wizard is it is designed to give you back the .MAXROWS, so rows 1 through the MAXROWS and it does not take querystring parameters that would be passed by the Related Content Framework. To handle this I decided to create my own DataSource by extending the PTPPB_PAGELET:DataSource:DataSource by copying the delivered QueryDataSource and adding a "START" parameter. Using this Parameter I was able to derive a stop row number and modify the XML generation to use a start row number and end row number. I also limited the number of rows returned by the query to 25000, since it is stated that query's run to rowset of 50000 or more could cause an memory issue with your system. The reason for the Start Row and End row is simple, I wanted to load the first 100 rows for the query and if the user scrolls the table I wanted to load the next 100 rows. This allows the service to be responsive and only load data if it is needed.
Below is my DataSource:
import PTPPB_PAGELET:UTILITY:*;
import PTPPB_PAGELET:DataSource:*;
import PTPPB_PAGELET:EXCEPTION:*;
/**
* The QueryDataSource retrieves data from a PSQuery and returns it in XML format.
*/
class RLC_QueryDataSource extends PTPPB_PAGELET:DataSource:DataSource
property string SQL get;
property PTPPB_PAGELET:UTILITY:Setting QueryName readonly;
method RLC_QueryDataSource(&id_param As string);
method execute() Returns string;
method Clone() Returns object;
method initializeSettings(&NewSettings As PTPPB_PAGELET:UTILITY:Collection);
method processSettingsChange();
method getSourceDataLinkHTML() Returns string;
method evaluateOutputFormat() Returns string;
private
instance string &mObjSource;
instance string &mData;
instance string &queryLinkURL_inst;
instance array of boolean &arRequiredPrompts;
method checkQueryForRequiredPrompts(&QuerySelect As ApiObject, &MainQueryPromptColl As ApiObject);
method checkCriteriaForRequiredPrompts(&Criteria As ApiObject, &MainQueryPromptColl As ApiObject);
Constant &NoTableEdit = 0;
Constant &TranslateTable = 512;
Constant &YesNo = 8192;
Constant &PromptTable = 16384;
end-class;
/**
* Constructor
*/
method RLC_QueryDataSource
/+ &id_param as String +/
%Super = create PTPPB_PAGELET:DataSource:DataSource(&id_param);
%This.setObjectSubType("QUERY");
%This.setCanHaveParameters( True);
%This.initializeSettings(%This.Settings);
%This.hasSourceDataLinkHTML = True;
end-method;
/**
* Executes the query and returns the results in XML format.
*
* @return String XML represenation of the query results.
*/
method execute
/+ Returns String +/
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.execute +/
Local ApiObject &MyQuery, &ThisMetaData, &MetaDataColl, &qryPromptColl, &qryPrompt;
Local number &returnStatus, &i, &j, &ColCount, &RowCount, &StopRow;
Local string &s_QueryName, &LinkParamList, &s_ParamValue, &s_MaxRows, &TempStr, &dataStr, &s_StartRow;
Local XmlDoc &xmlDoc;
Local XmlNode &xmlDocNode, &baseNode, &thisNode, &rowNode;
Local Field &ThisField, &Prompt;
Local Record &qryRecord, &promptRecord;
Local PTPPB_PAGELET:UTILITY:Collection &FieldsColl;
Local PTPPB_PAGELET:DataSource:DataSourceParameter &thisDSParameter;
Local PTPPB_PAGELET:UTILITY:Parameter &thisOutputParam;
Local Rowset &rsResult;
Local Row &QryRow;
Local array of string &arQueryFields, &arQueryFldHeadings;
&s_QueryName = %This.ParameterCollection.getItemByID("QueryName").evaluatedValue();
&MyQuery = %Session.GetQuery();
If &MyQuery = Null Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);
End-If;
&returnStatus = &MyQuery.Open(&s_QueryName, True, False);
If &returnStatus <> 0 Then
/* Error opening query */
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);
End-If;
&qryPromptColl = &MyQuery.runtimeprompts;
&promptRecord = &MyQuery.promptrecord;
If &promptRecord <> Null Then
If %This.ParameterCollection.Count > 0 Then
/* bind the prompt record with the query */
For &i = 1 To &promptRecord.FieldCount
&Prompt = &promptRecord.GetField(&i);
&qryPrompt = &qryPromptColl.item(&i);
/* Retrieve the datasource parameter for this prompt. First, try using the explicit format... */
/* Note: the explicit format will only be used when a field is used more than once in */
/* the prompts */
&thisDSParameter = %This.ParameterCollection.getItemByID(&qryPrompt.Name | "." | &i);
If &thisDSParameter = Null Then
/* Did not find in explicit format ... try just the fieldname */
/* Note: this is the most common case */
&thisDSParameter = %This.ParameterCollection.getItemByID(&qryPrompt.Name);
End-If;
Evaluate &Prompt.Type
When "DATETIME"
&thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_DATETIME;
&Prompt.Value = DateTimeValue(&thisDSParameter.evaluatedValue());
Break;
When "DATE"
&thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_DATE;
&Prompt.Value = DateValue(&thisDSParameter.evaluatedValue());
Break;
When "TIME"
&thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_TIME;
&Prompt.Value = TimeValue(&thisDSParameter.evaluatedValue());
Break;
When-Other
&Prompt.Value = &thisDSParameter.evaluatedValue();
End-Evaluate;
&LinkParamList = &LinkParamList | "&" | EncodeURLForQueryString(&qryPrompt.uniquepromptname) | "=" | EncodeURLForQueryString(&Prompt.Value);
End-For;
End-If;
End-If;
&s_MaxRows = %This.ParameterCollection.getItemByID(".MAXROWS").Value;
try
&s_StartRow = %This.ParameterCollection.getItemByID("START").Value;
catch Exception &EXCEPTIION
&s_StartRow = "1";
end-try;
If &s_MaxRows = "" Then
&s_MaxRows = %This.ParameterCollection.getItemByID(".MAXROWS").DefaultValue;
End-If;
rem &rsResult = &MyQuery.RunTorowset(&promptRecord, Value(&s_MaxRows));
REM SET TO A MAX OF 25000;
&rsResult = &MyQuery.RunTorowset(&promptRecord, 25000);
If &rsResult = Null Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);
End-If;
/* Create the xml output from the query ... */
&xmlDoc = CreateXmlDoc("<?xml version='1.0' encoding='UTF-8' standalone='yes'?><queryresult/>");
&xmlDocNode = &xmlDoc.DocumentElement;
&baseNode = &xmlDocNode.AddElement("queryproperties");
&thisNode = &baseNode.AddElement("querylink");
&thisNode = &thisNode.AddElement("URL");
&queryLinkURL_inst = GenerateQueryPortalURL(%Portal, %Node, &s_QueryName, True) | &LinkParamList;
&thisNode.NodeValue = &queryLinkURL_inst;
&thisNode = &baseNode.AddElement("queryname");
&thisNode.NodeValue = &s_QueryName;
&thisNode = &baseNode.AddElement("querydescription");
&thisNode.NodeValue = EscapeHTML(&MyQuery.Description);
&thisNode = &baseNode.AddElement("pagelet_width");
If %Request.GetParameter("PORTALPARAM_COMPWIDTH") = "Wide" Then
&thisNode.NodeValue = "Wide";
Else
&thisNode.NodeValue = "Narrow";
End-If;
&QryRow = &rsResult.GetRow(1);
&qryRecord = &QryRow.GetRecord(1);
&ColCount = &qryRecord.FieldCount;
&baseNode = &xmlDocNode.AddElement("queryfields");
&baseNode.AddAttribute("numfields", String(&ColCount));
&FieldsColl = %This.OutputFields;
If &FieldsColl.Count = 0 Then
/* OutputFields not set, get the fields collection manually */
&FieldsColl = create PTPPB_PAGELET:UTILITY:Collection("FieldsCollection");
&FieldsColl.setDuplicatesAllowed();
&MetaDataColl = &MyQuery.metadata;
For &i = 1 To &MetaDataColl.count
&ThisMetaData = &MetaDataColl.item(&i);
Evaluate &ThisMetaData.name
When "Field"
&TempStr = &ThisMetaData.value;
If &arQueryFields = Null Then
&arQueryFields = CreateArray(&TempStr);
Else
&arQueryFields.Push(&TempStr);
End-If;
Break;
When "Heading"
&TempStr = &ThisMetaData.value;
If &arQueryFldHeadings = Null Then
&arQueryFldHeadings = CreateArray(&TempStr);
Else
&arQueryFldHeadings.Push(&TempStr);
End-If;
<**>
Break;
End-Evaluate;
End-For;
For &j = 1 To &arQueryFields.Len
&thisOutputParam = create PTPPB_PAGELET:UTILITY:Parameter(&arQueryFields [&j]);
&thisOutputParam.Value = &arQueryFldHeadings [&j];
&FieldsColl.Insert(&thisOutputParam);
End-For;
&FieldsColl.setImmutable();
End-If;
For &j = 1 To &qryRecord.FieldCount
&thisNode = &baseNode.AddElement("field");
&thisNode.AddAttribute("fieldname", &FieldsColl.getItem(&j).Name);
&thisNode = &thisNode.AddElement("headingtext");
&thisNode.NodeValue = &FieldsColl.getItem(&j).Value;
End-For;
&RowCount = &rsResult.RowCount - 1;
rem - one extra row gets appended at the end of the rowset ;
If &RowCount > 0 Then
&baseNode = &xmlDocNode.AddElement("queryrows");
&baseNode.AddAttribute("numrows", String(&RowCount));
If Value(&s_StartRow) = 1 Then
&StopRow = Value(&s_MaxRows);
Else
&StopRow = Value(&s_MaxRows) + Value(&s_StartRow) - 1;
End-If;
rem this is a partial return of data stop at the rowcount;
If &StopRow > &RowCount Then
&StopRow = &RowCount
End-If;
For &i = Value(&s_StartRow) To &StopRow
&QryRow = &rsResult(&i);
&rowNode = &baseNode.AddElement("row");
&rowNode.AddAttribute("rownumber", String(&i));
For &j = 1 To &qryRecord.FieldCount
&thisNode = &rowNode.AddElement("querydata");
&thisNode.AddAttribute("fieldname", &FieldsColl.getItem(&j).Name);
&dataStr = &QryRow.GetRecord(1).GetField(&j).Value;
&thisNode = &thisNode.AddElement("text");
&thisNode.NodeValue = &dataStr;
End-For;
End-For;
End-If;
Return &xmlDoc.GenXmlString();
end-method;
/**
* Initializes the QueryName setting.
*
* @param NewSettings Collection of settings to use with this pagelet. A new collection is created if null.
*/
method initializeSettings
/+ &NewSettings as PTPPB_PAGELET:UTILITY:Collection +/
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.initializeSettings +/
Local PTPPB_PAGELET:UTILITY:Setting &thisSetting;
If &NewSettings = Null Then
&NewSettings = create PTPPB_PAGELET:UTILITY:Collection("QueryDataSourceSettings");
End-If;
%This.setSettings(&NewSettings);
&thisSetting = %This.Settings.getItemByID("QueryName");
If &thisSetting = Null Then
&QueryName = %This.createSettingProperty("QueryName", "");
Else
&QueryName = &thisSetting;
%This.setSettingsComplete( True);
End-If;
&QueryName.PromptTable = Record.PTPPB_QUERY_VW;
&QueryName.EditType = &QueryName.EDITTYPE_PROMPTTABLE;
&QueryName.FieldType = &QueryName.FIELDTYPE_CHARACTER;
&QueryName.Enabled = True;
&QueryName.Visible = True;
&QueryName.RefreshOnChange = True;
&QueryName.Required = True;
&QueryName.LongName = MsgGetText(219, 401, "(Message Not Found)Query Name");
&QueryName.setObjectToRefreshOnValueChange(%This);
end-method;
/**
* Handle the changing of the QueryName Setting value.
*
* <P>NOTE: CHANGING THE VALUE OF MANY SETTINGS WILL CAUSE THE PARAMETERCOLLECTION
* TO BE REBUILT. IT IS VERY VERY IMPORTANT THAT THE PARAMETERCOLLECTION
* BE SET (OR RETRIEVED) AFTER ALL SETTINGS VALUES HAVE BEEN SET!!!</P>
*/
method processSettingsChange
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.processSettingsChange +/
Local string &s_QueryName;
Local PTPPB_PAGELET:UTILITY:Setting &querySetting;
Local PTPPB_PAGELET:UTILITY:Collection &Params, &Outputs;
Local PTPPB_PAGELET:DataSource:DataSourceParameter &thisParam;
Local PTPPB_PAGELET:UTILITY:OutputField &thisOutputParam;
Local number &i, &j;
Local number &returnStatus;
Local string &DataSourceName, &TempStr;
Local ApiObject &MyQuery, &qryPrompt, &ThisMetaData, &MetaDataColl;
Local string &sFieldVal, &sLongname, &sShortname;
Local number &nXlat_cnt;
Local array of array of string &arValidSet;
Local array of string &arQueryFields;
Local array of string &arQueryFldHeadings;
Local array of string &temparray;
Local ApiObject &qryPromptColl;
/* do nothing if no settings */
If %This.Settings = Null Then
/* Mark the settings as incomplete */
%This.setSettingsComplete( False);
Return;
End-If;
&querySetting = %This.Settings.getItemByID("QueryName");
/* do nothing if no queryname setting */
If &querySetting = Null Then
/* Mark the settings as incomplete */
%This.setSettingsComplete( False);
Return
End-If;
&s_QueryName = &querySetting.Value;
/* do nothing if the queryname is blank */
If &s_QueryName = "" Then
/* Mark the settings as incomplete */
%This.setSettingsComplete( False);
Return;
End-If;
/* Looks like we have a queryname - now create the ParameterCollection*/
&Params = %This.createEmptyParameterCollection();
/* Look up the query, and copy the binds into the ParameterCollection */
Local Rowset &rsXlat = CreateRowset(Record.PSXLATITEM);
&MyQuery = %Session.GetQuery();
If &MyQuery = Null Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);
End-If;
&returnStatus = &MyQuery.Open(&s_QueryName, True, False);
If &returnStatus <> 0 Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);
End-If;
&qryPromptColl = &MyQuery.runtimeprompts;
/* Mark all prompts as not required... then call the checkQueryForRequiredPrompts method
to determine which are in fact required. */
&arRequiredPrompts = CreateArrayRept( False, &qryPromptColl.count);
%This.checkQueryForRequiredPrompts(&MyQuery.QuerySelect, &qryPromptColl);
For &i = 1 To &qryPromptColl.Count
&qryPrompt = &qryPromptColl.item(&i);
/* First, see if a parameter has already been created with this field name */
&thisParam = %This.ParameterCollection.getItemByID(&qryPrompt.Name);
If &thisParam = Null Then
/* No parameter with this field name exists ... just use the field name for the parameter name */
/* Note: This is the most common case */
&thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(&qryPrompt.name);
Else
/* A parameter exists with this field name ... use an explicit format */
/* Note: this is only used when a field is used more than once in the prompt list */
&thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(&qryPrompt.name | "." | &i);
End-If;
&thisParam.LongName = &qryPrompt.headingtext;
&thisParam.DefaultValue = "";
rem &thisParam.required=true;
&thisParam.Required = &arRequiredPrompts [&i];
&thisParam.allowChangesToRequired = ( Not (&arRequiredPrompts [&i]));
&thisParam.FieldType = &qryPrompt.FieldType;
Evaluate &qryPrompt.editType
When 1
When &NoTableEdit
&thisParam.EditType = 1;
Break;
When 2
When &PromptTable
&thisParam.EditType = 2;
Break;
When 3
When &TranslateTable
&thisParam.EditType = 3;
Break;
When 4
When &YesNo
&thisParam.EditType = 4;
Break;
When-Other
throw create PTPPB_PAGELET:EXCEPTION:InvalidValueException(&thisParam.Name | ".FieldType", String(&thisParam.FieldType));
End-Evaluate;
If &thisParam.EditType = 2 Then
&thisParam.PromptTable = &qryPrompt.PromptTable;
End-If;
If &thisParam.EditType = 3 Then
&thisParam.setRangeFromFieldTranslates(&qryPrompt.name);
End-If;
&Params.Insert(&thisParam);
End-For;
/* Also set up the OutputFields */
&Outputs = create PTPPB_PAGELET:UTILITY:Collection("OutputFields");
&MetaDataColl = &MyQuery.metadata;
For &i = 1 To &MetaDataColl.count
&ThisMetaData = &MetaDataColl.item(&i);
Evaluate &ThisMetaData.name
When "Field"
&TempStr = &ThisMetaData.value;
If &arQueryFields = Null Then
&arQueryFields = CreateArray(&TempStr);
Else
&arQueryFields.Push(&TempStr);
End-If;
Break;
When "Heading"
&TempStr = &ThisMetaData.value;
If &arQueryFldHeadings = Null Then
&arQueryFldHeadings = CreateArray(&TempStr);
Else
&arQueryFldHeadings.Push(&TempStr);
End-If;
Break;
End-Evaluate;
End-For;
For &j = 1 To &arQueryFields.Len
/* First, see if an output has already been created with this field name */
&thisOutputParam = &Outputs.getItemByID(&arQueryFields [&j]);
If &thisOutputParam = Null Then
/* No parameter with this field name exists ... just use the field name for the parameter name */
/* Note: This is the most common case */
&thisOutputParam = create PTPPB_PAGELET:UTILITY:OutputField(&arQueryFields [&j]);
Else
/* A parameter exists with this field name ... use an explicit format */
/* Note: this is only used when a field is used more than once in the prompt list */
&thisOutputParam = create PTPPB_PAGELET:UTILITY:OutputField(&arQueryFields [&j] | "." | &j);
End-If;
&thisOutputParam.Value = &arQueryFldHeadings [&j];
&thisOutputParam.HeadingXPATH = "xpath:/queryresult/queryfields/field[position()=" | &j | "]/headingtext";
&thisOutputParam.RowDataXPATH = "xpath:querydata[position()=" | &j | "]/text";
&Outputs.Insert(&thisOutputParam);
End-For;
%This.setOutputFields(&Outputs);
/* Add the reserved .MAXROWS parameter to the collection */
&thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(".MAXROWS");
&thisParam.LongName = MsgGetText(219, 252, "Message Not Found - Max Rows");
&thisParam.DefaultValue = "10";
&thisParam.Required = True;
&thisParam.FieldType = &thisParam.FIELDTYPE_NUMBER;
&Params.Insert(&thisParam);
/* Lastly - add in the queryname as an internal parameter */
&thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter("QueryName");
&thisParam.Value = &s_QueryName;
&thisParam.UsageType = &thisParam.USAGETYPE_INTERNAL;
&Params.Insert(&thisParam);
/* and set the ParameterCollection to be immutable */
&Params.setImmutable();
/* Mark the settings as complete */
%This.setSettingsComplete( True);
end-method;
/**
* Makes an exact duplicate of this QueryDataSource
*
*@return Object Copy of this QueryDataSource
*/
method Clone
/+ Returns Object +/
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.Clone +/
Local PTPPB_PAGELET:DataSource:QueryDataSource &NewDS = create PTPPB_PAGELET:DataSource:QueryDataSource(%This.ID);
&NewDS.PageletID = %This.PageletID;
&NewDS.ParameterCollection = %This.ParameterCollection.Clone();
&NewDS.initializeSettings(%This.Settings.Clone());
Return &NewDS;
end-method;
/**
* Returns the SQL for the Query
*
* @return String SQL for the Query
*/
get SQL
/+ Returns String +/
Local ApiObject &MySession, &QryObj;
Local string &SelectStatement;
Local string &DataSourceName;
Local number &returnStatus;
&QryObj = %Session.getquery();
If &QryObj = Null Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);
End-If;
&DataSourceName = &QueryName.Value;
&returnStatus = &QryObj.Open(&DataSourceName, True, False);
If &returnStatus <> 0 Then
throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);
End-If;
&SelectStatement = &QryObj.sql;
Return &SelectStatement;
end-get;
/**
* Generates an HTML link to execute the full PSQuery
*
* @return String URL for the full query
*/
method getSourceDataLinkHTML
/+ Returns String +/
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.getSourceDataLinkHTML +/
Local XmlNode &thisNode;
Local XmlNode &tableNode, &trNode, &tdNode, &queryLinkNode;
Local string &queryLinkText = MsgGetText(219, 402, "Full Query Results (Message Not Found)");
Local XmlDoc &XMLDoc = CreateXmlDoc("<?xml version='1.0' standalone='yes'?><TABLE/>");
&tableNode = &XMLDoc.DocumentElement;
&tableNode.AddAttribute("width", "94%");
&tableNode.AddAttribute("border", "0");
&trNode = &tableNode.AddElement("TR");
&tdNode = &trNode.AddElement("TD");
&queryLinkNode = &tdNode.AddElement("A");
&queryLinkNode.AddAttribute("class", "PSHYPERLINK");
&queryLinkNode.AddAttribute("target", "_top");
&queryLinkNode.AddAttribute("href", &queryLinkURL_inst);
&thisNode = &queryLinkNode.AddText(&queryLinkText);
Return &tableNode.GenXmlString();
end-method;
/**
* Determines the outputformat and sets hasXPATHInfo to true
*
* @return String "QUERY"
*/
method evaluateOutputFormat
/+ Returns String +/
/+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.evaluateOutputFormat +/
%This.hasXPATHInfo = True;
%This.BuilderContextNode = "/queryresult/queryrows/row";
Return %This.OUTPUTFORMAT_PSQUERY;
end-method;
/*********************************
PRIVATE METHODS FOLLOW
*********************************/
/*
* checkQueryForRequiredPrompts and checkCriteriaForRequiredPrompts
*
* Recursive methods that checks the query prompts, looking for all prompts that must
* be marked required. Essentially, any prompt found not to be in an IN or NOT IN
* criteria should be marked required.
*/
method checkQueryForRequiredPrompts
/+ &QuerySelect as ApiObject, +/
/+ &MainQueryPromptColl as ApiObject +/
Local ApiObject &criteria, &QuerySelectColl, &thisSQ;
&criteria = &QuerySelect.criteria;
%This.checkCriteriaForRequiredPrompts(&criteria, &MainQueryPromptColl);
&criteria = &QuerySelect.havingcriteria;
%This.checkCriteriaForRequiredPrompts(&criteria, &MainQueryPromptColl);
&QuerySelectColl = &QuerySelect.QuerySelects;
&thisSQ = &QuerySelectColl.first();
While &thisSQ <> Null
%This.checkQueryForRequiredPrompts(&thisSQ, &MainQueryPromptColl);
&thisSQ = &QuerySelectColl.next();
End-While;
end-method;
method checkCriteriaForRequiredPrompts
/+ &Criteria as ApiObject, +/
/+ &MainQueryPromptColl as ApiObject +/
Local ApiObject &thiscriteria, &subquery;
Local number &numPrompts, &promptID, &i;
&numPrompts = &MainQueryPromptColl.Count;
&thiscriteria = &Criteria.first();
While &thiscriteria <> Null
Evaluate &thiscriteria.expr2type
When %Query_ExprBind
&promptID = Value(LTrim(&thiscriteria.expr2constant1, ":"));
&arRequiredPrompts [&promptID] = True;
Break;
When %Query_Expression
When %Query_ExprExprConst
When %Query_ExprExprFld
When %Query_ExprBothExpr
If &thiscriteria.Expr2Expression1.BindFlag = True Then
For &i = 1 To &arRequiredPrompts.Len
If Find(":" | String(&i) | " ", &thiscriteria.Expr2Expression1.Text) > 0 Or
Find(":" | String(&i), &thiscriteria.Expr2Expression1.Text) > Len(&thiscriteria.Expr2Expression1.Text) - Len(":" | String(&i)) Then
&arRequiredPrompts [&i] = True;
End-If;
End-For;
End-If;
Break;
When %Query_ExprConstExpr
When %Query_ExprFldExpr
When %Query_ExprBothExpr
If &thiscriteria.Expr2Expression2.BindFlag = True Then
For &i = 1 To &arRequiredPrompts.Len
If Find(":" | String(&i) | " ", &thiscriteria.Expr2Expression2.Text) > 0 Or
Find(":" | String(&i), &thiscriteria.Expr2Expression2.Text) > Len(&thiscriteria.Expr2Expression1.Text) - Len(":" | String(&i)) Then
&arRequiredPrompts [&i] = True;
End-If;
End-For;
End-If;
End-Evaluate;
&thiscriteria = &Criteria.next();
End-While;
end-method;
Next I configured my new Data Type so I could use it in Pagelet Wizard.
As I stated earlier, Pagelet Wizard cannot take a Querystring paramter and this is how the Related Content Framework passes parameters to it's services. So I created a new record called WEBLIB_ZZ_RLC and added the ISCRIPT1 field and the code below to the FieldFormula.
import PTPPB_PAGELET:*;
import PTPPB_PAGELET:DataSource:*;
import ZZ_JSON:JSONEncoder;
Local XmlDoc &xmlDoc;
Function IScript_RLC_CSS()
%Response.SetContentType("text/css");
%Response.Write(GetHTMLText(HTML.ZZ_RLC_SWAN_CSS));
End-Function;
Function json()
Local array of XmlNode &rows = &xmlDoc.DocumentElement.FindNodes("/root/table/tbody/tr/td/table/tbody/tr");
Local array of XmlNode &td;
Local ZZ_JSON:JSONEncoder &utility = create ZZ_JSON:JSONEncoder();
Local boolean &isFirstRow = True;
%Response.Write("[");
rem start on row two to avoid the table headers;
For &x = 2 To &rows.Len
If (&isFirstRow) Then
&isFirstRow = False;
Else
%Response.Write(", ");
End-If;
%Response.Write("{""trClass"": """ | &utility.encode(&rows [&x].GetAttributeValue("class")) | """, ""valign"": """ | &utility.encode(&rows [&x].GetAttributeValue("valign")) | """, ""td"": [");
&td = &rows [&x].FindNodes("td");
&isFirstData = True;
For &y = 1 To &td.Len
If (&isFirstData) Then
&isFirstData = False;
Else
%Response.Write(", ");
End-If;
&Anchor = &td [&y].FindNode("A");
If &Anchor.isnull Then
%Response.Write("""" | &utility.encode(&td [&y].NodeValue) | """");
Else
%Response.Write("{""href"": """ | &utility.encode(&Anchor.GetAttributeValue("href")) | """, ""title"": """ | &utility.encode(&Anchor.GetAttributeValue("title")) | """, ""value"": """ | &Anchor.nodevalue | """}");
End-If;
End-For;
%Response.Write("]}");
End-For;
%Response.Write("]");
End-Function;
Function iScript_Request()
Local array of string &bindArray = CreateArray(%Request.GetParameter("BIND1"), %Request.GetParameter("BIND2"), %Request.GetParameter("BIND3"), %Request.GetParameter("BIND4"), %Request.GetParameter("BIND5"), %Request.GetParameter("BIND6"), %Request.GetParameter("BIND7"), %Request.GetParameter("BIND8"), %Request.GetParameter("BIND9"), %Request.GetParameter("BIND10"));
Local array &dsParms = CreateArray();
Local string &START = %Request.GetParameter("START");
Local string &AJAX = %Request.GetParameter("AJAX");
Local string &PAGELETID = %Request.GetParameter("PAGELETID");
Local number &chunk = 100;
Local PTPPB_PAGELET:PageletWizard &PWAPI = create PTPPB_PAGELET:PageletWizard();
Local PTPPB_PAGELET:Pagelet &thisPagelet = &PWAPI.getPageletByID(&PAGELETID, True);
SQLExec("select PTPPB_VALUE from ps_PTPPB_PGLT_PRMS where PTPPB_PAGELET_ID = :1 and PTPPB_FIELDNAME = 'QueryName'", &PAGELETID, &QryName);
&ajaxQS = "?AJAX=Y&PAGELETID=" | &PAGELETID;
For &x = 1 To &bindArray.Len
If All(&bindArray [&x]) Then
Local string &thisBind = "BIND" | String(&x);
SQLExec("SELECT FIELDNAME||'.'||BNDNUM FROM PSQRYBIND WHERE OPRID = ' ' AND QRYNAME = :1 AND BNDNAME=:2", &QryName, &thisBind, &qryBind);
REM MAP BIND VARIABLES TO THE RLC QUERY DATASOURCE PARAMETERS;
Local PTPPB_PAGELET:DataSource:DataSourceParameter &Temp = &thisPagelet.DataSource.addParameter(&qryBind, &bindArray [&x]);;
&dsParms.Push(&Temp);
&ajaxQS = &ajaxQS | "&" | &thisBind | "=" | &bindArray [&x];
End-If;
End-For;
&loadURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "iScript_Request") | &ajaxQS | "&START=";
&cssURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "IScript_RLC_CSS");
&jQueryURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_jQuery_1_7_1");
If &AJAX <> "Y" Then
&AJAX = "N";
Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStart = &thisPagelet.DataSource.addParameter("START", "1");
Else
Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStartAJAX = &thisPagelet.DataSource.addParameter("START", &START);
%Response.SetContentType("application/json");
End-If;
Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmq = &thisPagelet.DataSource.addParameter("QueryName", &QryName);
Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR = &thisPagelet.DataSource.addParameter(".MAXROWS", String(&chunk));
Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR1 = &thisPagelet.DataSource.addParameter("AJAX", &AJAX);
&string = &thisPagelet.Execute();
&html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL);
If &AJAX = "N" Then
%Response.Write(&html);
Else
REM DO JSON PROCESSING;
&xmlDoc = CreateXmlDoc("<?xml version='1.0'?><root>" | &string | "</root>");
json();
End-If;
End-Function;
Lets take a look at this WEBLIB. First you are going to need to inject jQuery, I used version 1.7.1 which I have stored in an HTML object, if you don't know how to do this, then please search google for "serving jquery in Peoplesoft". The Import ZZ_JSON:JSONEncoder documentation can be found on Jim Marion's blog. Third, I added 10 get parameters to get values passed into this weblib from the Related Content Framework. You can add more, but I thought 10 will do. Fourth, I created the DataSource parameters for the query within this WEBLIB, this prevents it from happening within the DataSource itself, very clever Oracle! Fifth, I add a style sheet to the embedded Pagelet to give it some style. This is the CSS stored in the ZZ_RLC_SWAN_CSS:
.PSLEVEL1GRIDODDROW,.PSLEVEL2GRIDODDROW,.PSLEVEL3GRIDODDROW,.PSSRCHRESULTSODDROW{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);background-color:rgb(247,247,247);border-bottom-width:1px;border-right-width:1px;border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-bottom-style:solid;border-right-style:solid;}.PSLEVEL1GRIDEVENROW,.PSLEVEL2GRIDEVENROW,.PSLEVEL3GRIDEVENROW,.PSSRCHRESULTSEVENROW{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);background-color:rgb(255,255,255);border-bottom-width:1px;border-right-width:1px;border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-bottom-style:solid;border-right-style:solid;}.PSLEVEL1GRIDCOLUMNHDRTXT,.PSLEVEL3GRIDCOLUMNHDRTXT,.PSLEVEL1GRIDCOLUMNHDR:LINK,.PSLEVEL1GRIDCOLUMNHDR:VISITED,.PSLEVEL1GRIDCOLUMNHDRDISABLED,.PSLEVEL1SSGRIDCOLUMNHDR:LINK,.PSLEVEL1SSGRIDCOLUMNHDR:VISITED,.PSLEVEL1SSGRIDCOLUMNHDRDISABLED,.PSLEVEL3GRIDCOLUMNHDR:LINK,.PSLEVEL3GRIDCOLUMNHDR:VISITED,.PSLEVEL3GRIDCOLUMNHDRDISABLED{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-style:none;border-bottom-style:none;border-left-style:none;border-right-style:none;}.PSLEVEL1GRIDCOLUMNHDR,.PSLEVEL2GRIDCOLUMNHDR,.PSLEVEL3GRIDCOLUMNHDR{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-width:1px;border-bottom-width:1px;border-right-width:1px;border-top-color:rgb(203,203,203);border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-top-style:solid;border-bottom-style:solid;border-right-style:solid;}.PSGRIDCOLUMNHDR{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-width:1px;border-bottom-width:1px;border-top-color:rgb(163,190,216);border-bottom-color:rgb(163,190,216);border-top-style:solid;border-bottom-style:solid;}.PSDROPDOWNLIST,.PSLEVEL1GRIDROW,.PSLEVEL2GRIDROW,.PSLEVEL3GRIDROW,.PSLONGEDITBOX,.PSSRCHINSTRUCTIONS,.PSSRCHRESULTSFOOTER,.PSTEXT,.PSQRYINSTRUCTIONS,.PSQRYRESULTSFOOTER,.PT_MCFDROPDOWNLIST,.PT_MCFEDITBOX{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);}
The json() function is for AJAX calls back to the service so it can load the next 100 rows as the user scrolls. The JavaScript for this behavior is in the HTML.ZZ_RLC_REQUEST. This is documented below:
<script type="text/javascript" src="%BIND(:5)"></script>
<script type="text/javascript">
function importStylesheet(sheetUrl) {
var ss = document.createElement("link");
ss.rel = "stylesheet";
ss.href = sheetUrl;
ss.type = "text/css";
document.getElementsByTagName("head")[0].appendChild(ss);
}
importStylesheet("%bind(:4)");
var processing;
var chunk = %bind(:3);
var StartRow = chunk + 1;
var iScriptURL = "%bind(:1)";
$(document).ready(function(){
$(window).scroll(function(e){
if (processing)
return false;
if ($(window).scrollTop() >= ($(document).height() - $(window).height())*0.7){
processing = true;
iScriptURL2 = iScriptURL+StartRow;
$.get(iScriptURL2, function(loaded){
var htmlRows = '';
StartRow = StartRow + chunk;
for (var i=0;i<loaded.length;i++)
{
//debugger;
htmlRows = htmlRows + "<tr class='" + loaded[i].trClass + "' valign='" + loaded[i].valign + "'>";
for (var d=0;d<loaded[i].td.length;d++)
{
if ( typeof loaded[i].td[d].href != 'undefined')
{
htmlRows = htmlRows + "<td><a href='" + loaded[i].td[d].href + "'>" + loaded[i].td[d].value +"</a></td>";
}
else
{
htmlRows = htmlRows + "<td>" + loaded[i].td[d] + "</td>";
}
}
}
$('.PSLEVEL1GRID tr:last').after(htmlRows);
if (loaded.length>0){
processing = false;
};
});
};
});
});
</script>
%bind(:2)
Now all I have to do is create a query and build my Pagelet Wizard definition using the the Related Content Query DataSource, the hidden gems of this approach is that it allowed me to take advandate of Pagelet Wizard's Post-Transformation Processing. This means I can define query output as hyperlinks and even format the output of the query. And finally I created my Related Content Service and associated it to the eComp+ Component at the Page Level so I could pass values from the page definition to my iScript.
As for the Parameters, please note that the Parameter Name for the Bind# need to match up with the Query's Bind Name, which typically defaults to BIND1, BIND2 etc.... Also, if you click the link within the Related Content Frame it will replace that frame with the transaction configured within the link. And lastly, this can be used outside of Related Content, if you properly pass the parameters it could be used as a drilldown in a graph or where ever your imagination takes you!
No comments:
Post a Comment