Share

How to sort a VBC using eScript

Hi everybody. As you know, when using a VBC, the returned records can not be sorted (because its a VBC restriction). In this post i will give you a scripting solution to do this. The explanation is a little long, please be patient.

Note: I pass the property set returned by the EAI WF to a Global PS (Bold code in ExecuteMyFind sub function)

Create a Business Service with the next functions:

(declarations)

var oCache = TheApplication().NewPropertySet();

var arrCache = new Array();

function Service_PreInvokeMethod (MethodName, Inputs, Outputs)

{

switch(MethodName)

{

case "Init":

Init(Inputs, Outputs);

return (CancelOperation);

case "Query":

Query(Inputs, Outputs);

return (CancelOperation);

default:

return (ContinueOperation);

}

}

function CheckError(oPropertySet)

{

if(oPropertySet.GetChildCount() > 0)

{

if(oPropertySet.GetChild(0).GetType().toLowerCase() == "status" && parseInt(oPropertySet.GetChild(0).GetProperty("Status")) > 0)

{

return(true);

}

else

{

return(false);

}

}

else

{

return(false);

}

}

function CheckSearchSpec(Inputs, &strNewLinkFieldValue)

{

var arrSearchFields = new Array();

var bolChildFound = false;

var bolFieldFound = false;

if(GetParameter(Inputs.GetProperty("Parameters"), "LinkFieldName") != "PARAMETER_NOT_FOUND")

{

for(var i = 0; i < Inputs.GetChildCount(); i++)

{

if(Inputs.GetChild(i).GetType() == "search-spec")

{

bolChildFound = true;

break;

}

}

if(bolChildFound)

{

arrSearchFields = ParseSearchSpec(Inputs.GetChild(i), arrSearchFields);

for(var i = 0; i < arrSearchFields.length; i++)

{

if(arrSearchFields[i][0] == GetParameter(Inputs.GetProperty("Parameters"), "LinkFieldName"))

{

if(arrSearchFields.length == 1)

{

var bolReturn = false;

}

else

{

if(arrSearchFields[i][1] != HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "LinkFieldValue", null, "Get"))

{

var bolReturn = false;

}

else

{

var bolReturn = true;

}

}

strNewLinkFieldValue = arrSearchFields[i][1];

arrSearchFields = null;

return(bolReturn);

}

}

if(arrSearchFields.length > 0)

{

arrSearchFields = null;

strNewLinkFieldValue = null;

return(false);

}

}

}

if(GetParameter(Inputs.GetProperty("Parameters"), "ShowPopup") != "PARAMETER_NOT_FOUND")

{

var PulsaBoton = TheApplication().GetProfileAttr("ShowPopup");

if (PulsaBoton == "1")

{

TheApplication().SetProfileAttr("ShowPopup","0");

}

else

{

return(false);

}

}

arrSearchFields = null;

strNewLinkFieldValue = null;

return(true);

}

function ExecuteMyFind(Inputs, Outputs)

{

var bolReturn = false;

try

{

var Outputs1 = TheApplication().GetPS("PS_GlobalRecords");

if (Outputs1 != null)

{

var n = Outputs1.GetChildCount();

var i = n;

while (i > 0)

{

var OutputsChild = Outputs1.GetChild( n – i );

var mField1 = OutputsChild.GetProperty("mField1");

var mField2 = OutputsChild.GetProperty("mField1");

var mField3 = OutputsChild.GetProperty("mField1");

Outputs.AddChild(OutputsChild);

OutputsChild = null;

i = i – 1;

}

}

var Outputs1 = null;

bolReturn = true;

}

catch(e)

{

bolReturn = false;

}

finally

{

//Reset Search & Sort Cache

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Remove")

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SortSpec", null, "Remove")

var oService = null;

}

return(bolReturn);

}

function GetFieldType(strBusCompName, strFieldName)

{

//Return Field Type

var intCount = 0;

var strUserProperty = Trim(this.GetProperty("Field Type " + intCount.toString()));

while(strUserProperty != null && strUserProperty != "")

{

var arrProperties = strUserProperty.split(",");

if(arrProperties.length == 3)

{

if(Trim(arrProperties[0]).substring(1, Trim(arrProperties[0]).length – 1) == strBusCompName && Trim(arrProperties[0]).charAt(0) == '"' && Trim(arrProperties[0]).charAt(Trim(arrProperties[0]).length – 1) == '"')

{

if(Trim(arrProperties[1]).substring(1, Trim(arrProperties[1]).length – 1) == strFieldName && Trim(arrProperties[1]).charAt(0) == '"' && Trim(arrProperties[1]).charAt(Trim(arrProperties[1]).length – 1) == '"')

{

if(Trim(arrProperties[2]).substring(1, Trim(arrProperties[2]).length – 1).length != 0 && Trim(arrProperties[2]).charAt(0) == '"' && Trim(arrProperties[2]).charAt(Trim(arrProperties[2]).length – 1) == '"')

{

return(Trim(arrProperties[2]).substring(1, Trim(arrProperties[2]).length – 1).toLowerCase());

}

}

}

}

intCount++;

strUserProperty = Trim(this.GetProperty("Field Type " + intCount.toString()));

}

//Default Return

return("text");

}

function GetParameter(strParameterString, strParamaterName)

{

if(strParameterString != null && strParameterString != "")

{

var arrParameters = strParameterString.split(";");

for(var i = 0; i < arrParameters.length; i++)

{

var arrField = arrParameters[i].split("=");

if(arrField.length = 2)

{

if(arrField[0].toString() == strParamaterName.toString())

{

return(arrField[1].toString());

}

}

var arrField = null;

}

var arrParameters = null;

return("PARAMETER_NOT_FOUND");

}

else

{

return("PARAMETER_NOT_FOUND");

}

}

function GetSortSpec(Inputs, &strSortField, &strSortType)

{

var bolChildFound = false;

for(var i = 0; i < Inputs.GetChildCount(); i++)

{

if(Inputs.GetChild(i).GetType() == "sort-spec")

{

bolChildFound = true;

break;

}

}

if(bolChildFound)

{

if(Inputs.GetChild(i).GetChildCount() > 0)

{

if(Inputs.GetChild(i).GetChild(0).GetType() == "sort")

{

strSortField = Inputs.GetChild(i).GetChild(0).GetProperty("field");

strSortType = Inputs.GetChild(i).GetChild(0).GetValue();

return(strSortField.toString() + " (" + strSortType.toString() + ")");

}

}

}

else

{

return(null);

}

}

function HandleCache(strBusCompName, oPropertySet, strType, strValue, strMode)

{

if(strBusCompName == null || strBusCompName == "" || typeof(strBusCompName) == "undefined")

{

return(null);

}

if(strMode.toLowerCase() == "add" || strMode.toLowerCase() == "remove" || strMode.toLowerCase() == "get")

{

if(strType.toLowerCase() == "query")

{

for(var i = 0; i < oCache.GetChildCount(); i++)

{

if(oCache.GetChild(i).GetType() == strBusCompName.toLowerCase())

{

if(strMode.toLowerCase() == "get")

{

var oReturn = oCache.GetChild(i).Copy();

oReturn.SetType("");

return(oReturn)

}

else

{

oCache.RemoveChild(i);

i–;

}

}

}

if(strMode.toLowerCase() == "add" && typeof(oPropertySet) == "object")

{

oPropertySet.SetType(strBusCompName.toLowerCase());

oCache.AddChild(oPropertySet);

}

}

else

{

if(strMode.toLowerCase() == "add")

{

arrCache[strBusCompName.toLowerCase()][strType.toLowerCase()] = strValue;

}

else if(strMode.toLowerCase() == "remove")

{

arrCache[strBusCompName.toLowerCase()][strType.toLowerCase()] = null;

}

else if(strMode.toLowerCase() == "get" && typeof(arrCache[strBusCompName.toLowerCase()][strType.toLowerCase()]) != "undefined")

{

return(arrCache[strBusCompName.toLowerCase()][strType.toLowerCase()]);

}

}

}

return(null);

}

function Init(Inputs, Outputs)

{

Outputs.SetProperty("mField1","");

Outputs.SetProperty("mField2","");

Outputs.SetProperty("mField3","");

}

function LeftTrim(strStringToTrim)

{

var strTrimChars = new String(" \t\n\r");

var strString = new String(strStringToTrim);

if (strTrimChars.indexOf(strString.charAt(0)) != -1)

{

var i = strString.length;

var j = 0;

while (j < i && strTrimChars.indexOf(strString.charAt(j)) != -1)

j++;

strString = strString.substring(j, i);

}

var strTrimChars = null;

var j = null;

var i = null;

return(strString);

}

function ParseSearchSpec(oSearchNode, arrSearchFields)

{

var bolFieldFound = false;

//Check if Child Node is an Indentifier

for(var i = 0; i < oSearchNode.GetChildCount(); i++)

{

if(oSearchNode.GetChild(i).GetType() == "node" && oSearchNode.GetChild(i).GetProperty("node-type") != "Binary Operator")

{

if(oSearchNode.GetChild(i).GetProperty("node-type") == "Identifier")

{

if(bolFieldFound)

{

arrSearchFields[arrSearchFields.length – 1][0] = oSearchNode.GetChild(i).GetValue();

}

else

{

arrSearchFields[arrSearchFields.length][0] = oSearchNode.GetChild(i).GetValue();

bolFieldFound = true;

}

}

else if(oSearchNode.GetChild(i).GetProperty("node-type") == "Constant")

{

if(bolFieldFound)

{

arrSearchFields[arrSearchFields.length – 1][1] = oSearchNode.GetChild(i).GetValue();

}

else

{

arrSearchFields[arrSearchFields.length][1] = oSearchNode.GetChild(i).GetValue();

bolFieldFound = true;

}

}

}

}

bolFieldFound = null;

//Check if Child Node is an Operator

for(var i = 0; i < oSearchNode.GetChildCount(); i++)

{

if(oSearchNode.GetChild(i).GetType() == "node" && oSearchNode.GetChild(i).GetProperty("node-type") == "Binary Operator")

{

arrSearchFields = ParseSearchSpec(oSearchNode.GetChild(i), arrSearchFields);

}

}

return(arrSearchFields);

}

function Query(Inputs, Outputs)

{

var strSortField = null;

var strSortType = null;

var strNewLinkFieldValue = null;

var value3 = Inputs.GetProperty(this.GetProperty("Business Component Search String Input Property"));

var value4 = CheckSearchSpec(Inputs, strNewLinkFieldValue);

if(Inputs.GetProperty(this.GetProperty("Business Component Search String Input Property")) != "" && CheckSearchSpec(Inputs, strNewLinkFieldValue))

{

//Check Query Mode

var value1 = Inputs.GetProperty(this.GetProperty("Business Component Search String Input Property"));

var value2 = HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Get");
if(HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Get") != null && HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Get") != "")

{

if(Inputs.GetProperty(this.GetProperty("Business Component Search String Input Property")) == HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Get"))

{

var bolExecuteQuery = false;

}

else

{

var bolExecuteQuery = true;

}

}

else

{

var bolExecuteQuery = true;

}

//Check Sort Mode

if(GetSortSpec(Inputs, strSortField, strSortType) != HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SortSpec", null, "Get"))

{

var bolSortResults = true;

}

else

{

var bolSortResults = false;

}

//Get Query Results

if(bolExecuteQuery)

{

//Execute New Query

if (ExecuteMyFind(Inputs, Outputs))

{

if(CheckError(Outputs))

{

var ErrorStatus = Outputs.GetChild(0).GetProperty("Status");

if (ErrorStatus > 0 &&  GetParameter(Inputs.GetProperty("Parameters"),"ShowError")== "TRUE")

{

var ErrorText = Outputs.GetChild(0).GetProperty("Error Text");

TheApplication().RaiseErrorText(ErrorText);

}

return(false);

}

else

{

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", Inputs.GetProperty(this.GetProperty("Business Component Search String Input Property")), "Add");

}

}

}

else

{

//Return Cached Results

var oCachedResults = HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "Query", null, "Get");

for(var i = 0; i < oCachedResults.GetChildCount(); i++)

{

Outputs.AddChild(oCachedResults.GetChild(i));

}

oCachedResults = null;

}

//Sort Property Set

if(bolExecuteQuery || bolSortResults)

{

strSortField = "mField2";

strSortType = "Ascending";

SortPropertySet(Outputs, strSortField, strSortType, Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")));

}

//Update Cache

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), Outputs.Copy(), "Query", null, "Add");

return(true);

}

else

{

//Reset Search & Sort Cache

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SearchExpr", null, "Remove");

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "SortSpec", null, "Remove");

HandleCache(Inputs.GetProperty(this.GetProperty("Business Component Name Input Property")), null, "LinkFieldValue", strNewLinkFieldValue, "Add");

return(true);

}

}

function RightTrim(strStringToTrim)

{

var strTrimChars = new String(" \t\n\r");

var strString = new String(strStringToTrim);

if (strTrimChars.indexOf(strString.charAt(strString.length – 1)) != -1)

{

var i = strString.length – 1;

while (i >= 0 && strTrimChars.indexOf(strString.charAt(i)) != -1)

i–;

strString = strString.substring(0, i + 1);

}

var strTrimChars = null;

var i = null;

return(strString);

}

function SortPropertySet(oPropertySet, strSortField, strSortType, strBusCompName)

{

if(typeof(oPropertySet) == "object" && strSortField != null && strSortType != null)

{

var strFieldType = GetFieldType(strBusCompName, strSortField);

for(var i = 0; i < oPropertySet.GetChildCount() – 1; i++)

{

var strCurrentValue = oPropertySet.GetChild(i).GetProperty(strSortField);

var intCurrentIdx = i;

//Find smallest or highest value

//******************************

for(var j = i + 1; j < oPropertySet.GetChildCount(); j++)

{

if(strFieldType == "number" && !isNaN(parseInt(oPropertySet.GetChild(j).GetProperty(strSortField))) && !isNaN(parseInt(strCurrentValue)))

{

if(strSortType.toLowerCase() == "ascending")

{

if(parseInt(oPropertySet.GetChild(j).GetProperty(strSortField)) < parseInt(strCurrentValue))

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

else

{

if(parseInt(oPropertySet.GetChild(j).GetProperty(strSortField)) > parseInt(strCurrentValue))

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

}

else if(strFieldType == "date")

{

var oRecordDate = new Date(oPropertySet.GetChild(j).GetProperty(strSortField));

var oCurrentDate = new Date(strCurrentValue);

if(typeof(oRecordDate) == "object" && typeof(oCurrentDate) == "object")

{

if(strSortType.toLowerCase() == "ascending")

{

if(parseInt(oRecordDate.getTime()) < parseInt(oCurrentDate.getTime()))

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

else

{

if(parseInt(oRecordDate.getTime()) > parseInt(oCurrentDate.getTime()))

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

}

}

else

{

if(strSortType.toLowerCase() == "ascending")

{

if(oPropertySet.GetChild(j).GetProperty(strSortField).toString() < strCurrentValue.toString())

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

else

{

if(oPropertySet.GetChild(j).GetProperty(strSortField).toString() > strCurrentValue.toString())

{

strCurrentValue = oPropertySet.GetChild(j).GetProperty(strSortField);

intCurrentIdx = j;

}

}

}

}

//Swap Records

//************

if(intCurrentIdx != i)

{

var oChild = oPropertySet.GetChild(intCurrentIdx);

oPropertySet.RemoveChild(intCurrentIdx);

oPropertySet.InsertChildAt(oChild, i);

var oChild = oPropertySet.GetChild(i + 1);

oPropertySet.RemoveChild(i + 1);

oPropertySet.InsertChildAt(oChild, intCurrentIdx);

}

}

HandleCache(strBusCompName, null, "SortSpec", strSortField + " (" + strSortType + ")", "Add");

}

else if(strSortField == null || strSortType == null)

{

HandleCache(strBusCompName, null, "SortSpec", null, "Remove");

}

return(true);

}

function Trim(strStringToTrim)

{

return(RightTrim(LeftTrim(strStringToTrim)));

}

Hope that helps you. King regards.

Daniel Rodriguez

Siebel Senior Consultant at IBM

2 comments on “How to sort a VBC using eScript”

  1. Aman Reply

    I think , this can also be achieved just by using clib.qsort function where VBC records can be stored in a array and complete array can be pass to this function which will give sorted data as output.

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!