I am in the process of rewriting the Automatic XI 3 Inbox Purge Program Object utility in Java, but realized it only considers report age as the delete criteria, where we need to consider report count.
Is there a combined query that could provide a listing of objects in all Inboxes, excluding the most recent “X” we are going to allow each user?
If anyone is interested in the direct replacement for the Automatic XI 3 Inbox Purge Program Object utility in Java let me know, it seems to be working fine for me, but the normal disclaimers would apply…
Excluding the most recent “X” requires a sub query or a SQL window function, both of which are unsupported. I think you have to return all objects per mailbox and iterate to determine the most recent ones you want to keep.
I ended up using a 2nd Query as suggested above.
For those interested, here is the most recent version of the code, I am sure it can still be cleaned up, but it seems to be working as-is.
One thing to note, the lines to delete each object and commit the recordset back to the infostore are commented out to protect the innocent.
Is this a worthy BOB Apps Candidate?
/*
* This utility is designed to purge the user Inboxes to keep them to a reasonable size
*/
package development;
import java.util.Date;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.properties.IProperties;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
public class PurgeBOBJInboxes {
public static void main(String[] args) {
String strBOBJUser = "JasonGardieff";
String strBOBJPass = "JuansMom";
String strBOBJCMS = "mahserver:6400";
String strBOBJAuth = "secEnterprise";
String queryString = null;
int intInboxLimit = 60;
String strSQL = null;
try {
IEnterpriseSession eSession = CrystalEnterprise.getSessionMgr().logon(strBOBJUser, strBOBJPass, strBOBJCMS, strBOBJAuth);
IInfoStore iStore = (IInfoStore) eSession.getService("InfoStore");
System.out.println("Logged in");
//Fetch all the users with more than intInboxLimit items in the inbox
queryString = ("Select SI_ID, SI_NAME, SI_CHILDREN from CI_InfoObjects where SI_KIND = 'InBox' and SI_Children > " + intInboxLimit);
System.out.println("queryString: " + queryString);
IInfoObjects rsInboxes = iStore.query(queryString);
System.out.println(rsInboxes.size());
if (rsInboxes.size()>0){
for ( int i =0; i < rsInboxes.size(); i++){
IInfoObject thisinbox = null;
thisinbox = (IInfoObject) rsInboxes.get(i);
IProperties inbox = thisinbox.properties();
System.out.println("Purging Inbox for User: " +
inbox.getProperty("SI_NAME").getValue() + "(" + //Report Name
inbox.getProperty("SI_ID").getValue() + ")" //Instance SI_ID
);
try {
/*
* The query below has restrictions on both SI_INSTANCE and SI_KIND to reduce
* the risk of us inadvertently deleting something critical (users, groups, servers, plug-ins)
*/
System.out.println(" Querying for the inbox objects for " + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
strSQL = ("Select SI_ID, SI_KIND from CI_InfoObjects where SI_INSTANCE=0 and SI_KIND in ('Shortcut','Webi','Excel','FullClient','Pdf') and SI_PARENTID = " + inbox.getProperty("SI_ID").getValue() + " ORDER BY SI_CREATION_TIME DESC");
System.out.println(" queryString: " + strSQL);
IInfoObjects objRS2 = iStore.query(strSQL);
System.out.println(" Inbox Object Count: " + objRS2.size());
if (objRS2.size()>intInboxLimit){
for ( int x = intInboxLimit; x < objRS2.size(); x++){
IInfoObject inboxObject = null;
inboxObject = (IInfoObject) objRS2.get(x);
try {
System.out.println(" This is where we delete object " + inboxObject);
//Delete the object from the InfoObjects collection
//objRS2.delete(inboxObject);
System.out.println(" Record Deleted");
}catch (Exception error){
System.err.println(new Date() + " Error while deleting records and commiting the recordset back to the InfoStore");
System.err.println("/t" + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
System.err.println(error.getMessage());
}
}
//Commit the changes to the CMS using the commit method. This deletes the object.
System.out.println(" Updating InfoStore");
//iStore.commit(objRS2);
}
}catch (Exception error) {
System.err.println(new Date() + " Error while querying for inbox items ");
System.err.println("/t" + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
System.err.println(error.getMessage());
}
}
}
eSession.logoff();
}catch (Exception error) {
System.err.println(new Date() + " \n Business Objects Authentication/Query Failed");
System.err.println(error.getMessage());
}
}
}
(BOB member since 2008-10-06)