Java > Open Source Codes > org > compiere > model > MLookupFactory


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/
14 package org.compiere.model;
15
16 import java.sql.PreparedStatement ;
17 import java.sql.ResultSet ;
18 import java.sql.SQLException ;
19 import java.util.ArrayList ;
20 import java.util.Properties ;
21
22 import org.compiere.util.*;
23
24 /**
25  * Create MLookups
26  *
27  * @author Jorg Janke
28  * @version $Id: MLookupFactory.java,v 1.20 2003/11/07 06:37:56 jjanke Exp $
29  */
30 public class MLookupFactory
31 {
32     /** Logging */
33     private static Logger s_log = Logger.getCLogger(MLookupFactory.class);
34
35     /**
36      * Create MLookup
37      *
38      * @param ctx context for access
39      * @param language report language
40      * @param WindowNo window no
41      * @param Column_ID AD_Column_ID or AD_Process_Para_ID
42      * @param ColumnName key column name
43      * @param AD_Reference_ID display type
44      * @param AD_Reference_Value_ID AD_Reference (List, Table)
45      * @param IsParent parent (prevents query to directly access value)
46      * @param ValidationCode optional SQL validation
47      * @throws Exception if Lookup could not be created
48      * @return MLookup
49      */
50     public static MLookup get (Properties ctx, int WindowNo, int Column_ID, int AD_Reference_ID,
51             Language language, String ColumnName, int AD_Reference_Value_ID,
52             boolean IsParent, String ValidationCode)
53         throws Exception
54     {
55         MLookupInfo info = getLookupInfo (ctx, WindowNo, Column_ID, AD_Reference_ID,
56             language, ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode);
57         if (info == null)
58             throw new Exception ("MLookup.create - no LookupInfo");
59         return new MLookup(info, 0);
60     } // create
61
62     /**
63      * Create MLookup
64      *
65      * @param ctx context for access
66      * @param WindowNo window no
67      * @param TabNo TabNo
68      * @param Column_ID AD_Column_ID or AD_Process_Para_ID
69      * @param AD_Reference_ID display type
70      * @return MLookup
71      */
72     public static MLookup get (Properties ctx, int WindowNo, int TabNo, int Column_ID, int AD_Reference_ID)
73     {
74         String ColumnName = "";
75         int AD_Reference_Value_ID = 0;
76         boolean IsParent = false;
77         String ValidationCode = "";
78         //
79 String sql = "SELECT c.ColumnName, c.AD_Reference_Value_ID, c.IsParent, vr.Code "
80             + "FROM AD_Column c"
81             + " LEFT OUTER JOIN AD_Val_Rule vr ON (c.AD_Val_Rule_ID=vr.AD_Val_Rule_ID) "
82             + "WHERE c.AD_Column_ID=?";
83         PreparedStatement pstmt = null;
84         try
85         {
86             pstmt = DB.prepareStatement(sql);
87             pstmt.setInt(1, Column_ID);
88             //
89 ResultSet rs = pstmt.executeQuery();
90             if (rs.next())
91             {
92                 ColumnName = rs.getString(1);
93                 AD_Reference_Value_ID = rs.getInt(2);
94                 IsParent = "Y".equals(rs.getString(3));
95                 ValidationCode = rs.getString(4);
96             }
97             else
98                 s_log.error("create - Column Not Found - AD_Column_ID=" + Column_ID);
99             rs.close();
100             //
101 pstmt.close();
102             pstmt = null;
103         }
104         catch (SQLException ex)
105         {
106             s_log.error("create", ex);
107         }
108         try
109         {
110             if (pstmt != null)
111                 pstmt.close();
112         }
113         catch (SQLException ex1)
114         {
115         }
116         pstmt = null;
117         //
118 MLookupInfo info = getLookupInfo (ctx, WindowNo, Column_ID, AD_Reference_ID,
119             Env.getLanguage(ctx), ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode);
120         return new MLookup(info, TabNo);
121     } // create
122
123
124     /*************************************************************************/
125
126     /**
127      * Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
128      *
129      * The SQL returns three columns:
130      * <pre>
131      * Key, Value, Name, IsActive (where either key or value is null)
132      * </pre>
133      * @param ctx context for access
134      * @param language report language
135      * @param WindowNo window no
136      * @param Column_ID AD_Column_ID or AD_Process_Para_ID
137      * @param ColumnName key column name
138      * @param AD_Reference_ID display type
139      * @param AD_Reference_Value_ID AD_Reference (List, Table)
140      * @param IsParent parent (prevents query to directly access value)
141      * @param ValidationCode optional SQL validation
142      * @return lookup info structure
143      */
144     static public MLookupInfo getLookupInfo (Properties ctx, int WindowNo, int Column_ID, int AD_Reference_ID,
145         Language language, String ColumnName, int AD_Reference_Value_ID,
146         boolean IsParent, String ValidationCode)
147     {
148         MLookupInfo info = null;
149         boolean needToAddSecurity = true;
150         // List
151 if (AD_Reference_ID == DisplayType.List) // 17
152 {
153             info = getLookup_List(language, AD_Reference_Value_ID);
154             needToAddSecurity = false;
155         }
156         // Table or Search with Reference_Value
157 else if ((AD_Reference_ID == DisplayType.Table || AD_Reference_ID == DisplayType.Search)
158             && AD_Reference_Value_ID != 0)
159         {
160             info = getLookup_Table (ctx, language, WindowNo, AD_Reference_Value_ID);
161         }
162         // TableDir, Search, ID, ...
163 else
164         {
165             info = getLookup_TableDir (ctx, language, WindowNo, ColumnName);
166         }
167         // do we have basic info?
168 if (info == null)
169         {
170             s_log.error ("createLookupInfo - No SQL - " + ColumnName);
171             return null;
172         }
173         // remaining values
174 info.ctx = ctx;
175         info.WindowNo = WindowNo;
176         info.Column_ID = Column_ID;
177         info.DisplayType = AD_Reference_ID;
178         info.AD_Reference_Value_ID = AD_Reference_Value_ID;
179         info.IsParent = IsParent;
180         info.ValidationCode = ValidationCode;
181         if (info.ValidationCode == null)
182             info.ValidationCode = "";
183
184         // Variables in SQL WHERE
185 if (info.Query.indexOf("@") != -1)
186         {
187             String newSQL = Env.parseContext(ctx, WindowNo, info.Query, false);
188             if (newSQL.length() == 0)
189             {
190                 s_log.error ("createLookupInfo - SQL parse error: " + info.Query);
191                 return null;
192             }
193             info.Query = newSQL;
194         }
195
196         // Direct Query - NO Validation/Security
197 int posOrder = info.Query.lastIndexOf(" ORDER BY ");
198         boolean hasWhere = info.Query.lastIndexOf(" WHERE ") != -1;
199         if (hasWhere) // might be for a select sub-query
200 {
201             // SELECT (SELECT .. FROM .. WHERE ..) FROM ..
202 // SELECT .. FROM .. WHERE EXISTS (SELECT .. FROM .. WHERE ..)
203 AccessSqlParser asp = new AccessSqlParser(info.Query);
204             String mainQuery = asp.getMainSql();
205             hasWhere = mainQuery.indexOf(" WHERE ") != -1;
206         }
207         if (posOrder == -1)
208             info.QueryDirect = info.Query
209                 + (hasWhere ? " AND " : " WHERE ") + info.KeyColumn + "=?";
210         else
211             info.QueryDirect = info.Query.substring(0, posOrder)
212                 + (hasWhere ? " AND " : " WHERE ") + info.KeyColumn + "=?";
213
214         // Validation
215 String local_validationCode = "";
216         if (info.ValidationCode.length() == 0)
217             info.IsValidated = true;
218         else
219         {
220             local_validationCode = Env.parseContext (ctx, WindowNo, info.ValidationCode, true);
221             if (local_validationCode.length() == 0) // returns "" if not all variables were parsed
222 info.IsValidated = false;
223             else
224                 info.IsValidated = true;
225         }
226         
227         // Add Local Validation
228 if (local_validationCode.length() != 0)
229         {
230             posOrder = info.Query.lastIndexOf(" ORDER BY ");
231             hasWhere = info.Query.lastIndexOf(" WHERE ") != -1;
232             info.Query = info.Query.substring(0, posOrder)
233                 + (hasWhere ? " AND " : " WHERE ") + local_validationCode
234                 + info.Query.substring(posOrder);
235         }
236                 
237         // Add Security
238 if (needToAddSecurity)
239             info.Query = MRole.getDefault(ctx, false).addAccessSQL(info.Query,
240                 info.TableName, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
241         //
242 // s_log.debug ("createLookupInfo - QueryDirect " + info.QueryDirect);
243 return info;
244     } // createLookupInfo
245
246     /*************************************************************************/
247
248     /**
249      * Get Lookup SQL for Lists
250      * @param language report language
251      * @param AD_Reference_Value_ID reference value
252      * @return SELECT NULL, Value, Name, IsActive FROM AD_Ref_List
253      */
254     static public MLookupInfo getLookup_List(Language language, int AD_Reference_Value_ID)
255     {
256         StringBuffer realSQL = new StringBuffer ("SELECT NULL, AD_Ref_List.Value,");
257         if (Env.isBaseLanguage(language, "AD_Ref_List"))
258             realSQL.append("AD_Ref_List.Name,AD_Ref_List.IsActive FROM AD_Ref_List");
259         else
260             realSQL.append("trl.Name, AD_Ref_List.IsActive "
261                 + "FROM AD_Ref_List INNER JOIN AD_Ref_List_Trl trl "
262                 + " ON (AD_Ref_List.AD_Ref_List_ID=trl.AD_Ref_List_ID AND trl.AD_Language='")
263                     .append(language.getAD_Language()).append("')");
264         realSQL.append(" WHERE AD_Ref_List.AD_Reference_ID=").append(AD_Reference_Value_ID);
265         realSQL.append(" ORDER BY 2");
266         //
267 return new MLookupInfo(realSQL.toString(), "AD_Ref_List", "AD_Ref_List.Value",
268             101,101, MQuery.getEqualQuery("AD_Reference_ID", AD_Reference_Value_ID)); // Zoom Window+Query
269 } // getLookup_List
270
271     /*************************************************************************/
272
273     /**
274      * Get Lookup SQL for Table Lookup
275      * @param ctx context for access and dynamic access
276      * @param language report language
277      * @param WindowNo window no
278      * @param AD_Reference_Value_ID reference value
279      * @return SELECT Key, NULL, Name, IsActive FROM Table - if KeyColumn end with _ID
280      * otherwise SELECT NULL, Key, Name, IsActive FROM Table
281      */
282     static private MLookupInfo getLookup_Table (Properties ctx, Language language,
283         int WindowNo, int AD_Reference_Value_ID)
284     {
285         String sql0 = "SELECT t.TableName,ck.ColumnName AS KeyColumn," // 1..2
286 + "cd.ColumnName AS DisplayColumn,rt.isValueDisplayed,cd.IsTranslated," // 3..5
287 + "rt.WhereClause,rt.OrderByClause,t.AD_Window_ID,t.PO_Window_ID, " // 6..9
288 + "t.AD_Table_ID " // 10
289 + "FROM AD_Ref_Table rt"
290             + " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID)"
291             + " INNER JOIN AD_Column ck ON (rt.AD_Key=ck.AD_Column_ID)"
292             + " INNER JOIN AD_Column cd ON (rt.AD_Display=cd.AD_Column_ID) "
293             + "WHERE rt.AD_Reference_ID=?"
294             + " AND rt.IsActive='Y' AND t.IsActive='Y'";
295         //
296 String KeyColumn = null, DisplayColumn = null, TableName = null, WhereClause = null, OrderByClause = null;
297         boolean IsTranslated = false, isValueDisplayed = false;
298         boolean isSOTrx = !"N".equals(Env.getContext(ctx, WindowNo, "IsSOTrx"));
299         int ZoomWindow = 0;
300         int ZoomWindowPO = 0;
301         int AD_Table_ID = 0;
302         boolean loaded = false;
303
304         try
305         {
306             PreparedStatement pstmt = DB.prepareStatement(sql0);
307             pstmt.setInt(1, AD_Reference_Value_ID);
308             ResultSet rs = pstmt.executeQuery();
309             if (rs.next())
310             {
311                 TableName = rs.getString(1);
312                 KeyColumn = rs.getString(2);
313                 DisplayColumn = rs.getString(3);
314                 isValueDisplayed = "Y".equals(rs.getString(4));
315                 IsTranslated = "Y".equals(rs.getString(5));
316                 WhereClause = rs.getString(6);
317                 OrderByClause = rs.getString(7);
318                 ZoomWindow = rs.getInt(8);
319                 ZoomWindowPO = rs.getInt(9);
320                 AD_Table_ID = rs.getInt(10);
321                 loaded = true;
322             }
323             rs.close();
324             pstmt.close();
325         }
326         catch (SQLException e)
327         {
328             s_log.error("getLookup_Table", e);
329             return null;
330         }
331         if (!loaded)
332         {
333             s_log.error("getLookup_Table - No Table Reference Table ID=" + AD_Reference_Value_ID);
334             return null;
335         }
336
337         StringBuffer realSQL = new StringBuffer ("SELECT ");
338         if (!KeyColumn.endsWith("_ID"))
339             realSQL.append("NULL,");
340
341         // Translated
342 if (IsTranslated && !Env.isBaseLanguage(language, TableName))
343         {
344             realSQL.append(TableName).append(".").append(KeyColumn).append(",");
345             if (KeyColumn.endsWith("_ID"))
346                 realSQL.append("NULL,");
347             if (isValueDisplayed)
348                 realSQL.append(TableName).append(".Value || '-' || ");
349             realSQL.append(TableName).append("_Trl.").append(DisplayColumn)
350                 .append(",").append(TableName).append(".IsActive");
351             realSQL.append(" FROM ").append(TableName)
352                 .append(" INNER JOIN ").append(TableName).append("_TRL ON (")
353                 .append(TableName).append(".").append(KeyColumn)
354                 .append("=").append(TableName).append("_Trl.").append(KeyColumn)
355                 .append(" AND ").append(TableName).append("_Trl.AD_Language='")
356                 .append(language.getAD_Language()).append("')");
357         }
358         // Not Translated
359 else
360         {
361             realSQL.append(TableName).append(".").append(KeyColumn).append(",");
362             if (KeyColumn.endsWith("_ID"))
363                 realSQL.append("NULL,");
364             if (isValueDisplayed)
365                 realSQL.append(TableName).append(".Value || '-' || ");
366             realSQL.append(TableName).append(".").append(DisplayColumn);
367             realSQL.append(",").append(TableName).append(".IsActive");
368             realSQL.append(" FROM ").append(TableName);
369         }
370
371         // add WHERE clause
372 MQuery zoomQuery = null;
373         if (WhereClause != null)
374         {
375             String where = WhereClause;
376             if (where.indexOf("@") != -1)
377                 where = Env.parseContext(ctx, WindowNo, where, false);
378             if (where.length() == 0 && WhereClause.length() != 0)
379                 s_log.error ("getLookup_Table - Could not resolve: " + WhereClause);
380
381             // We have no context
382 if (where.length() != 0)
383             {
384                 realSQL.append(" WHERE ").append(where);
385                 if (where.indexOf(".") == -1)
386                     s_log.error("getLookup_Table - " + TableName
387                         + ": WHERE should be fully qualified: " + WhereClause);
388                 zoomQuery = new MQuery (TableName);
389                 zoomQuery.addRestriction(where);
390             }
391         }
392
393         // Order By qualified term or by Name
394 if (OrderByClause != null)
395         {
396             realSQL.append(" ORDER BY ").append(OrderByClause);
397             if (OrderByClause.indexOf(".") == -1)
398                 s_log.error("getLookup_Table - " + TableName
399                     + ": ORDER BY must fully qualified: " + OrderByClause);
400         }
401         else
402             realSQL.append(" ORDER BY 3");
403
404         if (Log.isTraceLevel(10))
405             s_log.debug("getLookup_Table: " + AD_Reference_Value_ID + " - " + realSQL);
406         return new MLookupInfo (realSQL.toString(), TableName,
407             TableName + "." + KeyColumn, ZoomWindow, ZoomWindowPO, zoomQuery);
408     } // getLookup_Table
409
410     /**
411      * Get Embedded Lookup SQL for Table Lookup
412      * @param language report language
413      * @param BaseColumn base column name
414      * @param BaseTable base table name
415      * @param AD_Reference_Value_ID reference value
416      * @return SELECT Name FROM Table
417      */
418     static public String getLookup_TableEmbed (Language language,
419         String BaseColumn, String BaseTable, int AD_Reference_Value_ID)
420     {
421         String sql = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"
422             + "cd.ColumnName AS DisplayColumn,rt.isValueDisplayed,cd.IsTranslated "
423             + "FROM AD_Ref_Table rt"
424             + " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID)"
425             + " INNER JOIN AD_Column ck ON (rt.AD_Key=ck.AD_Column_ID)"
426             + " INNER JOIN AD_Column cd ON (rt.AD_Display=cd.AD_Column_ID) "
427             + "WHERE rt.AD_Reference_ID=?"
428             + " AND rt.IsActive='Y' AND t.IsActive='Y'";
429         //
430 String KeyColumn, DisplayColumn, TableName;
431         boolean IsTranslated, isValueDisplayed;
432
433         try
434         {
435             PreparedStatement pstmt = DB.prepareStatement(sql);
436             pstmt.setInt(1, AD_Reference_Value_ID);
437             ResultSet rs = pstmt.executeQuery();
438             if (!rs.next())
439             {
440                 s_log.error("getLookup_TableEmbed - Cannot find Reference Table, ID=" + AD_Reference_Value_ID
441                     + ", Base=" + BaseTable + "." + BaseColumn);
442                 rs.close();
443                 pstmt.close();
444                 return null;
445             }
446
447             TableName = rs.getString(1);
448             KeyColumn = rs.getString(2);
449             DisplayColumn = rs.getString(3);
450             isValueDisplayed = rs.getString(4).equals("Y");
451             IsTranslated = rs.getString(5).equals("Y");
452
453             rs.close();
454             pstmt.close();
455         }
456         catch (SQLException e)
457         {
458             s_log.error("getLookup_TableEmbed", e);
459             return null;
460         }
461
462         StringBuffer embedSQL = new StringBuffer ("SELECT ");
463
464         // Translated
465 if (IsTranslated && !Env.isBaseLanguage(language, TableName))
466         {
467             if (isValueDisplayed)
468                 embedSQL.append(TableName).append(".Value||'-'||");
469             embedSQL.append(TableName).append("_Trl.").append(DisplayColumn);
470             //
471 embedSQL.append(" FROM ").append(TableName)
472                 .append(" INNER JOIN ").append(TableName).append("_TRL ON (")
473                 .append(TableName).append(".").append(KeyColumn)
474                 .append("=").append(TableName).append("_Trl.").append(KeyColumn)
475                 .append(" AND ").append(TableName).append("_Trl.AD_Language='")
476                 .append(language.getAD_Language()).append("')");
477         }
478         // Not Translated
479 else
480         {
481             if (isValueDisplayed)
482                 embedSQL.append(TableName).append(".Value||'-'||");
483             embedSQL.append(TableName).append(".").append(DisplayColumn);
484             //
485 embedSQL.append(" FROM ").append(TableName);
486         }
487
488         embedSQL.append(" WHERE ").append(BaseTable).append(".").append(BaseColumn);
489         embedSQL.append("=").append(TableName).append(".").append(KeyColumn);
490
491         return embedSQL.toString();
492     } // getLookup_TableEmbed
493
494
495     /*************************************************************************/
496
497     /**
498      * Get Lookup SQL for direct Table Lookup
499      * @param ctx context for access
500      * @param language report language
501      * @param ColumnName column name
502      * @param WindowNo Window (for SOTrx)
503      * @return SELECT Key, NULL, Name, IsActive from Table (fully qualified)
504      */
505     static private MLookupInfo getLookup_TableDir (Properties ctx, Language language,
506         int WindowNo, String ColumnName)
507     {
508         if (!ColumnName.endsWith("_ID"))
509         {
510             s_log.error("getLookup_TableDir - Key does not end with '_ID': " + ColumnName);
511             return null;
512         }
513
514         String TableName = ColumnName.substring(0,ColumnName.length()-3);
515         boolean isSOTrx = !"N".equals(Env.getContext(ctx, WindowNo, "IsSOTrx"));
516         int ZoomWindow = 0;
517         int ZoomWindowPO = 0;
518
519         // get display column names
520 String sql0 = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID,t.AD_Window_ID,t.PO_Window_ID "
521             + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
522             + "WHERE TableName=?"
523             + " AND c.IsIdentifier='Y' "
524             + "ORDER BY c.SeqNo";
525         //
526 String KeyColumn = ColumnName;
527         //
528 ArrayList list = new ArrayList ();
529         boolean isTranslated = false;
530         //
531 try
532         {
533             PreparedStatement pstmt = DB.prepareStatement(sql0);
534             pstmt.setString(1, TableName);
535             ResultSet rs = pstmt.executeQuery();
536             while (rs.next())
537             {
538                 LookupDisplayColumn ldc = new LookupDisplayColumn (rs.getString(1),
539                     "Y".equals(rs.getString(2)), rs.getInt(3), rs.getInt(4));
540                 list.add (ldc);
541             // s_log.debug("getLookup_TableDir: " + ColumnName + " - " + ldc);
542 //
543 if (!isTranslated && ldc.IsTranslated)
544                     isTranslated = true;
545                 ZoomWindow = rs.getInt(5);
546                 ZoomWindowPO = rs.getInt(6);
547             }
548             rs.close();
549             pstmt.close();
550         }
551         catch (SQLException e)
552         {
553             s_log.error("getLookup_TableDir", e);
554             return null;
555         }
556         // Do we have columns ?
557 if (list.size() == 0)
558         {
559             s_log.error("getLookup_TableDir - No Identifier records found: " + ColumnName);
560             return null;
561         }
562
563         StringBuffer realSQL = new StringBuffer ("SELECT ");
564         realSQL.append(TableName).append(".").append(KeyColumn).append(",NULL,");
565
566         StringBuffer displayColumn = new StringBuffer ();
567         int size = list.size();
568         // Get Display Column
569 for (int i = 0; i < size; i++)
570         {
571             if (i > 0)
572                 displayColumn.append(" ||'_'|| " );
573             LookupDisplayColumn ldc = (LookupDisplayColumn)list.get(i);
574
575             // translated
576 if (ldc.IsTranslated && !Env.isBaseLanguage(language, TableName))
577                 displayColumn.append(TableName).append("_Trl.").append(ldc.ColumnName);
578             // date
579 else if (DisplayType.isDate(ldc.DisplayType))
580             {
581                 displayColumn.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
582             }
583             // TableDir
584 else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
585                 && ldc.ColumnName.endsWith("_ID"))
586             {
587                 String embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName);
588                 if (embeddedSQL != null)
589                     displayColumn.append("(").append(embeddedSQL).append(")");
590             }
591             // Table
592 else if (ldc.DisplayType == DisplayType.Table && ldc.AD_Reference_ID != 0)
593             {
594                 String embeddedSQL = getLookup_TableEmbed (language, ldc.ColumnName, TableName, ldc.AD_Reference_ID);
595                 if (embeddedSQL != null)
596                     displayColumn.append("(").append(embeddedSQL).append(")");
597             }
598             // number
599 else if (DisplayType.isNumeric(ldc.DisplayType))
600             {
601                 displayColumn.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
602             }
603             // String
604 else
605                 displayColumn.append(TableName).append(".").append(ldc.ColumnName);
606         }
607         realSQL.append(displayColumn.toString());
608         realSQL.append(",").append(TableName).append(".IsActive");
609
610         // Translation
611 if (isTranslated && !Env.isBaseLanguage(language, TableName))
612         {
613             realSQL.append(" FROM ").append(TableName)
614                 .append(" INNER JOIN ").append(TableName).append("_TRL ON (")
615                 .append(TableName).append(".").append(KeyColumn)
616                 .append("=").append(TableName).append("_Trl.").append(KeyColumn)
617                 .append(" AND ").append(TableName).append("_Trl.AD_Language='")
618                 .append(language.getAD_Language()).append("')");
619         }
620         else // no translation
621 {
622             realSQL.append(" FROM ").append(TableName);
623         }
624
625         // Order by Display
626 realSQL.append(" ORDER BY 3");
627
628         if (Log.isTraceLevel(10))
629             s_log.debug("getLookup_TableDir: " + ColumnName + " - " + realSQL);
630         MLookupInfo lInfo = new MLookupInfo(realSQL.toString(), TableName,
631             TableName + "." + KeyColumn, ZoomWindow, ZoomWindowPO, null);
632         return lInfo;
633     } // getLookup_TableDir
634
635
636     /**
637      * Get embedded SQL for TableDir Lookup (no translation)
638      *
639      * @param language report language
640      * @param ColumnName column name
641      * @param BaseTable base table
642      * @return SELECT Column FROM TableName WHERE BaseTable.ColumnName=TableName.ColumnName
643      */
644     static public String getLookup_TableDirEmbed (Language language, String ColumnName, String BaseTable)
645     {
646         return getLookup_TableDirEmbed (language, ColumnName, BaseTable, ColumnName);
647     } // getLookup_TableDirEmbed
648
649     /**
650      * Get embedded SQL for TableDir Lookup (no translation)
651      *
652      * @param language report language
653      * @param ColumnName column name
654      * @param BaseTable base table
655      * @param BaseColumn base column
656      * @return SELECT Column FROM TableName WHERE BaseTable.BaseColumn=TableName.ColumnName
657      */
658     static public String getLookup_TableDirEmbed (Language language,
659         String ColumnName, String BaseTable, String BaseColumn)
660     {
661         String TableName = ColumnName.substring(0,ColumnName.length()-3);
662
663         // get display column name (first identifier column)
664 String SQL = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID "
665             + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
666             + "WHERE TableName=?"
667             + " AND c.IsIdentifier='Y' "
668             + "ORDER BY c.SeqNo";
669         //
670 ArrayList list = new ArrayList ();
671         //
672 try
673         {
674             PreparedStatement pstmt = DB.prepareStatement(SQL);
675             pstmt.setString(1, TableName);
676             ResultSet rs = pstmt.executeQuery();
677             while (rs.next())
678             {
679                 LookupDisplayColumn ldc = new LookupDisplayColumn (rs.getString(1),
680                     "Y".equals(rs.getString(2)), rs.getInt(3), rs.getInt(4));
681                 list.add (ldc);
682             // s_log.debug("getLookup_TableDirEmbed: " + ColumnName + " - " + ldc);
683 }
684             rs.close();
685             pstmt.close();
686         }
687         catch (SQLException e)
688         {
689             s_log.error("getLookup_TableDirEmbed", e);
690             return "";
691         }
692         // Do we have columns ?
693 if (list.size() == 0)
694         {
695             s_log.error("getLookup_TableDirEmbed - No Identifier records found: " + ColumnName);
696             return "";
697         }
698
699         //
700 StringBuffer embedSQL = new StringBuffer ("SELECT ");
701
702         int size = list.size();
703         for (int i = 0; i < size; i++)
704         {
705             if (i > 0)
706                 embedSQL.append("||' - '||" );
707             LookupDisplayColumn ldc = (LookupDisplayColumn)list.get(i);
708
709             // date, number
710 if (DisplayType.isDate(ldc.DisplayType) || DisplayType.isNumeric(ldc.DisplayType))
711             {
712                 embedSQL.append(DB.TO_CHAR(TableName + "." + ldc.ColumnName, ldc.DisplayType, language.getAD_Language()));
713             }
714             // TableDir
715 else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
716               && ldc.ColumnName.endsWith("_ID"))
717             {
718                 String embeddedSQL = getLookup_TableDirEmbed(language, ldc.ColumnName, TableName);
719                 embedSQL.append("(").append(embeddedSQL).append(")");
720             }
721             // String
722 else
723                 embedSQL.append(TableName).append(".").append(ldc.ColumnName);
724         }
725
726         embedSQL.append(" FROM ").append(TableName);
727         embedSQL.append(" WHERE ").append(BaseTable).append(".").append(BaseColumn);
728         embedSQL.append("=").append(TableName).append(".").append(ColumnName);
729         //
730 return embedSQL.toString();
731     } // getLookup_TableDirEmbed
732
733 } // MLookupFactory
734
735

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Free Books   Free Magazines  

Popular Tags