Hi Kaushik,

This is custom PSO code, so in theory you should get support for it through PSO - they're hitting directly the PPM DB, not using proper APIs, so one should expect such trouble eventually.

Regarding your question, if you check in Table DSH_ID_GENERATOR, you should find a single column "NEXT_HI" with a single value - which is basically used as custom sequence in many Dashboard objects.

If the negative returned ID is "-2147483648", it must mean that your maximum value in the DSH_ID_GENERATOR table must have reached the limit of Java Integers, i.e. 2,147,483,647 . Getting the next value will result in looping into the negative numbers. 

Now, how exactly you managed to exhaust the 2 billions available IDs is a bit beyond me: Getting continuously values 24/7 for 10 years, it would have taken issuing 7 new IDs per second to reach that limit - very unlikely. What is most likely is that someone manually jumped the value of this table to a very high value as part of some custom change to avoid conflict, thus accelerating IDs exhaustion - again, not a supported use of the system.

To be honest, I don't really have a solution for you here: You must now have Dashboard IDs spanning all over and you've already reached the possible max - as a result, the only solution would be to use Long instead of Int for the IDs, but that's a massive change in the application with a lot of impact and not something we are likley to implement any time soon. I would advise you to check with PSO in order to see how to address this problem - It could be done with a one time huge script that would "reset" all the Dashboard IDs to some low values (sequential starting from one, as I doubt you have 2 billion dashboard records) and reset the value in DSH_ID_GENERATOR to the next largest value. And most importantly, you must remove whatever custom code may be consistently bumping values of DSH_ID_GENERATOR more than what is needed. All of this is custom work and is not covered by standard PPM support.

Hi Etienne,

I checked the details as below:
select * from DSH_ID_GENERATOR - output 65833

SELECT COUNT(*) FROM DSH_PAGES - output 727881

select COUNT(*) from DSH_USER_PAGES - output 719488

select COUNT(*) from DSH_PAGE_GROUPS - output 91041

For example for one of the tables:

select select MAX(page_group_id) from DSH_PAGE_GROUPS - output 2156363776

This positive ID is getting generated when a user creates a new pae/ group by going to the personalize dashboard page. The page works fine. However, when the user wants this page to be distributed to other user, then he/ she does so by creating the distribute dashboard request which calls this java code. The overall java code has that code i shared.

As per your query the ID in DSH_ID_GENERATOR is 5 digit and quite less. Is it the TableHiLoGenerator() causing the issue?

You haven't shared enough info for me to tell.

For example, I have no idea what is returned when the code calls TableGenerator tg = idGenerators.get(table); , because you didn't show what is idGenerators.

As I said before, this custom code is clearly doing something wrong by calling low level PPM Java API and was written by PSO, so you should check with them for support on it. We do NOT have access to this source code, and we will only provide support on capabilities that are built in PPM, not the ones that were custom-developed.

Also, if you use some PSO code to share dashboard, then you should stop - you can now share Dashboard pages with other users though a supported feature in PPM since PPM 10.0.1.

package DistributeDashboard.Project;

import java.io.IOException;
import java.io.Serializable;
import java.rmi.dgc.VMID;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import java.util.logging.FileHandler;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.dialect.DataDirectOracle9Dialect;
import org.hibernate.id.TableGenerator;
import org.hibernate.id.TableHiLoGenerator;
import org.hibernate.impl.SessionImpl;
import org.hibernate.type.IntegerType;

public final class DashboardPageCopyUtil {
public String listOfUsers = "";
public String pageOrPageGroupName = "";

private HashMap<String, TableGenerator> idGenerators = new HashMap<String, TableGenerator>(
5);

private static Session session = null;
static Logger LOGGER = Logger.getLogger("DashboardPageCopyUtil");
static FileHandler fh = null;

// Added the method to use Java logger (JUL)
static void init_logger() {

System.setProperty("java.util.logging.SimpleFormatter.format",
"[%1$tF %1$tT %1$tL] [%4$-7s] %5$s %n");
SimpleFormatter formatter = new SimpleFormatter();

try {
fh = new FileHandler("DeloitteCustomLogs/DashBoardCopyUtil%g.log", 100000, 2, true);
fh.setFormatter(formatter);
LOGGER.addHandler(fh);
} catch (SecurityException | IOException e1) {
LOGGER.warning("Logfile configuration error, please check that directory DeloitteCustomLogs exists and has right permissions.");
}
}

// Parameterized Constructor to Establish Connectivity to SQL Database
public DashboardPageCopyUtil(String url, String userName, String passWord) {
init(url, userName, passWord);
}

// The init method to Create new DB Session using Hibernate
private void init(String URL, String userName, String passWord) {

// Setup Hibernate Session

try {

SessionFactory mainSF = new Configuration()
.addResource("hibernate.cfg.xml")
.setProperty("hibernate.dialect",
"org.hibernate.dialect.DataDirectOracle9Dialect")
.setProperty("hibernate.connection.driver_class",
"oracle.jdbc.driver.OracleDriver")
.setProperty("hibernate.connection.url", URL)
.setProperty("hibernate.connection.username", userName)

.setProperty("hibernate.connection.password", passWord)
.buildSessionFactory();
session = mainSF.openSession();
LOGGER.info("Session generated");

} catch (Exception e) {
// In case of Exception Log the exception with Stack trace
LOGGER.severe(
"Error in Creating Session in INIT2 method inside DashboardPageCopy class. Error Details as below:-");
LOGGER.severe(e.getMessage());
System.exit(1);
}

}

/**
* Get the next value of TableHilo Generator
*
* @param table
* @return returns next value
*/

// The Given Serializable method returns Unique ID for Page and PageGroup
public Serializable nextValue(String table) {
TableGenerator tg = idGenerators.get(table);

try {
if (tg == null) {
Properties p = new Properties();
p.setProperty("table", table);

tg = new TableHiLoGenerator();
tg.configure(new IntegerType(), p,
new DataDirectOracle9Dialect());

idGenerators.put(table, tg);
}
} catch (Exception e) {

LOGGER.severe("Exception occured in generating Next Value using Table Generator");
LOGGER.severe(e.getMessage());
System.exit(1);
}

return tg.generate((SessionImpl) session, null);
}

/**
* Get the UUID
*
* @return returns new UUID
*/
public String newUUID() {
try {
return new VMID().toString();
} catch (Exception e) {
LOGGER.severe("New UUID not created successfully.");
LOGGER.severe(e.getMessage());

System.exit(1);
return null;
// Get Below Lines Reviewed
// return null;

}

}

/**
* General method to copy record of tables
*
* @param selectSQL
* @param copycat
*/

// Copy Records method Just does a Batch Insert which is Genreral to all
// Insert Statements in DB

/**
* This Method is called when
*
* Select * From Table is Finally used to Insert * Into Table
*
* @throws Exception
*
*/
@SuppressWarnings("deprecation")
private void copyRecords(final String selectSQL,
final FieldValueCopycat copycat) {
try {
PreparedStatement ps = session.connection().prepareStatement(
selectSQL);
ResultSet rs = ps.executeQuery();

String table = "";
Pattern p = Pattern.compile(
"^select\\s+.*\\sfrom\\s+([\\w_]+)\\s+where\\s.*$",
Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(selectSQL);
if (m.matches()) {
table = m.group(1);
}

ResultSetMetaData meta = rs.getMetaData();
StringBuffer sb = new StringBuffer();
sb.append("INSERT INTO " + table + " (");

int columncount = meta.getColumnCount();
StringBuffer params = new StringBuffer();
for (int c = 1; c <= columncount; c++) {
if (c > 1) {
sb.append(",");
params.append(",");
}

sb.append(meta.getColumnLabel(c));
params.append("?");

}

sb.append(") VALUES (");
sb.append(params.toString());
sb.append(")");

PreparedStatement batchInsert = session.connection()
.prepareStatement(sb.toString());
batchInsert.clearBatch();
while (rs.next()) {
batchInsert.clearParameters();
for (int c = 1; c <= columncount; c++) {
String field = meta.getColumnLabel(c);
batchInsert.setObject(c,
copycat.copy(field, rs.getObject(field)));
}
batchInsert.addBatch();
}

batchInsert.executeBatch();
batchInsert.getConnection().commit();
batchInsert.close();
rs.close();
ps.close();
}

catch (Exception e) {

e.printStackTrace();
LOGGER.severe("Exception occured while Copying Records");
LOGGER.severe("Error in method :- copyRecords");
LOGGER.severe(e.getMessage());
System.exit(1);
}
}

/**
* Method Creates new Page Group
*
* @param pageGroupIDOLD
* @param userID
* @param newPageGroupID
* @throws Exception
*/

public void createNewPageGroup(long pageGroupIDOLD, long userID,
long newPageGroupID) {

try {

final long pagegroupID = newPageGroupID;
final long user = userID;

LOGGER.info("COpy Page Group with ID " + pageGroupIDOLD
+ "Called to Replace PAge Group ID WIth " + newPageGroupID);
copyRecords("SELECT * FROM DSH_PAGE_GROUPS WHERE PAGE_GROUP_ID = "
+ pageGroupIDOLD, new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
// We need to Replace the column Values based on Column
// Label as follows
case "PAGE_GROUP_ID":
return pagegroupID;
case "USER_ID":
return user;
case "SEQUENCE":
return getPageOrPageGroupSequence(user);
}

return origin;

}
});

LOGGER.info("New Page Group with ID :- "
+ pagegroupID
+ " Created successdully and associated to User :- "
+ user);

} catch (Exception e) {
e.printStackTrace();
LOGGER.severe("Exception occured while Copying Records");
LOGGER.severe("Error in method :- copyRecords");
LOGGER.severe(e.getMessage());
System.exit(1);
}

}

/**
* Below Method Created New Page Group and Associate copy of all pages
* inside the page group to destination user
*
* @param pageGroupIdOld
* @param userId
* @throws Exception
*/
@SuppressWarnings("deprecation")
public void copyPageGroupToUser(long pageGroupIdOld, long userId) {

final Serializable newPageGroupId = nextValue("DSH_ID_GENERATOR");
final long newPageGroupIdtobeCreated = Long.valueOf(newPageGroupId
.toString());
final long user = userId;

LOGGER.info("New Page Group ID To be Created is :- "
+ newPageGroupIdtobeCreated);
try {
createNewPageGroup(pageGroupIdOld, userId,
newPageGroupIdtobeCreated);

} catch (Exception e) {
e.printStackTrace();
LOGGER.severe("Error in copyPageGroup to User Line 258");
LOGGER.severe(e.getMessage());
System.exit(1);
}

try {
LOGGER.info("Inside CopyPageGroupToUser");
String page_group_sql = "SELECT PAGE_ID FROM DSH_PAGES WHERE PAGE_GROUP_ID =?";
PreparedStatement sgStmt = session.connection().prepareStatement(
page_group_sql);
sgStmt.setInt(1, (int) pageGroupIdOld);

ResultSet sg_PageList = sgStmt.executeQuery();
LOGGER.info("Query to Fetch Pages in a Group is "
+ page_group_sql + "Value is : " + pageGroupIdOld);
while (sg_PageList.next()) {
LOGGER.info("Pages in PageGroup Found");
int sg_page = sg_PageList.getInt(1);
LOGGER.info("Page is :- " + sg_page);
copyPageGroupValuesToUser(sg_page, user,
newPageGroupIdtobeCreated);

} // end of while
sg_PageList.close();
sgStmt.close();
} // end of try block
catch (Exception e) {
e.printStackTrace();
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are security groups");
LOGGER.severe(e.getMessage());
System.exit(1);
}

}

/**
* Method Copy Page to User Called When Page is Pushed to User as Standalone
* Page
*
* @param pageId
* @param userId
*/

@SuppressWarnings("deprecation")
public void copyPageToUser(long pageId, long userId) {

try {

final Serializable newPageId = nextValue("DSH_ID_GENERATOR");
final long user = userId;

// Copy DSH_PAGES
copyRecords("SELECT * FROM DSH_PAGES WHERE PAGE_ID=" + pageId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PAGE_ID":
return newPageId;
case "UUID":
return newUUID();
case "PAGE_SEQUENCE":

return getPageOrPageGroupSequence(user);

}

return origin;

}
});

// Copy DSH_PORTLETS
final List<Serializable> oldPortletIds = new LinkedList<Serializable>();
final List<Serializable> newPortletIds = new LinkedList<Serializable>();
copyRecords("select * from DSH_PORTLETS where page_ID=" + pageId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_ID":
oldPortletIds.add((Serializable) origin);
Serializable newPortletId = nextValue("DSH_ID_GENERATOR");
newPortletIds.add(newPortletId);
return newPortletId;
case "UUID":
return newUUID();
case "PAGE_ID":
return newPageId;
}
return origin;
}
});

// Copy DSH_PORTLET_PREFERENCES
final Iterator<Serializable> newPortletIdItr = newPortletIds
.iterator();
for (final Serializable portletId : oldPortletIds) {
final List<Serializable> oldPortletReferenceIds = new LinkedList<Serializable>();
final List<Serializable> newPortletReferenceIds = new LinkedList<Serializable>();

final Serializable newPortletId = newPortletIdItr.next();
copyRecords(
"SELECT * from DSH_PORTLET_PREFERENCES where portlet_id="
+ portletId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_PREFERENCE_ID":
oldPortletReferenceIds
.add((Serializable) origin);
Serializable newPRId = nextValue("DSH_ID_GENERATOR");
newPortletReferenceIds.add(newPRId);
return newPRId;
case "PORTLET_ID":
return newPortletId;
}
return origin;
}
});

// Copy DSH_PREFERENCE_VALS
Iterator<Serializable> newRef = newPortletReferenceIds
.iterator();
for (Serializable oldPortletRef : oldPortletReferenceIds) {
final Serializable ref = newRef.next();
copyRecords(
"SELECT * from DSH_PREFERENCE_VALS where PORTLET_PREFERENCE_ID="
+ oldPortletRef,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_PREFERENCE_VALUE_ID":
return nextValue("DSH_ID_GENERATOR");
case "PORTLET_PREFERENCE_ID":
return ref;
}
return origin;
}
});
}
}

// Link the new page to user
try {
PreparedStatement ps = session
.connection()
.prepareStatement(
"INSERT INTO DSH_USER_PAGES (USER_PAGE_ID,VERSION,CUSTOMER_ID,PAGE_ID,USER_ID) VALUES (?,?,?,?,?)");
ps.setObject(1, nextValue("DSH_ID_GENERATOR"));
ps.setObject(2, 0);
ps.setObject(3, "default");
ps.setObject(4, newPageId);
ps.setObject(5, userId);
ps.executeUpdate();
session.connection().commit();
ps.close();
LOGGER.info("New Page ID created is " + newPageId);

} catch (Exception e) {

LOGGER.severe("Exception occured while inserting into DSH_USER_PAGES");
LOGGER.severe("Error in method :- copyPageToUser");
LOGGER.severe(e.getMessage());
System.exit(1);

}

// Since we need to only Copy as Single Page we will remove
// Page_Group References from User and set PAGE_GROUP_ID as null

try {
PreparedStatement ps_update = session
.connection()
.prepareStatement(
"UPDATE DSH_PAGES SET page_group_id = '' where PAGE_ID = (?)");
ps_update.setObject(1, newPageId);
ps_update.executeUpdate();
session.connection().commit();
ps_update.close();
LOGGER.info("Updated PageGroupID to null where Page_ID is : "
+ newPageId);

} catch (Exception e) {
e.printStackTrace();
LOGGER.severe("Error when Updating Page Group ID to null");
LOGGER.severe(e.getMessage());
System.exit(1);
}

} catch (Exception e) {
LOGGER.severe("Exception Occured with Details as :- " + e);
System.exit(1);
}
}

/**
* The helper class to specify how to copy the original value
*/
public interface FieldValueCopycat {
public Object copy(String field, Object origin);
}

/**
* The Method to identify new PageGroup Sequence of User
*
* @param userId
* @return
*/

/**
* The Method to identify new Page Sequence of User where Page is not part
* of Page Group
*
* @param userId
* @return
*/
public int getPageOrPageGroupSequence(long userId) {
try {
int page_sequence = 0;

String getSequenceSql = "select max(sq) from (SELECT dpg.sequence sq FROM dsh_page_groups dpg where dpg.user_id = ? UNION SELECT dp.page_sequence sq FROM dsh_pages dp,dsh_user_pages dup WHERE dp.page_id = dup.page_id AND dup.user_id = ? AND dp.page_group_id IS NULL AND dp.module_id IS NULL)";

@SuppressWarnings("deprecation")
PreparedStatement sgStmt = session.connection().prepareStatement(
getSequenceSql);
sgStmt.setLong(1, userId);
sgStmt.setLong(2, userId);
ResultSet page_sequence_rslt = sgStmt.executeQuery();

while (page_sequence_rslt.next()) {
page_sequence = page_sequence_rslt.getInt(1) + 1;
} // end of while
// LOGGER.info("Max Page Sequence for user: " + userId + " = " +
// page_sequence);
page_sequence_rslt.close();
sgStmt.close();
return page_sequence;
} // end of try block

catch (SQLException sqle) {
LOGGER.severe("Error while getting page Sequence. SQL Error occured");
LOGGER.severe(sqle.getMessage());
System.exit(1);
return 0;

} catch (Exception e) {
LOGGER.severe("Error while getting page Sequence");
LOGGER.severe(e.getMessage());
System.exit(1);
return 0;

} // end of catch
}

// To Test connection to DB We can remove Following Comments
/**
* @SuppressWarnings("deprecation") public void test_Connection(){ String
* test =
* "SELECT PROJECT_NAME FROM PM_PROJECTS ";
* PreparedStatement sgStmt; try { sgStmt =
* session.connection()
* .prepareStatement(test); ResultSet
* test_result = sgStmt.executeQuery();
*
* while (test_result.next()){
* LOGGER.info
* (test_result.getString(1)); } } catch
* (HibernateException e) {
*
* e.printStackTrace(); } catch
* (SQLException e) { /
* e.printStackTrace(); }
*
* }
*/

/**
* Method Creates Copy of Pages inside Page Group and associate to user Also
* Associate to Page Group Created by preceding method to associate new page
* Group to User
*
* @param pageId
* @param userId
* @param PageGroupId
*/
@SuppressWarnings("deprecation")
public void copyPageGroupValuesToUser(long pageId, long userId,
long PageGroupId) {

try {

final Serializable newPageId = nextValue("DSH_ID_GENERATOR");
final long pageGroupIDNew = PageGroupId;

// Copy DSH_PAGES
copyRecords("SELECT * FROM DSH_PAGES WHERE PAGE_ID=" + pageId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PAGE_ID":
return newPageId;
case "PAGE_GROUP_ID":
return pageGroupIDNew;
case "UUID":
return newUUID();
}

return origin;

}
});

// Copy DSH_PORTLETS
final List<Serializable> oldPortletIds = new LinkedList<Serializable>();
final List<Serializable> newPortletIds = new LinkedList<Serializable>();
copyRecords("select * from DSH_PORTLETS where page_ID=" + pageId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_ID":
oldPortletIds.add((Serializable) origin);
Serializable newPortletId = nextValue("DSH_ID_GENERATOR");
newPortletIds.add(newPortletId);
return newPortletId;
case "UUID":
return newUUID();
case "PAGE_ID":
return newPageId;
}
return origin;
}
});

// Copy DSH_PORTLET_PREFERENCES
final Iterator<Serializable> newPortletIdItr = newPortletIds
.iterator();
for (final Serializable portletId : oldPortletIds) {
final List<Serializable> oldPortletReferenceIds = new LinkedList<Serializable>();
final List<Serializable> newPortletReferenceIds = new LinkedList<Serializable>();

final Serializable newPortletId = newPortletIdItr.next();
copyRecords(
"SELECT * from DSH_PORTLET_PREFERENCES where portlet_id="
+ portletId,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_PREFERENCE_ID":
oldPortletReferenceIds
.add((Serializable) origin);
Serializable newPRId = nextValue("DSH_ID_GENERATOR");
newPortletReferenceIds.add(newPRId);
return newPRId;
case "PORTLET_ID":
return newPortletId;
}
return origin;
}
});

// Copy DSH_PREFERENCE_VALS
Iterator<Serializable> newRef = newPortletReferenceIds
.iterator();
for (Serializable oldPortletRef : oldPortletReferenceIds) {
final Serializable ref = newRef.next();
copyRecords(
"SELECT * from DSH_PREFERENCE_VALS where PORTLET_PREFERENCE_ID="
+ oldPortletRef,
new FieldValueCopycat() {
@Override
public Object copy(String field, Object origin) {
switch (field.toUpperCase()) {
case "PORTLET_PREFERENCE_VALUE_ID":
return nextValue("DSH_ID_GENERATOR");
case "PORTLET_PREFERENCE_ID":
return ref;
}
return origin;
}
});
}
}

// Link the new page to user
try {
PreparedStatement ps = session
.connection()
.prepareStatement(
"INSERT INTO DSH_USER_PAGES (USER_PAGE_ID,VERSION,CUSTOMER_ID,PAGE_ID,USER_ID) VALUES (?,?,?,?,?)");
ps.setObject(1, nextValue("DSH_ID_GENERATOR"));
ps.setObject(2, 0);
ps.setObject(3, "default");
ps.setObject(4, newPageId);
ps.setObject(5, userId);
ps.executeUpdate();
session.connection().commit();
ps.close();
LOGGER.info("New Page ID created is " + newPageId);

} catch (Exception e) {

LOGGER.severe("Exception occured while inserting into DSH_USER_PAGES");
LOGGER.severe("Error in method :- copyPageToUser");
LOGGER.severe(e.getMessage());
System.exit(1);

}

// Since we need to only Copy as Single Page we will remove
// Page_Group References from User and set PAGE_GROUP_ID as null

} catch (Exception e) {
LOGGER.severe("Exception Occured. Details are as :- " + e);
System.exit(1);
}
}

/**
* The below comments can be Removed in Order to Update Pages with Column
* Label As PAGE_GROUP_ID To any custom value
*
* @SuppressWarnings("deprecation") public void
* updatePageTablewithNewPageGroup ( long
* PageGroupId, long newPageId){
*
* try { PreparedStatement ps_update =
* session .connection() .prepareStatement(
* "UPDATE DSH_PAGES SET page_group_id = "
* + PageGroupId +
* " where PAGE_ID = (?)");
* ps_update.setObject(1,newPageId);
* ps_update.executeUpdate();
* session.connection().commit();
* ps_update.close();
* LOGGER.info("Updated PageGroupID to :- "
* + PageGroupId + " where Page_ID is : " +
* newPageId);
* LOGGER.severe("Updated PageGroupID to :- "
* + PageGroupId + " where Page_ID is : " +
* newPageId); } catch (Exception e) {
* e.printStackTrace(); } }
**/

// Recipient_Type, whatToCopy , countProjectsInPageFilter,
// PageorPageGroupId, UserIdListOrSecGroupID

@SuppressWarnings({ "resource", "deprecation" })
public void copyPageorPageGroupToUserOrGroups(String recipient_type,
String whatToCopy, long countProjectsInPageFilter,
long PageorPageGroupId, String UserIdListOrSecGroupID,
int UserLoggedInID) {

try {

/**
* Comparison of Scenario where which case to be called
*/

/**
* Case When we are trying to copy a page to Multiple Users where
* any Number of Projects in Page Filter
*/

if (recipient_type.equalsIgnoreCase("users")
&& whatToCopy.equalsIgnoreCase("page")) {
listOfUsers = UserIdListOrSecGroupID;
LOGGER.info("Method to copy page to User is called");
Scanner userList = new Scanner(UserIdListOrSecGroupID)
.useDelimiter(",");
while (userList.hasNext()) {
long user = Long.parseLong(userList.next());
try {
LOGGER.info("Copying Page to User :- " + user);
copyPageToUser(PageorPageGroupId, user);
} catch (Exception e) {
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are Users");
LOGGER.severe(e.getMessage());
System.exit(1);
}

} // end of while
userList.close();

} // end of if

/**
* Case When we are trying to copy a page to Security Group where
* Zero or 1 of Projects (i.e, Project Count in Page Filter) in Page
* Filter
*/

/**
* Logic Behind this is to Fetch All Users in Security Group For
* each User call CopyPage to User
*/

else if (recipient_type.equalsIgnoreCase("security_groups")
&& whatToCopy.equalsIgnoreCase("page")
&& (countProjectsInPageFilter < 2)) {
try {
String securityGroupIds = UserIdListOrSecGroupID;
String commaSeparatedIds = securityGroupIds.replaceAll(
"#@#", ", ");

try {
LOGGER.info(
"Project count is 0 or 1 and hence finding all user from Security Group except system logged in user");
String security_group_sql = "SELECT DISTINCT KU.USER_ID FROM KNTA_USERS KU, KNTA_USER_SECURITY KUS WHERE KU.USER_ID=KUS.USER_ID AND (KU.END_DATE IS NULL OR END_DATE > sysdate) AND KUS.SECURITY_GROUP_ID IN ( "
+ commaSeparatedIds
+ " ) AND KUS.USER_ID != "
+ UserLoggedInID;
PreparedStatement sgStmt = session.connection()
.prepareStatement(security_group_sql);
ResultSet sg_userList = sgStmt.executeQuery();

while (sg_userList.next()) {
int sg_user = sg_userList.getInt(1);
String usertoNotify = String.valueOf(sg_user);
listOfUsers = listOfUsers.concat(usertoNotify);
listOfUsers = listOfUsers.concat("#@#");
LOGGER.info("Copying Page with Page ID "
+ PageorPageGroupId + "To User " + sg_user);
copyPageToUser(PageorPageGroupId, sg_user);
} // end of while
sg_userList.close();
sgStmt.close();
} // end of try block
catch (Exception e) {
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are security groups");
LOGGER.severe(e.getMessage());
System.exit(1);
} // end of catch

} catch (Exception e) {
LOGGER.info("Exception occured when pushing page to Security Group when Project count <2");
LOGGER.severe(e.getMessage());
System.exit(1);
}
}
// End of Else If

/**
* Case When we are trying to copy a page to Security Group where
* more than 1 of Projects (i.e, Project Count in Page Filter) in
* Page Filter
*/

/**
* Logic Behind this is to Fetch All common Users part of Pages This
* can be achieved by Calling a DB Package
*
* For each User call CopyPage to User
*/

else if (recipient_type.equals("security_groups")
&& (countProjectsInPageFilter > 1)
&& whatToCopy.equalsIgnoreCase("PAGE")) {

LOGGER.info("Common Users will be copied as there are many Projects in Project Filter");
LOGGER.info("Current user who created the Request is :- "
+ UserLoggedInID);

try {

String commonUser = "select USR_ID from table (DELOITTE_DISTRIBUTE_DASHBOARD.DC_GET_USR_LIST(?, ?))";
PreparedStatement sgStmt = session.connection()
.prepareStatement(commonUser);
sgStmt.setInt(1, (int) PageorPageGroupId);
sgStmt.setInt(2, UserLoggedInID);

ResultSet commonUserList = sgStmt.executeQuery();
while (commonUserList.next()) {
int sg_user = commonUserList.getInt(1);
String usertoNotify = String.valueOf(sg_user);
listOfUsers = listOfUsers.concat(usertoNotify);
listOfUsers = listOfUsers.concat("#@#");
LOGGER.info("Copying Page to User :- " + sg_user);
copyPageToUser(PageorPageGroupId, sg_user);
} // end of while
commonUserList.close();
sgStmt.close();
} // end of try block
catch (Exception e) {
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are security groups");
LOGGER.severe(e.getMessage());
System.exit(1);
} // end of catch
} // End of Else IF

/**
* Case When we are trying to copy a page Group to User List of
* Projects in Page Filter is not relevant
*/

/**
* Logic Behind this is to Fetch All Users in passed as token For
* each User call CopyPageGroup to User
*/

else if (recipient_type.equalsIgnoreCase("users")
&& whatToCopy.equalsIgnoreCase("PAGEGROUP")) {
listOfUsers = UserIdListOrSecGroupID;
LOGGER.info("Page Group is to be Copied to Users");
LOGGER.info("Current user who created the Request is :- "
+ UserLoggedInID);
try {

Scanner userList = new Scanner(UserIdListOrSecGroupID)
.useDelimiter(",");
while (userList.hasNext()) {
long user = Long.parseLong(userList.next());
try {
LOGGER.info("Copying Page Group to User :- "
+ user);
copyPageGroupToUser(PageorPageGroupId, user);
} catch (Exception e) {
e.printStackTrace();
LOGGER.severe(e.getMessage());
System.exit(1);
}
}
} catch (Exception e) {
e.printStackTrace();
LOGGER.severe(e.getMessage());
System.exit(1);
} // End of ELSE IF
}

/**
* Case When we are trying to copy a page Group to Security Group
* where Zero or 1 of Projects (i.e, Project Count in Page Filter)
* in Page Filter
*/

/**
* Logic Behind this is to Fetch All Users in Security Group For
* each User call CopyPageGroup to User
*/

else if (recipient_type.equalsIgnoreCase("security_groups")
&& whatToCopy.equalsIgnoreCase("PAGEGROUP")
&& (countProjectsInPageFilter < 2)) {
try {

String securityGroupIds = UserIdListOrSecGroupID;
String commaSeparatedIds = securityGroupIds.replaceAll(
"#@#", ", ");

try {
LOGGER.info(
"Project count is 0 or 1 and hence finding all user from Security Group except system logged in user and Pushing the PageGroup to this List of User");
String security_group_sql = "SELECT DISTINCT KU.USER_ID FROM KNTA_USERS KU, KNTA_USER_SECURITY KUS WHERE KU.USER_ID=KUS.USER_ID AND (KU.END_DATE IS NULL OR END_DATE > sysdate) AND KUS.SECURITY_GROUP_ID IN ( "
+ commaSeparatedIds
+ " ) AND KUS.USER_ID != "
+ UserLoggedInID;
PreparedStatement sgStmt = session.connection()
.prepareStatement(security_group_sql);

ResultSet sg_userList = sgStmt.executeQuery();

while (sg_userList.next()) {
int sg_user = sg_userList.getInt(1);
String usertoNotify = String.valueOf(sg_user);
listOfUsers = listOfUsers.concat(usertoNotify);
listOfUsers = listOfUsers.concat("#@#");
LOGGER.info("Copying Page with Page ID "
+ PageorPageGroupId + "To User " + sg_user);
copyPageGroupToUser(PageorPageGroupId, sg_user);

} // end of while
sg_userList.close();
sgStmt.close();
} // end of try block
catch (Exception e) {
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are security groups and we are pushing Page Group");
LOGGER.severe(e.getMessage());
System.exit(1);
} // end of catch

} catch (Exception e) {
LOGGER.severe(e.getMessage());
LOGGER.info("Error when pushing page group to Security Group");
System.exit(1);
}

} // END OF ELSE IF

/**
* Case When we are trying to copy a page Group to Security Group
* where more than 1 of Projects (i.e, Project Count in Page Filter)
* in Page Filter
*/

/**
* Logic Behind this is to Fetch All common Users part of Page
* Groups This can be achieved by Calling a DB Package
*
* For each User call CopyPageGroup to User
*/
else if (recipient_type.equals("security_groups")
&& (countProjectsInPageFilter > 1)
&& whatToCopy.equalsIgnoreCase("PAGEGROUP")) {

LOGGER.info(
"Common Users will be copied as there are many Projects in Project Filter and Pushing PageGroup to Users");
LOGGER.info("Current user who created the Request is :- "
+ UserLoggedInID);

try {

String commonUser = "select USR_ID from table (DELOITTE_DISTRIBUTE_DASHBOARD.DC_GET_USR_LIST(?, ?))";
PreparedStatement sgStmt = session.connection()
.prepareStatement(commonUser);

sgStmt.setInt(1, (int) PageorPageGroupId);
sgStmt.setInt(2, UserLoggedInID);

ResultSet commonUserList = sgStmt.executeQuery();
while (commonUserList.next()) {
int sg_user = commonUserList.getInt(1);
String usertoNotify = String.valueOf(sg_user);
listOfUsers = listOfUsers.concat(usertoNotify);
listOfUsers = listOfUsers.concat("#@#");
LOGGER.info("Copying Page to User :- " + sg_user);
copyPageGroupToUser(PageorPageGroupId, sg_user);
} // end of while
commonUserList.close();
sgStmt.close();
} // end of try block
catch (Exception e) {
LOGGER.severe("Error occured in method copyPageToUserOrGroups. This error is specific when ");
LOGGER.severe("receipient type are security groups");
LOGGER.severe(e.getMessage());
System.exit(1);
} // end of catch
} // End of Else IF

} // end of try
catch (Exception e) {
LOGGER.severe("Exception :- " + e);
System.exit(1);
}
}

public static void main(String[] argv) {
init_logger();
LOGGER.info("Lets Start");

try {

if (argv.length < 3) {
LOGGER.info("LESS ARGUMENT PASSED");
LOGGER.severe("Incorrect length of argument passed to main Method");
LOGGER.severe("Usage: java DashboardPageCopyUtil <recipient_type> <page_id> <user_id>");
LOGGER.info("Usage: java DashboardPageCopyUtil <recipient_type> <page_id> <user_id>");
System.exit(1);
}

Long countProjectsInPageFilter = Long.parseLong(argv[2]);
String whatToCopy = argv[1]; // page or PageGroup
String Recipient_Type = argv[0]; // "users" or "security_group"
int PageorPageGroupId = Integer.parseInt(argv[3]);
String UserIdListOrSecGroupID = argv[4];
String url = argv[5];
String userName = argv[6];
String passWord = argv[7];
String requestID = argv[8];
int UserLoggedInID = Integer.parseInt(argv[9]);
LOGGER.info("All Params Accepted");
LOGGER.info("Request Generated by : " + UserLoggedInID);
LOGGER.info("Destination Receipient Type : "
+ Recipient_Type + " Destination User/Group :"
+ UserIdListOrSecGroupID);
LOGGER.info("The page ID to be copied is "
+ PageorPageGroupId);
LOGGER.info("Total Count of Project in Page Filter is as :- "
+ countProjectsInPageFilter);

DashboardPageCopyUtil util = new DashboardPageCopyUtil(url,
userName, passWord);
LOGGER.info("Only Logic of Pushing Page to User or security Group is Implemented till now");

util.toPopulatePageNameOrPageGroupName(PageorPageGroupId,
whatToCopy);

util.copyPageorPageGroupToUserOrGroups(Recipient_Type, whatToCopy,
countProjectsInPageFilter, PageorPageGroupId,
UserIdListOrSecGroupID, UserLoggedInID);

util.toNotify(requestID);

LOGGER.info("The Request got Processed Successfully");
} catch (NullPointerException e) {
LOGGER.severe("Null pointer Exception in main method while copying Dashboard page");
LOGGER.severe(e.getMessage());
System.exit(1);
}
// check parameter

catch (Exception e) {

e.printStackTrace();
// LOGGER.info("Hello");
System.exit(1);
// LOGGER.info("Hello2");
LOGGER.severe("Exception in main method while copying Dashboard page");
LOGGER.severe(e.getMessage());

}

}

@SuppressWarnings("deprecation")
private void toPopulatePageNameOrPageGroupName(long pageOrPageGroupID,
String isPageorPageGroup) throws Exception {

if (isPageorPageGroup.equalsIgnoreCase("PAGE")) {
try {

String getPageName = "Select TITLE FROM DSH_PAGES where PAGE_ID = ?";
PreparedStatement sgStmt = session.connection()
.prepareStatement(getPageName);
sgStmt.setInt(1, (int) pageOrPageGroupID);

ResultSet commonUserList = sgStmt.executeQuery();
while (commonUserList.next()) {
String pageName = commonUserList.getString(1);
LOGGER.info("You have selected to push a page with title "
+ pageName
+ ". It will be a Standalone Page to end User");
pageOrPageGroupName = pageName;

} // end of while
commonUserList.close();
sgStmt.close();
} catch (Exception e) {
LOGGER.severe(e.getMessage());
}
}

else if (isPageorPageGroup.equalsIgnoreCase("PAGEGROUP")) {

LOGGER.info("You have selected to Push Page");

try {

String getPageGroupName = "Select TITLE FROM DSH_PAGE_GROUPS where PAGE_GROUP_ID = ?";
PreparedStatement sgStmt = session.connection()
.prepareStatement(getPageGroupName);
sgStmt.setInt(1, (int) pageOrPageGroupID);

ResultSet commonUserList = sgStmt.executeQuery();
while (commonUserList.next()) {
String pageGroupName = commonUserList.getString(1);
LOGGER.info("You have selected to push a page Group Part of your Dashboard with title "
+ pageGroupName + " .");
pageOrPageGroupName = pageGroupName;

} // end of while
commonUserList.close();
sgStmt.close();
} catch (Exception e) {
LOGGER.severe(e.getMessage());
}

}

else {
LOGGER.info("Nothing is Pushed as no Page or Page Group Selected");
}

}

@SuppressWarnings("deprecation")
private void toNotify(String requestID) throws Exception {

if (listOfUsers.endsWith("#@#")) {
String newSubstring = listOfUsers.substring(0,
listOfUsers.length() - 3);

listOfUsers = null;
listOfUsers = newSubstring;
}

LOGGER.info("List of Users to be notifies are as :- "
+ listOfUsers + " " + "The Request ID current processing is "
+ requestID);

try {

LOGGER.info("Now Update ");
PreparedStatement ps_update = session
.connection()
.prepareStatement(
"UPDATE KCRT_REQ_HEADER_DETAILS SET PARAMETER45 = '"
+ listOfUsers
+ "' , VISIBLE_PARAMETER45 = '"
+ listOfUsers
+ "' , VISIBLE_PARAMETER10 = '"
+ pageOrPageGroupName
+ "' where REQUEST_ID = (?)");
ps_update.setObject(1, requestID);
ps_update.executeUpdate();
session.connection().commit();
ps_update.close();
LOGGER.info("Updated List of Users to notify to :- "
+ listOfUsers + " where Request_ID is : " + requestID);

} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}

}
}

This is the full code. Appreciate you that you have been helping this out. We will look into the copy dashboard functionality. The reason why this is being used is because, the users also want to distribute dashboard page groups. So that is the reason. This was designed somewhere around PPM version 9.14 where i guess the functionality was not available OOTB.

Please check into the built-in capability to evaluate possibly adopting it, and please follow up with PSO for any issue related to this custom code. 

Thanks!

And just for the record, yes, the problem seems to be caused by using TableHiLoGenerator with default settings, which will result in large gaps in generated IDs and will end up exceeding max Int value and then returning negative ID values because the max(ID) in tables is too large.

The only way to fix that is to regenerate all your IDs in the Dashboard tables, but that's definitely a problem in your custom code - not a product problem. Addressing this issue is out of the scope of this PPM Discussion forum, as well as out of scope of the PPM Product Support.

Thanks Etienne, can you share the link to the documentation as to how a user can distribute a private page to other users. We are in 10.0.4 and we would explore it on priority.

Sure, check "Share a private Dashboard Page" in the "Use PPM Dashboard" help page.