RModelData


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.report.core;
15  
16  import java.util.*;
17  import java.math.*;
18  import java.sql.*;
19  
20  import org.compiere.util.*;
21  import org.compiere.model.MRole;
22  
23  /**
24   *  Report Model Data - ValueObject.
25   *  - Build SQL from RColumn info and Retrieve Data
26   *  - owned by RModel
27   *
28   *  @author Jorg Janke
29   *  @version  $Id: RModelData.java,v 1.9 2003/11/02 07:49:20 jjanke Exp $
30   */
31  class RModelData
32  {
33      /**
34       *  Constructor. Use query method to populate data
35       *  @param TableName
36       */
37      public RModelData (String   TableName)
38      {
39          m_TableName = TableName;
40      }   //  RModelData
41  
42      /** The Rows                        */
43      public ArrayList        rows = new ArrayList();
44      /** The temporary Rows              */
45      private ArrayList       m_rows = new ArrayList();
46  
47      /** The Row MetaData                */
48      public ArrayList        rowsMeta = new ArrayList();
49      /** The Column Definitions          */
50      public ArrayList        cols = new ArrayList();
51  
52  
53      /** Table Name                      */
54      private String            m_TableName;
55  
56      /** Functions (Integer - String)    */
57      public HashMap          functions = new HashMap();
58      /** Groups (Integer)                */
59      public ArrayList        groups = new ArrayList();
60  
61      /** Array with row numbers that are groups  */
62      private ArrayList       m_groupRows = new ArrayList();
63      private ArrayList       m_groupRowsIndicator = null;
64  
65      /** Constant 1                      */
66      private static final BigDecimal     ONE = new BigDecimal(1.0);
67  
68      /**
69       *  Dispose
70       */
71      public void dispose()
72      {
73          rows.clear();
74          m_rows.clear();
75          rowsMeta.clear();
76          cols.clear();
77      }   //  dispose
78  
79      /*************************************************************************/
80  
81      /**
82       *  Query
83       *  @param ctx
84       *  @param  whereClause the SQL where clause (w/o the WHERE)
85       *  @param  orderClause
86       */
87      public void query (Properties ctx, String   whereClause, String   orderClause)
88      {
89          RColumn rc = null;
90          //  Create SQL
91          StringBuffer   sql = new StringBuffer   ("SELECT ");
92          int size = cols.size();
93          for (int i = 0; i < size; i++)
94          {
95              rc = (RColumn)cols.get(i);
96              if (i > 0)
97                  sql.append(",");
98              sql.append(rc.getColSQL());
99          }
100         sql.append(" FROM ").append(m_TableName).append(" ").append(RModel.TABLE_ALIAS);
101         if (whereClause != null && whereClause.length() > 0)
102             sql.append(" WHERE ").append(whereClause);
103         String   finalSQL = MRole.getDefault(ctx, false).addAccessSQL(
104             sql.toString(), RModel.TABLE_ALIAS, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
105         if (orderClause != null && orderClause.length() > 0)
106             finalSQL += " ORDER BY " + orderClause;
107         Log.trace(Log.l6_Database, "RModelData.query SQL=" + finalSQL);
108 
109         //  FillData
110         int index = 1;      //  rowset index
111         m_rows.clear();
112         try
113         {
114             Statement stmt = DB.createStatement();
115             ResultSet rs = stmt.executeQuery(finalSQL);
116             while (rs.next())
117             {
118                 ArrayList row = new ArrayList(size);
119                 index = 1;
120                 //  Columns
121                 for (int i = 0; i < size; i++)
122                 {
123                     rc = (RColumn)cols.get(i);
124                     //  Get ID
125                     if (rc.isIDcol())
126                         row.add(new KeyNamePair (rs.getInt(index++), rs.getString(index++)));
127                     //  Null check
128                     else if (rs.getString(index) == null)
129                     {
130                         index++;
131                         row.add(null);
132                     }
133                     else if (rc.getColClass() == String  .class)
134                         row.add(rs.getString(index++));
135                     else if (rc.getColClass() == BigDecimal.class)
136                         row.add(rs.getBigDecimal(index++));
137                     else if (rc.getColClass() == Double  .class)
138                         row.add(new Double  (rs.getDouble(index++)));
139                     else if (rc.getColClass() == Integer  .class)
140                         row.add(new Integer  (rs.getInt(index++)));
141                     else if (rc.getColClass() == Timestamp.class)
142                         row.add(rs.getTimestamp(index++));
143                     else if (rc.getColClass() == Boolean  .class)
144                         row.add(new Boolean  ("Y".equals(rs.getString(index++))));
145                     else    //  should not happen
146                     {
147                         row.add(rs.getString(index++));
148                     }
149                 }
150                 m_rows.add(row);
151             }
152             rs.close();
153             stmt.close();
154         }
155         catch (SQLException e)
156         {
157             Log.error("RModelData.query", e);
158         }
159         process();
160     }   //  query
161 
162     /**
163      *  Process Data
164      *  Copy data in m_rows to rows and perform functions
165      */
166     private void process()
167     {
168         Log.trace(Log.l5_DData, "RModelData.process - Start Rows=" + m_rows.size());
169 
170         //  Row level Funcions
171         //  would come here
172 
173         //  Group by Values
174         int gSize = groups.size();
175         int[] groupBys = new int[gSize];
176         Object  [] groupBysValue = new Object  [gSize];
177         Object   INITVALUE = new Object  ();
178         for (int i = 0; i < gSize; i++)
179         {
180             groupBys[i] = ((Integer  )groups.get(i)).intValue();
181             groupBysValue[i] = INITVALUE;
182             Log.trace(Log.l6_Database, "GroupBy level=" + i + " col=" + groupBys[i]);
183         }
184         //  Add additional row to force group change
185         if (gSize > 0)
186         {
187             ArrayList newRow = new ArrayList();
188             for (int c = 0; c < cols.size(); c++)
189                 newRow.add("");
190             m_rows.add(newRow);
191         }
192 
193         //  Function Values - Function - GroupValue
194         int fSize = functions.size();
195         int[] funcCols = new int[fSize];
196         String  [] funcFuns = new String  [fSize];
197         int index = 0;
198         Iterator it = functions.keySet().iterator();
199         while (it.hasNext())
200         {
201             Object   key = it.next();
202             funcCols[index] = ((Integer  )key).intValue();
203             funcFuns[index] = functions.get(key).toString();
204             Log.trace(Log.l6_Database, "Function " + funcFuns[index] + " col=" + funcCols[index]);
205             index++;
206         }
207         BigDecimal[][] funcVals = new BigDecimal [fSize][gSize+1];
208         int totalIndex = gSize;  //  place for overall total
209         Log.trace(Log.l6_Database, "FunctionValues = [ " + fSize + " * " + (gSize+1) + " ]");
210         for (int f = 0; f < fSize; f++)
211             for (int g = 0; g < gSize+1; g++)
212                 funcVals[f][g] = Env.ZERO;
213 
214         rows.clear();
215         //  Copy m_rows into rows
216         for (int r = 0; r < m_rows.size(); r++)
217         {
218             ArrayList row = (ArrayList)m_rows.get(r);
219             //  do we have a group break
220             boolean[] haveBreak = new boolean[groupBys.length];
221             for (int level = 0; level < groupBys.length; level ++)
222             {
223                 int idx = groupBys[level];
224                 if (groupBysValue[level] == INITVALUE)
225                     haveBreak[level] = false;
226                 else if (!groupBysValue[level].equals(row.get(idx)))
227                     haveBreak[level] = true;
228                 else
229                     haveBreak[level] = false;
230                 //  previous level had a break
231                 if (level > 0 && haveBreak[level-1])
232                     haveBreak[level] = true;
233             }
234             //  create group levels - reverse order
235             for (int level = groupBys.length-1; level >= 0; level--)
236             {
237                 int idx = groupBys[level];
238                 if (groupBysValue[level] == INITVALUE)
239                     groupBysValue[level] = row.get(idx);
240                 else if (haveBreak[level])
241                 {
242                 //  Log.trace(Log.l6_Database, "GroupBy Change level=" + level + " col=" + idx + " - " + groupBysValue[level]);
243                     //  create new row
244                     ArrayList newRow = new ArrayList();
245                     for (int c = 0; c < cols.size(); c++)
246                     {
247                         if (c == idx)   //  the group column
248                         {
249                             if (groupBysValue[c] == null || groupBysValue[c].toString().length() == 0)
250                                 newRow.add("=");
251                             else
252                                 newRow.add(groupBysValue[c]);
253                         }
254                         else
255                         {
256                             boolean found = false;
257                             for (int fc = 0; fc < funcCols.length; fc++)
258                             {
259                                 if (c == funcCols[fc])
260                                 {
261                                 //  newRow.add("fc= " + fc + " gl=" + level + " " + funcFuns[fc]);
262                                     newRow.add(funcVals[fc][level]);
263                                     funcVals[fc][level] = Env.ZERO;
264                                     found = true;
265                                 }
266                             }
267                             if (!found)
268                                 newRow.add(null);
269                         }
270                     }   //  for all columns
271                     //
272                     m_groupRows.add(new Integer  (rows.size())); //  group row indicator
273                     rows.add(newRow);
274                     groupBysValue[level] = row.get(idx);
275                 }
276             }   //  for all groups
277 
278             //  functions
279             for (int fc = 0; fc < funcCols.length; fc++)
280             {
281                 int col = funcCols[fc];
282                 //  convert value to big decimal
283                 Object   value = row.get(col);
284                 BigDecimal bd = Env.ZERO;
285                 if (value == null)
286                     ;
287                 else if (value instanceof BigDecimal)
288                     bd = (BigDecimal)value;
289                 else
290                 {
291                     try {
292                         bd = new BigDecimal(value.toString());
293                     } catch (Exception   e) { }
294                 }
295 
296                 for (int level = 0; level < gSize+1; level++)
297                 {
298                     if (funcFuns[fc].equals(RModel.FUNCTION_SUM))
299                         funcVals[fc][level] = funcVals[fc][level].add(bd);
300                     else if (funcFuns[fc].equals(RModel.FUNCTION_COUNT))
301                         funcVals[fc][level] = funcVals[fc][level].add(ONE);
302                 }   //  for all group levels
303             }   //  for all functions
304 
305             rows.add(row);
306         }   //  for all m_rows
307 
308         //  total row
309         if (functions.size() > 0)
310         {
311             ArrayList newRow = new ArrayList();
312             for (int c = 0; c < cols.size(); c++)
313             {
314                 boolean found = false;
315                 for (int fc = 0; fc < funcCols.length; fc++)
316                 {
317                     if (c == funcCols[fc])
318                     {
319                         newRow.add(funcVals[fc][totalIndex]);
320                         found = true;
321                     }
322                 }
323                 if (!found)
324                     newRow.add(null);
325             }   //  for all columns
326             //  remove empty row added earlier to force group change
327             if (gSize > 0)
328                 rows.remove(rows.size()-1);
329             m_groupRows.add(new Integer  (rows.size())); //  group row indicator
330             rows.add(newRow);
331         }
332         Log.trace(Log.l5_DData, "RModelData.process - End Rows=" + rows.size());
333         m_rows.clear();
334     }   //  process
335 
336     /*************************************************************************/
337 
338     /**
339      *  Is Row a Group Row
340      *  @param row row index
341      *  @return true, if group row
342      */
343     public boolean isGroupRow (int row)
344     {
345         //  build boolean Array
346         if (m_groupRowsIndicator == null)
347         {
348             m_groupRowsIndicator = new ArrayList(rows.size());
349             for (int r = 0; r < rows.size(); r++)
350                 m_groupRowsIndicator.add(new Boolean  (m_groupRows.contains(new Integer  (r))));
351         }
352         if (row < 0 || row >= m_groupRowsIndicator.size())
353             return false;
354         return ((Boolean  )m_groupRowsIndicator.get(row)).booleanValue();
355     }   // isGroupRow
356 
357     /**
358      *  Move Row
359      *  @param from index
360      *  @param to index
361      *  @throws IllegalArgumentException if row index is invalid
362      */
363     public void moveRow (int from, int to)
364     {
365         if (from < 0 || to >= rows.size())
366             throw new IllegalArgumentException  ("Row from invalid");
367         if (to < 0 || to >= rows.size())
368             throw new IllegalArgumentException  ("Row to invalid");
369         //  Move Data
370         Object   temp = rows.get(from);
371         rows.remove(from);
372         rows.add(to, temp);
373         //  Move Description indicator >>> m_groupRows is not in sync after row move !!
374         if (m_groupRowsIndicator != null)
375         {
376             temp = m_groupRowsIndicator.get(from);
377             m_groupRowsIndicator.remove(from);
378             m_groupRowsIndicator.add(to, temp);
379         }
380     }   //  moveRow
381 
382 }   //  RModelData
383
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags