So I have the following code which is a query that should return me a result of WTDocuments with an identical iba value to an iba value in part.
But I’m not getting any results thought the sql query that the statementspec calls returns a result. Can you help me understand what I am doing wrong and also what is a columnaccesscontrolitembuilder and how do I remove it.
SQL Query from StatementSpec : (yellow I think is the culprit but not sure what that is)
SELECT A0.*,wt.pds.ColumnAccessControlItemBuilder@7e17cd6
FROM wt.doc.WTDocument A0,wt.iba.value.StringValue A1,wt.iba.definition.StringDefinition A2,com.ptc.core.lwc.server.LWCIBAAttDefinition A3
WHERE (A3.idA3A7 = A2.idA2A2) AND (A1.idA3A6 = A2.idA2A2) AND (A1.idA3A4 = A0.idA2A2) AND (A3.name = 'ICS_NUMBER') AND (A1.value IN '1234567') AND (A0.branchIditerationInfo IN (SELECT MAX(A0.branchIditerationInfo) FROM WTDocument A0 WHERE ((A0.idA2A2 = A0.idA2A2))))
SQL query :
SELECT A0.*
FROM WTDocument A0,StringValue A1,StringDefinition A2,LWCIBAAttDefinition A3
WHERE (A3.idA3A7 = A2.idA2A2) AND (A1.idA3A6 = A2.idA2A2) AND (A1.idA3A4 = A0.idA2A2) AND (A3.name = 'ICS_NUMBER') AND (A1.value IN '1234567') AND (A0.branchIditerationInfo IN
(SELECT MAX(A0.branchIditerationInfo) FROM WTDocument A0 WHERE ((A0.idA2A2 = A0.idA2A2))))
CODE :
package ext.itt.audit.configaudit;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.ptc.core.lwc.server.LWCIBAAttDefinition;
import com.ptc.core.lwc.server.LWCNormalizedObject;
import ext.itt.commonutil.CommonUtils;
import ext.itt.manager.ITTExceptionHandler;
import wt.audit.configaudit.AuditEventRecorder;
import wt.doc.WTDocument;
import wt.epm.EPMDocument;
import wt.events.KeyedEvent;
import wt.fc.ObjectReference;
import wt.fc.Persistable;
import wt.fc.PersistenceHelper;
import wt.fc.QueryResult;
import wt.fc.WTObject;
import wt.fc.collections.WTArrayList;
import wt.fc.collections.WTCollection;
import wt.iba.definition.StringDefinition;
import wt.iba.value.StringValue;
import wt.part.WTPart;
import wt.pds.StatementSpec;
import wt.query.ClassAttribute;
import wt.query.QueryException;
import wt.query.QuerySpec;
import wt.query.SearchCondition;
import wt.query.SubSelectExpression;
import wt.util.WTException;
import wt.util.WTPropertyVetoException;
public class SecurityLabelPropagationAuditEventListener implements AuditEventRecorder {
@Override
public void recordMultiObjectEvent(KeyedEvent keyedevent, WTCollection wtcollection) throws WTException {
System.out.println("KeyedEvent: " + keyedevent.getEventType() + " | " + keyedevent.getEventKey());
System.out.println("WTCollection.size() : " + wtcollection.size());
Iterator wtcollection_iterator = wtcollection.iterator();
WTArrayList arrList = new WTArrayList();
while (wtcollection_iterator.hasNext()) {
Persistable p = (Persistable) ((ObjectReference) wtcollection_iterator.next()).getObject();
System.out.println("Persistable Info : " + p.getPersistInfo().getObjectIdentifier());
if (p instanceof WTPart) {
// eligible for pdf stamping
arrList.add((WTPart) p);
} else {
continue;
}
}
if (!arrList.isEmpty()) {
try {
propogateSecurityLabels(arrList);
} catch (WTPropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ITTExceptionHandler e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else {
System.out.println("No persistable found from where can propogate security labels");
}
}
@Override
public void recordEvent(KeyedEvent keyedevent) throws WTException {
System.out.println("KeyedEvent: " + keyedevent.getEventType() + " | " + keyedevent.getEventKey());
}
public void propogateSecurityLabels(WTArrayList docList) throws WTException, WTPropertyVetoException, IOException, ITTExceptionHandler {
//logger.debug("Entering ********propogateSecurityLabels********");
System.out.println("Entering propogateSecurityLabels");
CommonUtils cu = new CommonUtils();
if (docList.size()!=0) {
Iterator iterator = docList.iterator();
while(iterator.hasNext()) {
Persistable perst;
Object object = iterator.next();
if (object instanceof ObjectReference) {
ObjectReference obj_ref = (ObjectReference)object;
perst = obj_ref.getObject();
WTPart part = (WTPart) perst;
LWCNormalizedObject obj = new LWCNormalizedObject(perst, null, java.util.Locale.US, newcom.ptc.core.meta.common.DisplayOperationIdentifier());
/* Get value of IBAName soft attribute */
obj.load("ICS_NUMBER");
String ibaValue = (java.lang.String) obj.get("ICS_NUMBER");
//String ibaValue = cu.getIbaValue("ICS_NUMBER", part);
System.out.println("ibaValue : " + ibaValue);
// get all WTDocuments with an iba of ics and the value that wtpart has
List<WTObject> docs = fetchAllDocumentsWithIBAValue(WTDocument.class, "ICS_NUMBER", ibaValue, false );
System.out.println("doc size: " + docs.size());
// update their security labels
for (WTObject doc : docs) {
new CommonUtils().setSecurityLabels((WTDocument) doc, cu.getSecurityLabels(part) );
}
// get all WTDocuments with an iba of ics and the value that wtpart has
List<WTObject> epmDocs = fetchAllDocumentsWithIBAValue(EPMDocument.class, "ICS_NUMBER", ibaValue, false);
System.out.println("epmdoc size: " + epmDocs.size());
// update their security labels
for (WTObject doc : docs) {
new CommonUtils().setSecurityLabels((EPMDocument) doc, cu.getSecurityLabels(part) );
}
}
}
}
System.out.println("Exiting propogateSecurityLabels");
//logger.debug("Leaving propogateSecurityLabels");
}
/*
* Query Database
* Find all documents with an ICS Number that is linked to WTPart
* SQL Query :
* Select doc.ida2a2, sv.value, lwc.name
FROM WTDocument doc, StringValue sv, StringDefinition sd, LWCIBAAttDefinition lwc
WHERE
doc.latestiterationinfo=1
and
lwc.ida3a7 = sd.ida2a2
and
sv.ida3a6 = sd.ida2a2
and
sv.ida3a4 = doc.ida2a2
and
lwc.name='ICS_NUMBER'
and
sv.value='000466990'
and
doc.branchiditerationinfo in
(select MAX(doc2.branchiditerationinfo) from wtdocument doc2 where doc.ida2a2 = doc2.ida2a2)
*/
public static List<WTObject> fetchAllDocumentsWithIBAValue(Class documentClass, String ibaName, StringvalueToSearchFor, boolean grabLatest) throws WTException {
QuerySpec qs = new QuerySpec(documentClass);
int idxDocument = 0;
int idxStringValue = qs.addClassList(StringValue.class, false);
int idxStringDefinition = qs.addClassList(StringDefinition.class, false);
int idxLWCIBAAttDefinition = qs.addClassList(LWCIBAAttDefinition.class, false);
if (grabLatest) {
qs.appendWhere(new SearchCondition(documentClass, "iterationInfo.latest", SearchCondition.IS_TRUE), newint[] {idxDocument});
qs.appendAnd();
}
qs.appendWhere(new SearchCondition(LWCIBAAttDefinition.class, "ibaReference.key.id", StringDefinition.class,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxLWCIBAAttDefinition, idxStringDefinition});
qs.appendAnd();
qs.appendWhere(new SearchCondition(StringValue.class, "definitionReference.key.id" , StringDefinition.class, "thePersistInfo.theObjectIdentifier.id" ), new int[] {idxStringValue, idxStringDefinition});
qs.appendAnd();
qs.appendWhere(new SearchCondition(StringValue.class, "theIBAHolderReference.key.id", documentClass,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxStringValue, idxDocument});
qs.appendAnd();
qs.appendWhere(new SearchCondition(LWCIBAAttDefinition.class, "name", SearchCondition.EQUAL, ibaName), new int[] {idxLWCIBAAttDefinition});
qs.appendAnd();
qs.appendWhere(new SearchCondition(StringValue.class, "value", SearchCondition.IN, valueToSearchFor), new int[] {idxStringValue});
qs.appendAnd();
qs.appendWhere(new SearchCondition(new ClassAttribute(documentClass, "iterationInfo.branchId"), SearchCondition.IN,
new SubSelectExpression(getMAXBranchID(documentClass, idxDocument))), new int[] {idxDocument});
qs.setAdvancedQueryEnabled(true);
QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);
System.out.println("qs:" + qs.toString());
List<WTObject> documents = new ArrayList<WTObject>();
while (qr.hasMoreElements()) {
Object nextElement = qr.nextElement();
if (nextElement instanceof WTObject) {
documents.add((WTObject) nextElement);
}
}
return documents;
}
// (select MAX(doc2.branchiditerationinfo) from wtdocument doc2 where doc.ida2a2 = doc2.ida2a2)
public static QuerySpec getMAXBranchID(Class documentClass, int indexForSelectDocClass) throws QueryException {
QuerySpec qs2 = new QuerySpec();
int idxDoc2 = qs2.appendClassList(documentClass, false);
qs2.appendSelect(wt.query.SQLFunction.newSQLFunction(wt.query.SQLFunction.MAXIMUM, newClassAttribute(documentClass, "iterationInfo.branchId" )), new int[] {idxDoc2}, false);
qs2.appendWhere(new SearchCondition(documentClass, "thePersistInfo.theObjectIdentifier.id", documentClass,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxDoc2, indexForSelectDocClass});
return qs2;
}
}
