KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > print > DataEngine


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-2002 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/

14 package org.compiere.print;
15
16 import java.sql.*;
17 import java.util.*;
18
19 import org.apache.log4j.Logger;
20
21 import org.compiere.util.Language;
22 import org.compiere.util.DB;
23 import org.compiere.util.DisplayType;
24 import org.compiere.util.KeyNamePair;
25 import org.compiere.util.ValueNamePair;
26 import org.compiere.util.Msg;
27 import org.compiere.util.Env;
28 import org.compiere.util.Access;
29 import org.compiere.util.Log;
30
31 import org.compiere.model.*;
32
33 /**
34  * Data Engine.
35  * Creates SQL and laods data into PrintData (including totals/etc.)
36  *
37  * @author Jorg Janke
38  * @version $Id: DataEngine.java,v 1.27 2003/11/02 07:50:34 jjanke Exp $
39  */

40 public class DataEngine
41 {
42     /**
43      * Constructor
44      * @param language Language of the data (for translation)
45      */

46     public DataEngine (Language language)
47     {
48         if (language != null)
49             m_language = language;
50     } // DataEngine
51

52     /** Logger */
53     private Logger log = Logger.getLogger (getClass());
54     private static Logger s_log = Logger.getLogger (DataEngine.class);
55
56     /** Synonym */
57     private String JavaDoc m_synonym = "A";
58
59     /** Default Language */
60     private Language m_language = Language.getLanguage();
61     /** Break & Column Funcations */
62     private PrintDataGroup m_group = new PrintDataGroup();
63     /** Start Time */
64     private long m_startTime = System.currentTimeMillis();
65
66     public static final String JavaDoc KEY = "*"; // Key Indicator in Report
67

68
69     /*************************************************************************/
70
71     /**
72      * Load Data
73      *
74      * @param format print format
75      * @param query query
76      * @parameter ctx context
77      * @return PrintData
78      */

79     public PrintData getPrintData (Properties ctx, MPrintFormat format, MQuery query)
80     {
81         if (format == null)
82             throw new IllegalStateException JavaDoc ("DataEngine.getPrintData - no print format");
83         String JavaDoc tableName = null;
84         String JavaDoc reportName = format.getName();
85         //
86
if (format.getAD_ReportView_ID() != 0)
87         {
88             String JavaDoc sql = "SELECT t.AD_Table_ID, t.TableName, rv.Name "
89                 + "FROM AD_Table t"
90                 + " INNER JOIN AD_ReportView rv ON (t.AD_Table_ID=rv.AD_Table_ID) "
91                 + "WHERE rv.AD_ReportView_ID=?"; // 1
92
try
93             {
94                 PreparedStatement pstmt = DB.prepareStatement(sql);
95                 pstmt.setInt(1, format.getAD_ReportView_ID());
96                 ResultSet rs = pstmt.executeQuery();
97                 if (rs.next())
98                 {
99                     tableName = rs.getString(2); // TableName
100
reportName = rs.getString(3);
101                 }
102                 rs.close();
103                 pstmt.close();
104             }
105             catch (SQLException e)
106             {
107                 log.error("getPrintData - ReportView", e);
108                 return null;
109             }
110         }
111         else
112         {
113             String JavaDoc sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?"; // #1
114
try
115             {
116                 PreparedStatement pstmt = DB.prepareStatement(sql.toString());
117                 pstmt.setInt(1, format.getAD_Table_ID());
118                 ResultSet rs = pstmt.executeQuery();
119                 if (rs.next())
120                     tableName = rs.getString(1); // TableName
121
rs.close();
122                 pstmt.close();
123             }
124             catch (SQLException e1)
125             {
126                 log.error("getPrintData - Table", e1);
127                 return null;
128             }
129         }
130         if (tableName == null)
131         {
132             log.error("getPrintData - Not found Format=" + format);
133             return null;
134         }
135         if (format.isTranslationView() && tableName.toLowerCase().endsWith("_v")) // _vt not just _v
136
tableName += "t";
137         format.setTranslationViewQuery (query);
138         //
139
PrintData pd = getPrintDataInfo (ctx, format, query, reportName, tableName);
140         loadPrintData(pd, format);
141         return pd;
142     } // getPrintData
143

144     /*************************************************************************/
145
146     /**
147      * Create Load SQL and update PrintData Info
148      *
149      * @param ctx context
150      * @param format print format
151      * @param query query
152      * @param reportName report name
153      * @param tableName table name
154      * @return PrintData
155      */

156     private PrintData getPrintDataInfo (Properties ctx, MPrintFormat format, MQuery query,
157         String JavaDoc reportName, String JavaDoc tableName)
158     {
159         m_startTime = System.currentTimeMillis();
160         log.info("getPrintDataInfo - " + reportName + " - " + m_language.getAD_Language());
161         log.debug("TableName=" + tableName + ", Query=" + query + ", Format=" + format);
162         ArrayList columns = new ArrayList();
163         m_group = new PrintDataGroup();
164
165         // Order Columns (identifed by non zero/null SortNo)
166
int[] orderAD_Column_IDs = format.getOrderAD_Column_IDs();
167         ArrayList orderColumns = new ArrayList(orderAD_Column_IDs.length);
168         for (int i = 0; i < orderAD_Column_IDs.length; i++)
169         {
170             if (Log.isTraceLevel(9))
171                 log.debug("Order AD_Column_ID=" + orderAD_Column_IDs[i]);
172             orderColumns.add(""); // initial value overwritten with fully qualified name
173
}
174
175         // Direct SQL w/o Reference Info
176
StringBuffer JavaDoc sqlSELECT = new StringBuffer JavaDoc("SELECT ");
177         StringBuffer JavaDoc sqlFROM = new StringBuffer JavaDoc(" FROM ");
178         sqlFROM.append(tableName);
179         StringBuffer JavaDoc sqlGROUP = new StringBuffer JavaDoc(" GROUP BY ");
180         //
181
boolean IsGroupedBy = false;
182         //
183
String JavaDoc sql = "SELECT c.AD_Column_ID,c.ColumnName," // 1..2
184
+ "c.AD_Reference_ID,c.AD_Reference_Value_ID," // 3..4
185
+ "c.FieldLength,c.IsMandatory,c.IsKey,c.IsParent," // 5..8
186
+ "COALESCE(rvc.IsGroupFunction,'N'),rvc.FunctionColumn," // 9..10
187
+ "pfi.IsGroupBy,pfi.IsSummarized,pfi.IsAveraged,pfi.IsCounted, " // 11..14
188
+ "pfi.IsPrinted,pfi.SortNo,pfi.IsPageBreak " // 15..17
189
+ "FROM AD_PrintFormat pf"
190             + " INNER JOIN AD_PrintFormatItem pfi ON (pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID)"
191             + " INNER JOIN AD_Column c ON (pfi.AD_Column_ID=c.AD_Column_ID)"
192             + " LEFT OUTER JOIN AD_ReportView_Col rvc ON (pf.AD_ReportView_ID=rvc.AD_ReportView_ID AND c.AD_Column_ID=rvc.AD_Column_ID) "
193             + "WHERE pf.AD_PrintFormat_ID=?" // #1
194
+ " AND pfi.IsActive='Y' AND (pfi.IsPrinted='Y' OR c.IsKey='Y' OR pfi.SortNo > 0) "
195             + "ORDER BY pfi.IsPrinted DESC, pfi.SeqNo"; // Functions are put in first column
196
try
197         {
198             PreparedStatement pstmt = DB.prepareStatement(sql);
199             pstmt.setInt(1, format.getID());
200             ResultSet rs = pstmt.executeQuery();
201
202             m_synonym = "A"; // synonym
203
while (rs.next())
204             {
205                 // get Values from record
206
int AD_Column_ID = rs.getInt(1);
207                 String JavaDoc ColumnName = rs.getString(2);
208                 int AD_Reference_ID = rs.getInt(3);
209                 int AD_Reference_Value_ID = rs.getInt(4);
210                 // ColumnInfo
211
int FieldLength = rs.getInt(5);
212                 boolean IsMandatory = "Y".equals(rs.getString(6));
213                 boolean IsKey = "Y".equals(rs.getString(7));
214                 boolean IsParent = "Y".equals(rs.getString(8));
215                 // SQL GroupBy
216
boolean IsGroupFunction = "Y".equals(rs.getString(9));
217                 if (IsGroupFunction)
218                     IsGroupedBy = true;
219                 String JavaDoc FunctionColumn = rs.getString(10);
220                 if (FunctionColumn == null)
221                     FunctionColumn = "";
222                 // Breaks/Column Functions
223
if ("Y".equals(rs.getString(11)))
224                     m_group.addGroupColumn(ColumnName);
225                 if ("Y".equals(rs.getString(12)))
226                     m_group.addFunction(ColumnName, PrintDataFunction.F_SUM);
227                 if ("Y".equals(rs.getString(13)))
228                     m_group.addFunction(ColumnName, PrintDataFunction.F_AVERAGE);
229                 if ("Y".equals(rs.getString(14)))
230                     m_group.addFunction(ColumnName, PrintDataFunction.F_COUNT);
231                 // General Info
232
boolean IsPrinted = "Y".equals(rs.getString(15));
233                 int SortNo = rs.getInt(16);
234                 boolean isPageBreak = "Y".equals(rs.getString(17));
235
236                 // Fully qualified Table.Column for ordering
237
String JavaDoc orderName = tableName + "." + ColumnName;
238                 PrintDataColumn pdc = null;
239
240                 // -- Key --
241
if (IsKey)
242                 {
243                     // => Table.Column,
244
sqlSELECT.append(tableName).append(".").append(ColumnName).append(",");
245                     sqlGROUP.append(tableName).append(".").append(ColumnName).append(",");
246                     pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, KEY, isPageBreak); // KeyColumn
247
}
248                 else if (!IsPrinted) // not printed Sort Columns
249
;
250                 // -- Parent, TableDir (and unqualified Search) --
251
else if (IsParent || AD_Reference_ID == DisplayType.TableDir
252                         || (AD_Reference_ID == DisplayType.Search && AD_Reference_Value_ID == 0)
253                     )
254                 {
255                     // Creates Embedded SQL in the form
256
// SELECT ColumnTable.Name FROM ColumnTable WHERE TableName.ColumnName=ColumnTable.ColumnName
257
String JavaDoc eSql = MLookupFactory.getLookup_TableDirEmbed(m_language, ColumnName, tableName);
258
259                     // TableName
260
String JavaDoc table = ColumnName;
261                     if (table.endsWith("_ID"))
262                         table = table.substring(0, table.length()-3);
263                     // DisplayColumn
264
String JavaDoc display = ColumnName;
265                     // => (..) AS AName, Table.ID,
266
sqlSELECT.append("(").append(eSql).append(") AS ").append(m_synonym).append(display).append(",")
267                         .append(tableName).append(".").append(ColumnName).append(",");
268                     sqlGROUP.append(m_synonym).append(display).append(",")
269                         .append(tableName).append(".").append(ColumnName).append(",");
270                     orderName = m_synonym + display;
271                     //
272
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
273                     synonymNext();
274                 }
275
276                 // -- Table --
277
else if (AD_Reference_ID == DisplayType.Table
278                         || (AD_Reference_ID == DisplayType.Search && AD_Reference_Value_ID != 0)
279                     )
280                 {
281                     String JavaDoc rInfo[] = getTableReference(AD_Reference_Value_ID);
282                     // TableName
283
String JavaDoc table = rInfo[0];
284                     // DisplayColumn
285
String JavaDoc display = rInfo[2];
286                     // => A.Name AS AName, Table.ID,
287
sqlSELECT.append(m_synonym).append(".").append(display).append(" AS ").append(m_synonym).append(display).append(",")
288                         .append(tableName).append(".").append(ColumnName).append(",");
289                     sqlGROUP.append(m_synonym).append(".").append(display).append(",")
290                         .append(tableName).append(".").append(ColumnName).append(",");
291                     orderName = m_synonym + display;
292
293                     // => x JOIN table A ON (x.KeyColumn=A.Key)
294
if (IsMandatory)
295                         sqlFROM.append(" INNER JOIN ");
296                     else
297                         sqlFROM.append(" LEFT OUTER JOIN ");
298                     sqlFROM.append(table).append(" ").append(m_synonym).append(" ON (")
299                         .append(tableName).append(".").append(ColumnName).append("=")
300                         .append(m_synonym).append(".").append(rInfo[1]).append(")");
301                     //
302
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
303                     synonymNext();
304                 }
305
306                 // -- List or Button with ReferenceValue --
307
else if (AD_Reference_ID == DisplayType.List || (AD_Reference_ID == DisplayType.Button && AD_Reference_Value_ID != 0))
308                 {
309                     if (Env.isBaseLanguage(m_language, "AD_Ref_List"))
310                     {
311                         // => A.Name AS AName,
312
sqlSELECT.append(m_synonym).append(".Name AS ").append(m_synonym).append("Name,");
313                         sqlGROUP.append(m_synonym).append(".Name,");
314                         orderName = m_synonym + "Name";
315                         // => x JOIN AD_Ref_List A ON (x.KeyColumn=A.Value AND A.AD_Reference_ID=123)
316
if (IsMandatory)
317                             sqlFROM.append(" INNER JOIN ");
318                         else
319                             sqlFROM.append(" LEFT OUTER JOIN ");
320                         sqlFROM.append("AD_Ref_List ").append(m_synonym).append(" ON (")
321                             .append(tableName).append(".").append(ColumnName).append("=").append(m_synonym).append(".Value")
322                             .append(" AND ").append(m_synonym).append(".AD_Reference_ID=").append(AD_Reference_Value_ID).append(")");
323                     }
324                     else
325                     {
326                         // => A.Name AS AName,
327
sqlSELECT.append(m_synonym).append(".Name AS ").append(m_synonym).append("Name,");
328                         sqlGROUP.append(m_synonym).append(".Name,");
329                         orderName = m_synonym + "Name";
330
331                         // LEFT OUTER JOIN AD_Ref_List XA ON (AD_Table.EntityType=XA.Value AND XA.AD_Reference_ID=245)
332
// LEFT OUTER JOIN AD_Ref_List_Trl A ON (XA.AD_Ref_List_ID=A.AD_Ref_List_ID AND A.AD_Language='de_DE')
333
if (IsMandatory)
334                             sqlFROM.append(" INNER JOIN ");
335                         else
336                             sqlFROM.append(" LEFT OUTER JOIN ");
337                         sqlFROM.append(" AD_Ref_List X").append(m_synonym).append(" ON (")
338                             .append(tableName).append(".").append(ColumnName).append("=X")
339                             .append(m_synonym).append(".Value AND X").append(m_synonym).append(".AD_Reference_ID=").append(AD_Reference_Value_ID)
340                             .append(")");
341                         if (IsMandatory)
342                             sqlFROM.append(" INNER JOIN ");
343                         else
344                             sqlFROM.append(" LEFT OUTER JOIN ");
345                         sqlFROM.append(" AD_Ref_List_Trl ").append(m_synonym).append(" ON (X")
346                             .append(m_synonym).append(".AD_Ref_List_ID=").append(m_synonym).append(".AD_Ref_List_ID")
347                             .append(" AND ").append(m_synonym).append(".AD_Language='").append(m_language.getAD_Language()).append("')");
348                     }
349                     // TableName.ColumnName,
350
sqlSELECT.append(tableName).append(".").append(ColumnName).append(",");
351                     pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
352                     synonymNext();
353                 }
354
355                 // -- Special Lookups --
356
else if (AD_Reference_ID == DisplayType.Location
357                     || AD_Reference_ID == DisplayType.Account
358                     || AD_Reference_ID == DisplayType.Locator)
359                 {
360                     // TableName, DisplayColumn
361
String JavaDoc table = "", key = "", display = "";
362                     //
363
if (AD_Reference_ID == DisplayType.Location)
364                     {
365                         table = "C_Location";
366                         key = "C_Location_ID";
367                         display = "City";
368                     }
369                     else if (AD_Reference_ID == DisplayType.Account)
370                     {
371                         table = "C_ValidCompination";
372                         key = "C_ValidCombination_ID";
373                         display = "Combination";
374                     }
375                     else if (AD_Reference_ID == DisplayType.Locator)
376                     {
377                         table = "M_Locator";
378                         key = "M_Locator_ID";
379                         display = "Value";
380                     }
381
382                     // => A.Name AS AName, table.ID,
383
sqlSELECT.append(m_synonym).append(".").append(display).append(" AS ")
384                         .append(m_synonym).append(display).append(",")
385                         .append(tableName).append(".").append(ColumnName).append(",");
386                     sqlGROUP.append(m_synonym).append(".").append(display).append(",")
387                         .append(tableName).append(".").append(ColumnName).append(",");
388                     orderName = m_synonym + display;
389                     // => x JOIN table A ON (table.ID=A.Key)
390
if (IsMandatory)
391                         sqlFROM.append(" INNER JOIN ");
392                     else
393                         sqlFROM.append(" LEFT OUTER JOIN ");
394                     sqlFROM.append(table).append(" ").append(m_synonym).append(" ON (")
395                         .append(tableName).append(".").append(ColumnName).append("=")
396                         .append(m_synonym).append(".").append(key).append(")");
397                     //
398
pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, orderName, isPageBreak);
399                     synonymNext();
400                 }
401
402                 // -- Standard Column --
403
else
404                 {
405                     int index = FunctionColumn.indexOf("@");
406                     StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
407                     if (index == -1)
408                     {
409                     // => Table.Column,
410
sb.append(tableName).append(".").append(ColumnName).append(",");
411                         sqlSELECT.append(sb.toString());
412                         if (!IsGroupFunction)
413                             sqlGROUP.append(sb.toString());
414                     }
415                     else
416                     {
417                     // => Function(Table.Column) AS Column -- function has @ where column name goes
418
sb.append(FunctionColumn.substring(0, index))
419                             .append(tableName).append(".").append(ColumnName)
420                             .append(FunctionColumn.substring(index+1));
421                         sqlSELECT.append(sb.toString()).append(" AS ").append(ColumnName).append(",");
422                         if (!IsGroupFunction)
423                             sqlGROUP.append(sb.toString()).append(",");
424                     }
425                     pdc = new PrintDataColumn(AD_Column_ID, ColumnName, AD_Reference_ID, FieldLength, ColumnName, isPageBreak);
426                 }
427
428                 // Order Sequence - Overwrite order column name
429
for (int i = 0; i < orderAD_Column_IDs.length; i++)
430                 {
431                     if (AD_Column_ID == orderAD_Column_IDs[i])
432                     {
433                         orderColumns.set(i, orderName);
434                         break;
435                     }
436                 }
437
438                 //
439
if (pdc == null || (!IsPrinted && !IsKey))
440                     continue;
441
442                 columns.add(pdc);
443             } // for all Fields in Tab
444
rs.close();
445         }
446         catch (SQLException e)
447         {
448             log.error("getPrintDataInfo - SQL=" + sql, e);
449         }
450
451         boolean hasLevelNo = false;
452         if (tableName.startsWith("T_Report"))
453         {
454             hasLevelNo = true;
455             if (sqlSELECT.indexOf("LevelNo") == -1)
456                 sqlSELECT.append("LevelNo,");
457         }
458
459         /**
460          * Assemble final SQL - delete last SELECT ','
461          */

462         StringBuffer JavaDoc finalSQL = new StringBuffer JavaDoc();
463         finalSQL.append(sqlSELECT.substring(0, sqlSELECT.length()-1))
464             .append(sqlFROM);
465
466         // WHERE clause
467
if (tableName.startsWith("T_Report"))
468         {
469             finalSQL.append(" WHERE ");
470             for (int i = 0; i < query.getRestrictionCount(); i++)
471             {
472                 String JavaDoc q = query.getWhereClause (i);
473                 if (q.indexOf("AD_PInstance_ID") != -1) // ignore all other Parameters
474
finalSQL.append (q);
475             } // for all restrictions
476
}
477         else
478         {
479             // Access Restriction - adds WHERE clause
480
finalSQL = new StringBuffer JavaDoc (MRole.getDefault(ctx, false).addAccessSQL (
481                 finalSQL.toString (), tableName, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO));
482             // User supplied Where Clause
483
if (query != null && query.isActive ())
484             {
485                 finalSQL.append (" AND "); // will have a where from the access
486
if (!query.getTableName ().equals (tableName))
487                     query.setTableName (tableName);
488                 finalSQL.append (query.getWhereClause (true));
489             } // where clause
490
}
491
492         // Group By
493
if (IsGroupedBy)
494             finalSQL.append(sqlGROUP.substring(0, sqlGROUP.length()-1)); // last ,
495

496         // Add ORDER BY clause
497
if (orderColumns != null)
498         {
499             for (int i = 0; i < orderColumns.size(); i++)
500             {
501                 if (i == 0)
502                     finalSQL.append(" ORDER BY ");
503                 else
504                     finalSQL.append(",");
505                 String JavaDoc by = (String JavaDoc)orderColumns.get(i);
506                 if (by == null || by.length() == 0)
507                     by = String.valueOf(i+1);
508                 finalSQL.append(by);
509             }
510         } // order by
511

512         // Print Data
513
PrintData pd = new PrintData (ctx, reportName);
514         PrintDataColumn[] info = new PrintDataColumn [columns.size()];
515         columns.toArray(info); // column order is is m_synonymc with SELECT column position
516
pd.setColumnInfo(info);
517         pd.setTableName(tableName);
518         pd.setSQL(finalSQL.toString());
519         pd.setHasLevelNo(hasLevelNo);
520
521         if (Log.isTraceLevel(9))
522         {
523             log.debug (finalSQL.toString ());
524             log.debug (m_group);
525         }
526         return pd;
527     } // getPrintDataInfo
528

529     /**
530      * Next Synonym.
531      * Creates next synonym A..Z AA..ZZ AAA..ZZZ
532      */

533     private void synonymNext()
534     {
535         int length = m_synonym.length();
536         char cc = m_synonym.charAt(0);
537         if (cc == 'Z')
538         {
539             cc = 'A';
540             length++;
541         }
542         else
543             cc++;
544         //
545
m_synonym = String.valueOf(cc);
546         if (length == 1)
547             return;
548         m_synonym += String.valueOf(cc);
549         if (length == 2)
550             return;
551         m_synonym += String.valueOf(cc);
552     } // synonymNext
553

554     /**
555      * Get TableName and ColumnName for Reference Tables.
556      * @param AD_Reference_Value_ID reference value
557      * @return 0=TableName, 1=KeyColumn, 2=DisplayColumn
558      */

559     public static String JavaDoc[] getTableReference (int AD_Reference_Value_ID)
560     {
561         /** @todo Translation of Table Reference */
562         String JavaDoc retStr[] = new String JavaDoc[3];
563         //
564
String JavaDoc SQL = "SELECT t.TableName, ck.ColumnName AS KeyColumn,"
565             + " cd.ColumnName AS DisplayColumn, cd.IsTranslated "
566             + "FROM AD_Ref_Table rt"
567             + " INNER JOIN AD_Table t ON (rt.AD_Table_ID = t.AD_Table_ID)"
568             + " INNER JOIN AD_Column ck ON (rt.AD_Key = ck.AD_Column_ID)"
569             + " INNER JOIN AD_Column cd ON (rt.AD_Display = cd.AD_Column_ID) "
570             + "WHERE rt.AD_Reference_ID=?" // 1
571
+ " AND rt.IsActive = 'Y' AND t.IsActive = 'Y'";
572         try
573         {
574             PreparedStatement pstmt = DB.prepareStatement(SQL);
575             pstmt.setInt (1, AD_Reference_Value_ID);
576             ResultSet rs = pstmt.executeQuery();
577             if (rs.next())
578             {
579                 retStr[0] = rs.getString("TableName");
580                 retStr[1] = rs.getString("KeyColumn");
581                 retStr[2] = rs.getString("DisplayColumn");
582             }
583             rs.close();
584             pstmt.close();
585         }
586         catch (SQLException ex)
587         {
588             s_log.error("getTableReference", ex);
589         }
590         return retStr;
591     } // getTableReference
592

593     /*************************************************************************/
594
595     /**
596      * Load Data into PrintData
597      * @param pd print data with SQL and ColumnInfo set
598      * @param format print format
599      */

600     private void loadPrintData (PrintData pd, MPrintFormat format)
601     {
602         // Translate Spool Output
603
boolean translateSpool = pd.getTableName().equals("T_Spool");
604         PrintDataColumn pdc = null;
605         boolean hasLevelNo = pd.hasLevelNo();
606         int levelNo = 0;
607         //
608
try
609         {
610             PreparedStatement pstmt = DB.prepareStatement(pd.getSQL());
611             ResultSet rs = pstmt.executeQuery();
612             // Row Loop
613
while (rs.next())
614             {
615                 if (hasLevelNo)
616                     levelNo = rs.getInt("LevelNo");
617                 else
618                     levelNo = 0;
619                 // Check Group Change
620
if (m_group.getGroupColumnCount() > 1) // one is TOTAL
621
{
622                     for (int i = pd.getColumnInfo().length-1; i >= 0; i--) // backwards (leaset group first)
623
{
624                         PrintDataColumn group_pdc = pd.getColumnInfo()[i];
625                         if (!m_group.isGroupColumn(group_pdc.getColumnName()))
626                             continue;
627                         Object JavaDoc value = m_group.groupChange(group_pdc.getColumnName(), rs.getObject(group_pdc.getAlias()));
628                         if (value != null) // Group change
629
{
630                             char[] functions = m_group.getFunctions(group_pdc.getColumnName());
631                             for (int f = 0; f < functions.length; f++)
632                             {
633                                 pd.addRow(true, levelNo);
634                                 // get columns
635
for (int c = 0; c < pd.getColumnInfo().length; c++)
636                                 {
637                                     pdc = pd.getColumnInfo()[c];
638                                     log.debug("loadPrintData - PageBreak = " + pdc.isPageBreak());
639
640                                     if (group_pdc.getColumnName().equals(pdc.getColumnName()))
641                                     {
642                                         String JavaDoc valueString = value.toString();
643                                         if (value instanceof Timestamp)
644                                             valueString = DisplayType.getDateFormat(pdc.getDisplayType(), m_language).format(value);
645                                         valueString += PrintDataFunction.getFunctionSymbol(functions[f]);
646                                         pd.addNode(new PrintDataElement(pdc.getColumnName(),
647                                             valueString, DisplayType.String, false, pdc.isPageBreak()));
648                                     }
649                                     else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
650                                     {
651                                         pd.addNode(new PrintDataElement(pdc.getColumnName(),
652                                             m_group.getValue(group_pdc.getColumnName(), pdc.getColumnName(),
653                                                 functions[f], (f+1 == functions.length)),
654                                             PrintDataFunction.getFunctionDisplayType(functions[f]), false, pdc.isPageBreak()));
655                                     }
656                                 }
657                             } // for all functions
658
} // Group change
659
}
660                 } // group change
661

662                 // new row
663
pd.addRow(false, levelNo);
664                 int counter = 1;
665                 // get columns
666
for (int i = 0; i < pd.getColumnInfo().length; i++)
667                 {
668                     pdc = pd.getColumnInfo()[i];
669                     PrintDataElement pde = null;
670
671                     // Key Column - No DisplayColumn
672
if (pdc.getAlias().equals(KEY))
673                     {
674                         if (pdc.getColumnName().endsWith("_ID"))
675                         {
676                         // int id = rs.getInt(pdc.getColumnIDName());
677
int id = rs.getInt(counter++);
678                             if (!rs.wasNull())
679                             {
680                                 KeyNamePair pp = new KeyNamePair(id, KEY); // Key
681
pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType(), true, pdc.isPageBreak());
682                             }
683                         }
684                         else
685                         {
686                         // String id = rs.getString(pdc.getColumnIDName());
687
String JavaDoc id = rs.getString(counter++);
688                             if (!rs.wasNull())
689                             {
690                                 ValueNamePair pp = new ValueNamePair(id, KEY); // Key
691
pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType(), true, pdc.isPageBreak());
692                             }
693                         }
694                     }
695                     // Non-Key Column
696
else
697                     {
698                         // Display and Value Column
699
if (pdc.hasAlias())
700                         {
701                             // DisplayColumn first
702
String JavaDoc display = rs.getString(counter++);
703                             if (pdc.getColumnName().endsWith("_ID"))
704                             {
705                                 int id = rs.getInt(counter++);
706                                 if (display != null && !rs.wasNull())
707                                 {
708                                     KeyNamePair pp = new KeyNamePair(id, display);
709                                     pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType());
710                                 }
711                             }
712                             else
713                             {
714                                 String JavaDoc id = rs.getString(counter++);
715                                 if (display != null && !rs.wasNull())
716                                 {
717                                     ValueNamePair pp = new ValueNamePair(id, display);
718                                     pde = new PrintDataElement(pdc.getColumnName(), pp, pdc.getDisplayType());
719                                 }
720                             }
721                         }
722                         // Display Value only
723
else
724                         {
725                             // Transformation for Booleans
726
if (pdc.getDisplayType() == DisplayType.YesNo)
727                             {
728                                 String JavaDoc s = rs.getString(counter++);
729                                 if (!rs.wasNull())
730                                 {
731                                     boolean b = s.equals("Y");
732                                     pde = new PrintDataElement(pdc.getColumnName(), new Boolean JavaDoc(b), pdc.getDisplayType());
733                                 }
734                             }
735                             else
736                             // The general case
737
{
738                                 Object JavaDoc obj = rs.getObject(counter++);
739                                 if (obj != null && obj instanceof String JavaDoc)
740                                 {
741                                     obj = ((String JavaDoc)obj).trim();
742                                     if (((String JavaDoc)obj).length() == 0)
743                                         obj = null;
744                                 }
745                                 if (obj != null)
746                                 {
747                                     // Translate Spool Output
748
if (translateSpool && obj instanceof String JavaDoc)
749                                     {
750                                         String JavaDoc s = (String JavaDoc)obj;
751                                         s = Msg.parseTranslation(pd.getCtx(), s);
752                                         pde = new PrintDataElement(pdc.getColumnName(), s, pdc.getDisplayType());
753                                     }
754                                     else
755                                         pde = new PrintDataElement(pdc.getColumnName(), obj, pdc.getDisplayType());
756                                 }
757                             }
758                         } // Value only
759
} // Non-Key Column
760
if (pde != null)
761                     {
762                         pd.addNode(pde);
763                         m_group.addValue(pde.getColumnName(), pde.getFunctionValue());
764                     }
765                 } // for all columns
766

767             } // for all rows
768
rs.close();
769             pstmt.close();
770         }
771         catch (SQLException e)
772         {
773             log.error("loadPrintData " + pdc + " - " + e.getMessage() + "\nSQL=" + pd.getSQL());
774         }
775
776         // Check last Group Change
777
if (m_group.getGroupColumnCount() > 1) // one is TOTAL
778
{
779             for (int i = pd.getColumnInfo().length-1; i >= 0; i--) // backwards (leaset group first)
780
{
781                 PrintDataColumn group_pdc = pd.getColumnInfo()[i];
782                 if (!m_group.isGroupColumn(group_pdc.getColumnName()))
783                     continue;
784                 Object JavaDoc value = m_group.groupChange(group_pdc.getColumnName(), new Object JavaDoc());
785                 if (value != null) // Group change
786
{
787                     char[] functions = m_group.getFunctions(group_pdc.getColumnName());
788                     for (int f = 0; f < functions.length; f++)
789                     {
790                         pd.addRow(true, levelNo);
791                         // get columns
792
for (int c = 0; c < pd.getColumnInfo().length; c++)
793                         {
794                             pdc = pd.getColumnInfo()[c];
795                             if (group_pdc.getColumnName().equals(pdc.getColumnName()))
796                             {
797                                 String JavaDoc valueString = value.toString();
798                                 if (value instanceof Timestamp)
799                                     valueString = DisplayType.getDateFormat(pdc.getDisplayType(), m_language).format(value);
800                                 valueString += PrintDataFunction.getFunctionSymbol(functions[f]);
801                                 pd.addNode(new PrintDataElement(pdc.getColumnName(),
802                                     valueString, DisplayType.String));
803                             }
804                             else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
805                             {
806                                 pd.addNode(new PrintDataElement(pdc.getColumnName(),
807                                     m_group.getValue(group_pdc.getColumnName(), pdc.getColumnName(),
808                                         functions[f], (f+1 == functions.length)),
809                                     PrintDataFunction.getFunctionDisplayType(functions[f])));
810                             }
811                         }
812                     } // for all functions
813
} // Group change
814
}
815         } // last group change
816

817         // Add Total Line
818
if (m_group.isGroupColumn(PrintDataGroup.TOTAL))
819         {
820             char[] functions = m_group.getFunctions(PrintDataGroup.TOTAL);
821             for (int f = 0; f < functions.length; f++)
822             {
823                 pd.addRow(true, levelNo);
824                 // get columns
825
for (int c = 0; c < pd.getColumnInfo().length; c++)
826                 {
827                     pdc = pd.getColumnInfo()[c];
828                     if (c == 0) // put Function in first Column
829
{
830                         String JavaDoc name = PrintDataFunction.getFunctionSymbol(functions[f]); // Symbol
831
if (!format.getTableFormat().isPrintFunctionSymbols()) // Translate Sum, etc.
832
name = Msg.getMsg(format.getLanguage(), PrintDataFunction.getFunctionName(functions[f]));
833                         pd.addNode(new PrintDataElement(pdc.getColumnName(), name, DisplayType.String));
834                     }
835                     else if (m_group.isFunctionColumn(pdc.getColumnName(), functions[f]))
836                     {
837                         pd.addNode(new PrintDataElement(pdc.getColumnName(),
838                             m_group.getValue(PrintDataGroup.TOTAL, pdc.getColumnName(),
839                                 functions[f], (f+1 == functions.length)),
840                             PrintDataFunction.getFunctionDisplayType(functions[f])));
841                     }
842                 }
843             } // for all functions
844
} // TotalLine
845

846         if (pd.getRowCount() == 0)
847             log.warn("loadPrintData - NO Rows ms=" + (System.currentTimeMillis()-m_startTime));
848         else
849             log.info("loadPrintData - Rows=" + pd.getRowCount()
850                 + " ms=" + (System.currentTimeMillis()-m_startTime));
851     } // loadPrintData
852

853     /*************************************************************************/
854
855     /**
856      * Test
857      * @param args args
858      */

859     public static void main(String JavaDoc[] args)
860     {
861         org.compiere.Compiere.startupClient();
862
863     // DataEngine de = new DataEngine(null);
864
DataEngine de = new DataEngine(Language.getLanguage("de_DE"));
865         MQuery query = new MQuery();
866         query.addRestriction("AD_Table_ID", MQuery.LESS, 105);
867     // PrintData pd = de.load_fromTable(100, query, null, null, false);
868
// pd.dump();
869
// pd.createXML(new javax.xml.transform.stream.StreamResult(System.out));
870
}
871 }
Popular Tags