Need to query the WTDocument from the particular Organization

[NG_9451104]

Need to query the WTDocument from the particular Organization, from WTDocument.class not from WTDocumentMaster.class.

If I use WTDoumentMaster.class this is working but I need for WTDocument.class

I've tried the code But having some issues, kindly let us know if any suggestions.

exampe:

Class Doc = WTDocument.class;
Class ORG= WTOrganization.class;
QuerySpec qs = new QuerySpec();
int DOC_idx = qs.addClassList(Doc , true);
int ORG_idx1 = qs.addClassList(ORG, true);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

java.util.Date d1 = format.parse(screatedDateFrom);
java.util.Date d2 = format.parse(screatedDateTo);

Date dF = new Date(d1.getTime());
Date dT = new Date(d2.getTime());

Timestamp tsFrom = new Timestamp(d1.getTime());
tsFrom.setHours(23);
Timestamp tsTo = new Timestamp(d2.getTime());
tsTo.setHours(23);

SearchCondition sc0 = new SearchCondition(ORG, "name", SearchCondition.EQUAL, "ORGNAME1");
SearchCondition sc00 = new SearchCondition(Doc, "organizationReference.key.id", qc1, "thePersistInfo.theObjectIdentifier.id");
SearchCondition sc1 = new SearchCondition(Doc,"thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL , tsFrom);
SearchCondition sc2 = new SearchCondition(Doc,"thePersistInfo.createStamp", SearchCondition.LESS_THAN_OR_EQUAL, tsTo);
qs.appendOpenParen();
qs.appendWhere(sc0, new int []{ORG_idx1});
qs.appendCloseParen();
qs.appendAnd();
qs.appendWhere(sc00, new int []{DOC_idx, ORG_idx1});
qs.appendAnd();
qs.appendWhere(sc1, new int[] { DOC_idx });
qs.appendAnd();
qs.appendWhere(sc2, new int[] { DCO_idx });

QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);

WTDocument Document = null;

while (qr.hasMoreElements())
{
Persistable[] ar = (Persistable[]) qr.nextElement();
Document = (WTDocument) ar[idx];

doclist.add(Document);
}

[HelesicPetr]

Hello @NG_9451104 

build a Query Specification is tricky.

Sometimes you have to use a some workaround to get you really need.

You have to define a relation between tables. In this case it is from WDocument to WTDocumentMaster and then to WTOrganization

As I know there are some limitations if you want to define direct Column names you should define aliases to get right column information from database

(or error is presented Example: Attribute "masterReference" is not a member of class "class wt.doc.WTDocument")

Following query spec should work for you.

QuerySpec queryspec;
queryspec = new QuerySpec();
queryspec.setAdvancedQueryEnabled(true);
int indexObjectWTD = queryspec.appendClassList(WTDocument.class, true);
int indexObjectMASTR = queryspec.appendClassList(WTDocumentMaster.class, true);
int indexObjectORG = queryspec.appendClassList(WTOrganization.class, true);

String orgName = "AVENG";

//alisID (A0,A1,A2 ... atc)
String[] aliases = new String[3];
aliases[0] = queryspec.getFromClause().getAliasAt(indexObjectWTD); // alias for WTDOCUMENT
aliases[1] = queryspec.getFromClause().getAliasAt(indexObjectMASTR); // ALIAS for MASTER WTDOcument
aliases[2] = queryspec.getFromClause().getAliasAt(indexObjectORG); // ALIAS for ORG

//relation settings
CompositeWhereExpression andExpression = new CompositeWhereExpression(LogicalOperator.AND);
// (A0.idA3masterReference=A1.idA2A2) and (A1.idA3organizationReference=A2.idA2A2)
andExpression.append(new SearchCondition(new TableColumn(aliases[0], "idA3masterReference"), "=", new TableColumn(aliases[1], "idA2A2")));
andExpression.append(new SearchCondition(new TableColumn(aliases[1], "idA3organizationReference"), "=", new TableColumn(aliases[2], "idA2A2")));
queryspec.appendWhere(andExpression,new int[]{indexObjectWTD,indexObjectMASTR,indexObjectORG});

SearchCondition sc0 = new SearchCondition(WTOrganization.class, "name", SearchCondition.EQUAL, orgName); // HERE is a NAME of your ORGANIZATION

SearchCondition sc1 = new SearchCondition(WTDocument.class,"thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL , newTimeStamp); // PUT YOUR TIME STAMP HERE 
SearchCondition sc2 = new SearchCondition(WTDocument.class,"thePersistInfo.createStamp", SearchCondition.LESS_THAN_OR_EQUAL, newTimeStamp2); // PUT YOUR TIME STAMP HERE 

queryspec.appendAnd();
queryspec.appendWhere(sc0, new int[]{indexObjectORG});

queryspec.appendAnd();
queryspec.appendWhere(sc1, new int[]{indexObjectWTD});

queryspec.appendAnd();
queryspec.appendWhere(sc2, new int[]{indexObjectWTD});

QueryResult queryRes = PersistenceServerHelper.manager.query(queryspec);

Hope this can help

PetrH

[NG_9451104]

Hi PetrH,

Thanks for sharing this let me check this and let you know the result.

Once again thanks.

Thanks and Regards,

Naveen Infant George

[HelesicPetr]

Hi @NG_9451104 

Does it help?

PetrH

[NG_9451104]

Hi PetrH,

Sorry for the delay I'm on vacation till 22nd May, I'll update you on 23rd May, 

Thanks,

Naveen Infant George 

[NG_9451104]

Hi

Sorry for the delay,

I've tried your suggestion but I can't get the objects count, for the same date if I search in UI there is an object available. Maybe I missed something. and let you know

[HelesicPetr]

Hi @NG_9451104 

Try to use Like for OrgName instead of equal.

PetrH

[HelesicPetr]

Hi @NG_9451104 

I guess the time format could case wrong results.

How do you create Timestamp object?

I see an output with just date not time.

If I use following definition for timestamp it gives me correct results. (all documents :D)

Timestamp newTimeStamp = new Timestamp(Long.parseLong("0"));
Timestamp newTimeStamp2 = new Timestamp(System.currentTimeMillis());

PetrH

[NG_9451104]

Hi HelesicPetr,

Sorry for the delay just stuck in some other projects now i have tried these lines but it is not works for me maybe i missed something, 

Now I tried the same using webject and it is now working fine, Thanks for the support and once again sorry for the delay.

<ie:webject name="Query-Objects" type="OBJ">
<ie:param name="INSTANCE" data="$(properties[0]wt.federation.ie.VMName[0])" />
<ie:param name="TYPE" data="wt.inf.container.WTContainer" />
<ie:param name="ATTRIBUTE" data="name" />
<ie:param name="WHERE" data="name='<%=getParam("orgname")%>'"/>
<ie:param name="GROUP_OUT" data="container" />
</ie:webject>

<ie:webject name="Query-Objects" type="OBJ">
<ie:param name="INSTANCE" data="$(properties[0]wt.federation.ie.VMName[0])"/>
<ie:param name="type" data="wt.doc.WTDocument"/>
<ie:param name="ATTRIBUTE_TYPE_CONTEXT" data="wt.fc.Persistable"/>
<ie:param name="CONTAINER_REF" data="$(container[]obid[])" />
<ie:param name="where" data="(thePersistInfo.modifyStamp>='2021-04-01')&amp;(thePersistInfo.modifyStamp<='2021-08-30')"/>
<ie:param name="where" data="(creator.name='workwolk')" delim=","/>
<ie:param name="attribute" data="number,name,version,lifeCycleState,creator.name,thePersistInfo.modifyStamp" delim=","/>
<ie:param name="group_out" data="All-Versions"/>
</ie:webject>

PREVIEW