Fixed issue 20221: If too many linked items exists they will not be shown.
authorGuillermo Álvarez de Eulate <guillermo.alvarez@openbravo.com>
Thu, 10 May 2012 17:43:01 +0200
changeset 16489 78d28661d80a
parent 16488 d989dee41c4e
child 16490 bca89372cadc
Fixed issue 20221: If too many linked items exists they will not be shown.

In order to avoid problems with oracle (only accepts 1000 items for in expession) and to preserve the performance, the tabs which are related with too many columns (more than 999) will not show the linked items. A new message has been created to be shown.
src-db/database/sourcedata/AD_MESSAGE.xml
src/org/openbravo/erpCommon/utility/UsedByLink.java
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Thu May 10 16:01:40 2012 +0200
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Thu May 10 17:43:01 2012 +0200
@@ -15136,6 +15136,17 @@
 <!--1C68A76422C94FEC882C1E576C9F4F28-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--1C68A76422C94FEC882C1E576C9F4F28--></AD_MESSAGE>
 
+<!--1CF066F6A68F419CA5241635305B9B77--><AD_MESSAGE>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <AD_MESSAGE_ID><![CDATA[1CF066F6A68F419CA5241635305B9B77]]></AD_MESSAGE_ID>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <VALUE><![CDATA[TooManyColumnsLinked]]></VALUE>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <MSGTEXT><![CDATA[Linked items cannot be shown. There are too many columns linked with this item.]]></MSGTEXT>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <MSGTYPE><![CDATA[I]]></MSGTYPE>
+<!--1CF066F6A68F419CA5241635305B9B77-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--1CF066F6A68F419CA5241635305B9B77--></AD_MESSAGE>
+
 <!--1D9A15F68BC447C68725A544F8ED1719--><AD_MESSAGE>
 <!--1D9A15F68BC447C68725A544F8ED1719-->  <AD_MESSAGE_ID><![CDATA[1D9A15F68BC447C68725A544F8ED1719]]></AD_MESSAGE_ID>
 <!--1D9A15F68BC447C68725A544F8ED1719-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src/org/openbravo/erpCommon/utility/UsedByLink.java	Thu May 10 16:01:40 2012 +0200
+++ b/src/org/openbravo/erpCommon/utility/UsedByLink.java	Thu May 10 17:43:01 2012 +0200
@@ -308,91 +308,103 @@
       String keyColumn, String keyId, String tableId) throws IOException, ServletException {
 
     final String keyColumnId = UsedByLinkData.selectKeyColumnId(this, tableId);
-
+    int numOfRelatedColumns = 0;
     boolean nonAccessible = false;
 
     UsedByLinkData[] data = null;
 
-    // Obtain the list of tables that are linked to the current one using DAL, this list will return
+    // Obtain the list of columns that are linked to the current one using DAL, this list will
+    // return
     // any reference including user defined ones. It will be joined with the old ones because
     // currently it doesn't support views.
-    StringBuffer linkedTablesQuery = new StringBuffer();
+    StringBuffer linkedColumnsQuery = new StringBuffer();
     for (LinkedTable linkedTable : getLinkedTables(tableId)) {
-      if (linkedTablesQuery.length() != 0) {
-        linkedTablesQuery.append(", ");
+      if (linkedColumnsQuery.length() != 0) {
+        linkedColumnsQuery.append(", ");
       }
-      linkedTablesQuery.append(linkedTable.toQueryString());
+      linkedColumnsQuery.append(linkedTable.toQueryString());
     }
-    if (linkedTablesQuery.length() == 0) {
-      linkedTablesQuery.append("'--'");
+    if (linkedColumnsQuery.length() == 0) {
+      linkedColumnsQuery.append("'--'");
+      numOfRelatedColumns = 0;
+    } else {
+      numOfRelatedColumns = linkedColumnsQuery.toString().split(",").length;
     }
 
-    data = UsedByLinkData.select(this, vars.getClient(), vars.getLanguage(), vars.getRole(),
-        keyColumnId, keyColumn, tableId, linkedTablesQuery.toString());
+    if (numOfRelatedColumns < 1000) {
+      data = UsedByLinkData.select(this, vars.getClient(), vars.getLanguage(), vars.getRole(),
+          keyColumnId, keyColumn, tableId, linkedColumnsQuery.toString());
 
-    if (data != null && data.length > 0) {
-      final Vector<UsedByLinkData> vecTotal = new Vector<UsedByLinkData>();
-      for (int i = 0; i < data.length; i++) {
-        String keyValue = keyId;
-        if (!data[i].referencedColumnId.equals(keyColumnId)) {
-          try {
-            keyValue = UsedByLinkData.selectKeyValue(this,
-                UsedByLinkData.selectColumnName(this, data[i].referencedColumnId),
-                data[i].tablename, data[i].columnname, keyId);
-          } catch (Exception e) {
-            // TODO: handle exception
+      if (data != null && data.length > 0) {
+        final Vector<UsedByLinkData> vecTotal = new Vector<UsedByLinkData>();
+        for (int i = 0; i < data.length; i++) {
+          String keyValue = keyId;
+          if (!data[i].referencedColumnId.equals(keyColumnId)) {
+            try {
+              keyValue = UsedByLinkData.selectKeyValue(this,
+                  UsedByLinkData.selectColumnName(this, data[i].referencedColumnId),
+                  data[i].tablename, data[i].columnname, keyId);
+            } catch (Exception e) {
+              // TODO: handle exception
+            }
+          }
+          if (log4j.isDebugEnabled())
+            log4j.debug("***Referenced tab: " + data[i].adTabId);
+          final UsedByLinkData[] dataRef = UsedByLinkData.windowRef(this, data[i].adTabId);
+          if (dataRef == null || dataRef.length == 0)
+            continue;
+          String strWhereClause = getWhereClause(vars, strWindow, dataRef[0].whereclause);
+          if (log4j.isDebugEnabled())
+            log4j.debug("***   Referenced where clause (1): " + strWhereClause);
+          strWhereClause += getAditionalWhereClause(vars, strWindow, data[i].adTabId,
+              data[i].tablename, keyColumn, data[i].columnname,
+              UsedByLinkData.getTabTableName(this, tableId));
+          if (log4j.isDebugEnabled())
+            log4j.debug("***   Referenced where clause (2): " + strWhereClause);
+          if (!nonAccessible) {
+            final String strNonAccessibleWhere = strWhereClause + " AND AD_ORG_ID NOT IN ("
+                + vars.getUserOrg() + ")";
+            if (!UsedByLinkData.countLinks(this, data[i].tablename, data[i].columnname, keyValue,
+                strNonAccessibleWhere).equals("0")) {
+              nonAccessible = true;
+            }
+          }
+          strWhereClause += " AND AD_ORG_ID IN (" + vars.getUserOrg() + ") AND AD_CLIENT_ID IN ("
+              + vars.getUserClient() + ")";
+          int total = Integer.valueOf(
+              UsedByLinkData.countLinks(this, data[i].tablename, data[i].columnname, keyValue,
+                  strWhereClause)).intValue();
+
+          if (log4j.isDebugEnabled())
+            log4j.debug("***   Count: " + total);
+
+          data[i].total = Integer.toString(total);
+
+          if (data[i].accessible.equals("N") && total > 0) {
+            nonAccessible = true;
+          } else if (total > 0 && !existsInVector(data[i], vecTotal)) {
+            vecTotal.addElement(data[i]);
           }
         }
-        if (log4j.isDebugEnabled())
-          log4j.debug("***Referenced tab: " + data[i].adTabId);
-        final UsedByLinkData[] dataRef = UsedByLinkData.windowRef(this, data[i].adTabId);
-        if (dataRef == null || dataRef.length == 0)
-          continue;
-        String strWhereClause = getWhereClause(vars, strWindow, dataRef[0].whereclause);
-        if (log4j.isDebugEnabled())
-          log4j.debug("***   Referenced where clause (1): " + strWhereClause);
-        strWhereClause += getAditionalWhereClause(vars, strWindow, data[i].adTabId,
-            data[i].tablename, keyColumn, data[i].columnname,
-            UsedByLinkData.getTabTableName(this, tableId));
-        if (log4j.isDebugEnabled())
-          log4j.debug("***   Referenced where clause (2): " + strWhereClause);
-        if (!nonAccessible) {
-          final String strNonAccessibleWhere = strWhereClause + " AND AD_ORG_ID NOT IN ("
-              + vars.getUserOrg() + ")";
-          if (!UsedByLinkData.countLinks(this, data[i].tablename, data[i].columnname, keyValue,
-              strNonAccessibleWhere).equals("0")) {
-            nonAccessible = true;
-          }
-        }
-        strWhereClause += " AND AD_ORG_ID IN (" + vars.getUserOrg() + ") AND AD_CLIENT_ID IN ("
-            + vars.getUserClient() + ")";
-        int total = Integer.valueOf(
-            UsedByLinkData.countLinks(this, data[i].tablename, data[i].columnname, keyValue,
-                strWhereClause)).intValue();
+        data = new UsedByLinkData[vecTotal.size()];
+        vecTotal.copyInto(data);
+      }
 
-        if (log4j.isDebugEnabled())
-          log4j.debug("***   Count: " + total);
-
-        data[i].total = Integer.toString(total);
-
-        if (data[i].accessible.equals("N") && total > 0) {
-          nonAccessible = true;
-        } else if (total > 0 && !existsInVector(data[i], vecTotal)) {
-          vecTotal.addElement(data[i]);
-        }
+      if (nonAccessible) {
+        final OBError myMessage = new OBError();
+        myMessage.setType("Info");
+        myMessage.setMessage(Utility.messageBD(this, "NonAccessibleRecords", vars.getLanguage()));
+        myMessage.setTitle(Utility.messageBD(this, "Info", vars.getLanguage()));
+        return new SearchResult(data, myMessage);
+      } else {
+        return new SearchResult(data);
       }
-      data = new UsedByLinkData[vecTotal.size()];
-      vecTotal.copyInto(data);
-    }
-
-    if (nonAccessible) {
+    } else {
       final OBError myMessage = new OBError();
       myMessage.setType("Info");
-      myMessage.setMessage(Utility.messageBD(this, "NonAccessibleRecords", vars.getLanguage()));
+      myMessage.setMessage(Utility.messageBD(this, "TooManyColumnsLinked", vars.getLanguage()));
       myMessage.setTitle(Utility.messageBD(this, "Info", vars.getLanguage()));
       return new SearchResult(data, myMessage);
-    } else {
-      return new SearchResult(data);
     }
   }
 
@@ -501,32 +513,33 @@
     final OBError msg = searchResult.getMessage();
 
     final List<JSONObject> usedByLinkDataJsonObjects = new ArrayList<JSONObject>();
+    if (usedByLinkData != null) {
+      for (UsedByLinkData data : usedByLinkData) {
+        final JSONObject usedByLinkDataJsonObj = new JSONObject();
+        usedByLinkDataJsonObj.put("accessible", data.accessible);
+        usedByLinkDataJsonObj.put("adMenuName", data.adMenuName);
+        usedByLinkDataJsonObj.put("adTabId", data.adTabId);
+        usedByLinkDataJsonObj.put("adWindowId", data.adWindowId);
+        usedByLinkDataJsonObj.put("columnName", data.columnname);
+        usedByLinkDataJsonObj.put("elementName", data.elementName);
+        usedByLinkDataJsonObj.put("fullElementName", data.elementName + " - " + data.tabname + " ("
+            + data.total + ")");
+        usedByLinkDataJsonObj.put("hasTree", data.hastree);
+        usedByLinkDataJsonObj.put("id", data.id);
+        usedByLinkDataJsonObj.put("name", data.name);
+        usedByLinkDataJsonObj.put("referencedColumnId", data.referencedColumnId);
+        usedByLinkDataJsonObj.put("tableName", data.tablename);
+        usedByLinkDataJsonObj.put("tabName", data.tabname);
+        usedByLinkDataJsonObj.put("total", data.total);
+        usedByLinkDataJsonObj.put("whereClause", data.whereclause);
+        usedByLinkDataJsonObj.put("windowName", data.windowname);
+        usedByLinkDataJsonObj.put("singleRecord", "SR".equals(data.uipattern));
+        usedByLinkDataJsonObj.put("readOnly", "RO".equals(data.uipattern));
+        usedByLinkDataJsonObjects.add(usedByLinkDataJsonObj);
+      }
 
-    for (UsedByLinkData data : usedByLinkData) {
-      final JSONObject usedByLinkDataJsonObj = new JSONObject();
-      usedByLinkDataJsonObj.put("accessible", data.accessible);
-      usedByLinkDataJsonObj.put("adMenuName", data.adMenuName);
-      usedByLinkDataJsonObj.put("adTabId", data.adTabId);
-      usedByLinkDataJsonObj.put("adWindowId", data.adWindowId);
-      usedByLinkDataJsonObj.put("columnName", data.columnname);
-      usedByLinkDataJsonObj.put("elementName", data.elementName);
-      usedByLinkDataJsonObj.put("fullElementName", data.elementName + " - " + data.tabname + " ("
-          + data.total + ")");
-      usedByLinkDataJsonObj.put("hasTree", data.hastree);
-      usedByLinkDataJsonObj.put("id", data.id);
-      usedByLinkDataJsonObj.put("name", data.name);
-      usedByLinkDataJsonObj.put("referencedColumnId", data.referencedColumnId);
-      usedByLinkDataJsonObj.put("tableName", data.tablename);
-      usedByLinkDataJsonObj.put("tabName", data.tabname);
-      usedByLinkDataJsonObj.put("total", data.total);
-      usedByLinkDataJsonObj.put("whereClause", data.whereclause);
-      usedByLinkDataJsonObj.put("windowName", data.windowname);
-      usedByLinkDataJsonObj.put("singleRecord", "SR".equals(data.uipattern));
-      usedByLinkDataJsonObj.put("readOnly", "RO".equals(data.uipattern));
-      usedByLinkDataJsonObjects.add(usedByLinkDataJsonObj);
+      jsonObject.put("usedByLinkData", usedByLinkDataJsonObjects);
     }
-
-    jsonObject.put("usedByLinkData", usedByLinkDataJsonObjects);
     if (msg != null) {
       jsonObject.put("msg", msg.getMessage());
     }