Using Related Content and jQuery lightbox to turn any PeopleSoft Transaction into a Dashboard.

In my two previous posts I showed how to implement a Colorbox and use Pagelet Wizard in related content to load up to 25,000 rows of related data into an HTML table.  In the Pagelet Wizard posting it will load the first 100 rows and fetch the next 100 rows as the user scrolls the table.  But as I noted in that posting if you define a link in Pagelet Wizard it will overlay the Related Content Area frame and your related data table will be gone.  Since I did not think this was a great UX for the user, I really wanted a way to open this content into a jQuery lightbox, I choose Colorbox based on its flexiblity and its friendly license.

When I examined the HTML that was being generated by Peoplesoft I noticed it was loading the content into three seperate iFrames.

There is a TargetContent Frame, this is the largest area on the page and would be the best location to present the content from the related content.  Then there are two related content frames, the Related Content frame and a child frame called the related content area frame.  The question was how do I load the content from the child frame to the uncle frame?  I call the TargetContent frame an uncle, because it is on the same level as the Related Content frame.  In order to accomplish this I needed a lightbox that could be called from the jQuery Namespace from the child frame.  In my research I found two lightboxes that were suitable for this type of use.  First was the Fancybox2, it is a very good jQuery lightbox, but it also requires a license be purchased for Commercial development.  Now that is only $89 for multi-domains or $19 for one domain, but still it has a cost beyond learning how to use it.  The second option is the Colorbox, this is the option I went with since it is lightweight, flexible and licensed under the MIT License.  

Now if you have already followed the steps of my previous 2 postings, Pagelet Wizard for Related Content and Colorbox. Then all you need to do is modify the iScript in the Pagelet Wizard post to deliver the Colorbox to the transaction.

This is my modified iScript:

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");
 rem  &FANCYBOX_CSS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_FBOX2, Field.ISCRIPT1, "FieldFormula", "IScript_FancyboxCss2");
 rem  &FANCYBOX_PACK_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_FBOX2, Field.ISCRIPT1, "FieldFormula", "IScript_FANCYBOX_2_1_5_PACK");
   &COLORBOX_CSS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_CB, Field.ISCRIPT1, "FieldFormula", "IScript_colorbox_css");
   &COLORBOX_JS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_CB, Field.ISCRIPT1, "FieldFormula", "IScript_ColorBoxJs");
   
   
   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();
   
   
   REM &html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL, &FANCYBOX_CSS_URL, &FANCYBOX_PACK_URL);
   &html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL, &COLORBOX_CSS_URL, &COLORBOX_JS_URL);
   
   If &AJAX = "N" Then
      
      %Response.Write(&html);
      
   Else
      REM DO JSON PROCESSING;
      &xmlDoc = CreateXmlDoc("" | &string | "");
      
      json();
      
   End-If;
   
End-Function;

Then modify the HTML.ZZ_RLC_REQUEST JavaScript to load jQuery in the TargetContent Frame and then load the Colorbox.js if the user request the content.

<script type="text/javascript"  src="%BIND(:5)"></script>
<script type="text/javascript"  src="%BIND(:7)"></script>

<script type="text/javascript">

function setcolorbox() {
    $('a').click(function() {
       if (typeof tgtFrame.contentWindow.$.colorbox ==='undefined') {
        var thisHref = this.href;
        tgtFrame.contentWindow.$.getScript(colorbox,function() {
        var pscUrl = thisHref.replace("/psp/", "/psc/");
        tgtFrame.contentWindow.$.colorbox({href : pscUrl,  iframe: true,  height: '90%', width: '95%'})});
        return false;
       }
       else
       {
       var pscUrl = this.href.replace("/psp/", "/psc/");
       tgtFrame.contentWindow.$.colorbox({href : pscUrl,   iframe: true,  height: '90%', width: '95%'});
       return false; 
       } 
    });
};

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)");
  importStylesheet("%bind(:6)");

 
var processing;
var chunk = %bind(:3);
var StartRow = chunk + 1;
var iScriptURL = "%bind(:1)";
var colorboxCss = "%bind(:6)";
var colorbox = "%bind(:7)";



var tgtFrame = window.top.document.getElementById('ptifrmtgtframe');
var tgtHead = tgtFrame.contentWindow.document.getElementsByTagName('Head');

var fbCss = document.createElement("link");
fbCss.rel = "stylesheet";
fbCss.href = colorboxCss;
fbCss.type = "text/css";
 
var scriptJQ = document.createElement('script');
scriptJQ.type='text/javascript';
scriptJQ.src = "%bind(:5)";


tgtHead[0].appendChild(scriptJQ);
tgtHead[0].appendChild(fbCss);

$(document).ready(function(){
 
    setcolorbox();    
      
    $(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);
 //rem setup new rows to open in colorbox;
 setcolorbox();

if (loaded.length>0){
processing = false;
};
});
        };
    });
});
</script>

 
%bind(:2)

The results are just stunning!

Colorbox in PeopleSoft

My new favorite lightbox for PeopleSoft is Jack Moore's Colorbox.  It is released under the MIT License and is free to use and customize to your liking.  I use iScripts to deliver the Colorbox to my applications; all you need to do to implement the Colorbox in your instance is follow these instructions below.

Download the Colorbox and pick the example you like best from one of the 5 examples in the download.

After you have made your selection you need to open the images folder under the example# that you chose and start loading the example's images into PeopleSoft.  I chose example1, so I loaded 5 photos into Application Designer.

Then you need to create and HTML object and copy the CSS from the example you chose and replace the relative urls for the images with %bind(:#) variables.

/*
    Colorbox Core Style:
    The following CSS is consistent between example themes and should not be altered.
*/
#colorbox, #cboxOverlay, #cboxWrapper{position:absolute; top:0; left:0; z-index:9999; overflow:hidden;}
#cboxWrapper {max-width:none;}
#cboxOverlay{position:fixed; width:100%; height:100%;}
#cboxMiddleLeft, #cboxBottomLeft{clear:left;}
#cboxContent{position:relative;}
#cboxLoadedContent{overflow:auto; -webkit-overflow-scrolling: touch;}
#cboxTitle{margin:0;}
#cboxLoadingOverlay, #cboxLoadingGraphic{position:absolute; top:0; left:0; width:100%; height:100%;}
#cboxPrevious, #cboxNext, #cboxClose, #cboxSlideshow{cursor:pointer;}
.cboxPhoto{float:left; margin:auto; border:0; display:block; max-width:none; -ms-interpolation-mode:bicubic;}
.cboxIframe{width:100%; height:100%; display:block; border:0; padding:0; margin:0;}
#colorbox, #cboxContent, #cboxLoadedContent{box-sizing:content-box; -moz-box-sizing:content-box; -webkit-box-sizing:content-box;}

/* 
    User Style:
    Change the following styles to modify the appearance of Colorbox.  They are
    ordered & tabbed in a way that represents the nesting of the generated HTML.
*/
#cboxOverlay{background:#000;}
#colorbox{outline:0;}
    #cboxTopLeft{width:21px; height:21px; background:url(%BIND(:2)) no-repeat -101px 0;}
    #cboxTopRight{width:21px; height:21px; background:url(%bind(:2)) no-repeat -130px 0;}
    #cboxBottomLeft{width:21px; height:21px; background:url(%bind(:2)) no-repeat -101px -29px;}
    #cboxBottomRight{width:21px; height:21px; background:url(%bind(:2)) no-repeat -130px -29px;}
    #cboxMiddleLeft{width:21px; background:url(%bind(:2)) left top repeat-y;}
    #cboxMiddleRight{width:21px; background:url(%bind(:2)) right top repeat-y;}
    #cboxTopCenter{height:21px; background:url(%bind(:3)) 0 0 repeat-x;}
    #cboxBottomCenter{height:21px; background:url(%bind(:3)) 0 -29px repeat-x;}
    #cboxContent{background:#fff; overflow:hidden;}
        .cboxIframe{background:#fff;}
        #cboxError{padding:50px; border:1px solid #ccc;}
        #cboxLoadedContent{margin-bottom:28px;}
        #cboxTitle{position:absolute; bottom:4px; left:0; text-align:center; width:100%; color:#949494;}
        #cboxCurrent{position:absolute; bottom:4px; left:58px; color:#949494;}
        #cboxLoadingOverlay{background:url(%bind(:4)) no-repeat center center;}
        #cboxLoadingGraphic{background:url(images/loading.gif) no-repeat center center;}

        /* these elements are buttons, and may need to have additional styles reset to avoid unwanted base styles */
        #cboxPrevious, #cboxNext, #cboxSlideshow, #cboxClose {border:0; padding:0; margin:0; overflow:visible; width:auto; background:none; }
        
        /* avoid outlines on :active (mouseclick), but preserve outlines on :focus (tabbed navigating) */
        #cboxPrevious:active, #cboxNext:active, #cboxSlideshow:active, #cboxClose:active {outline:0;}

        #cboxSlideshow{position:absolute; bottom:4px; right:30px; color:#0092ef;}
        #cboxPrevious{position:absolute; bottom:0; left:0; background:url(%bind(:2)) no-repeat -75px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxPrevious:hover{background-position:-75px -25px;}
        #cboxNext{position:absolute; bottom:0; left:27px; background:url(%bind(:2)) no-repeat -50px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxNext:hover{background-position:-50px -25px;}
        #cboxClose{position:absolute; bottom:0; right:0; background:url(%bind(:2)) no-repeat -25px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxClose:hover{background-position:-25px -25px;}

/*
  The following fixes a problem where IE7 and IE8 replace a PNG's alpha transparency with a black fill
  when an alpha filter (opacity change) is set on the element or ancestor element.  This style is not applied to or needed in IE9.
  See: http://jacklmoore.com/notes/ie-transparency-problems/
*/
.cboxIE #cboxTopLeft,
.cboxIE #cboxTopCenter,
.cboxIE #cboxTopRight,
.cboxIE #cboxBottomLeft,
.cboxIE #cboxBottomCenter,
.cboxIE #cboxBottomRight,
.cboxIE #cboxMiddleLeft,
.cboxIE #cboxMiddleRight {
    filter: progid:DXImageTransform.Microsoft.gradient(startColorstr=#00FFFFFF,endColorstr=#00FFFFFF);
}

Please note, that I modified the css in example one to use a background of black for the overlay.

#cboxOverlay{background:#000;}

Next copy the "jquery.colorbox.js" into an html object and save it using your local naming standards.  Note, I used the "jquery.colorbox-min" initially but I kept getting JavaScript errors and when I used the file above it worked without issue.

Then create a new weblib record and add the ISCRIPT1 field to your record and save this new definition as a derived work record then add the following code to your ISCRIPT1 FieldFormula PeopleCode and modify the code to match your sites naming standards and image that you have previously imported.  If you use my modified CSS then your Bind variables will match.

Here is my iScript code

Function IScript_colorbox_css   
   &bind1 = %Response.GetImageURL(Image.ZZ_CB_OVERLAY);
   &bind2 = %Response.GetImageURL(Image.ZZ_CB_CONTROLS);
   &bind3 = %Response.GetImageURL(Image.ZZ_CB_BORDER);
   &bind4 = %Response.GetImageURL(Image.ZZ_CB_LOADING_BACKGROUND);
   &bind5 = %Response.GetImageURL(Image.ZZ_CB_LOADING);
   
   %Response.SetContentType("text/css");
   %Response.Write(GetHTMLText(HTML.ZZ_CB_CSS, &bind1, &bind2, &bind3, &bind4, &bind5));
End-Function;



Function IScript_ColorBoxJs
   %Response.SetContentType("text/javascript");
   %Response.Write(GetHTMLText(HTML.ZZ_COLORBOX_JS));
End-Function;

Near Real-time integration without customization

I was speaking with a former colleague, who works at another PeopleSoft shop about integrating current employee's with their talent management system, basically populating current employee demographic data, so they don't have to populate this information when they apply for an internal position.  He told me that he was handed a design that required a nightly batch process to run and identify the changes and send them via integration broker.  

This got us talking about ways to do more real time integration without customizing.  And I shared with him how we use the delivered PERSON_BASIC_SYNC to integrate with our business continuity system, AlertFind.  Our approach was to piggyback off the delivered asynchronous message to generate a synchronous message to AlertFind.

 First you have to make sure you message is active and that you have security defined.

Next you have to configure a Local to Local routing.

Then you need to write a Handler to parse the Local to Local message to get the employee that you are wanting to process out of the message.

This is our Handler:

import ZZ_AF_SEND:UpdateUser;
import PS_PT:Integration:INotificationHandler;



class PersonSync_AlertFind implements PS_PT:Integration:INotificationHandler
   method PersonSync_AlertFind();
   method OnNotify(&_MSG As Message);
end-class;

method PersonSync_AlertFind
end-method;

method OnNotify
   /+ &_MSG as Message +/
   /+ Extends/implements PS_PT:Integration:INotificationHandler.OnNotify +/
   
   Local string &PubNodeName, &LocalNodeName;
   Local Record &idmQueue = CreateRecord(Record.ZZ_IDM_QUEUE);
   Local Record &idmData = CreateRecord(Record.ZZ_IDM);
   
   
   /* process only if message was published on the local node with the INTERNAL version */
   &PubNodeName = &_MSG.PubNodeName;
   SQLExec(SQL.EO_SEL_LOCALNODE, &LocalNodeName);
   If &LocalNodeName = &PubNodeName Then
      
      
      Local string &Emplid;
      &Emplid = &_MSG.GetRowset().GetRow(1).PERSON.EMPLID.Value;
      
      &idmData.EMPLID2.Value = &Emplid;
      &idmData.ZZ_REC_TYPE.Value = "C";
      If &idmData.SelectByKey() Then
         Local ZZ_AF_SEND:UpdateUser &AlertFind = create ZZ_AF_SEND:UpdateUser();
         &AlertFind.arrayOfUsers = CreateArray(&Emplid);
         &AlertFind.Execute();
      End-If;

      
   End-If;
   
end-method;

The handler above instantiates an UpdateUser that takes an array of employee ids, this package handles the message creation and the integration broker call to publish the update, this package is not shown in this blog posting.  

Then configure your handler to be active within the PERSON_BASIC_SYNC.

  This approach is basically asynchronous to synchronous approach that gives our organization near real-time integration with our Business Continuity tool, AlertFind.

Using Pagelet Wizard in Related Content

While at Collaborate 14 this year I was visiting with the wonderful people at WorkStrategy, Inc and discussing their eComp+ product that we happily utilize here at UMB.  While I was there discussing their product they shared with me an interesting request that they frequently get, about seeing all the data from the MSS page without drilling down through the manager's direct reports.  This immediatly made me think of PeopleSoft's Related content, but the dillema was do you write an iScript to format the data specifically for this component or better, could I write one iScript that could be used on many components.  I went for the latter, but that begged the question, do I really want to write a framework that takes data and formats into html to be loaded into the Related Framework iframe?  And then it hit me, could I use Pagelet Wizard to format the HTML?

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!