Java > Open Source Codes > org > compiere > report > FinReport


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 Smart 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-2003 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/
14 package org.compiere.report;
15
16 import java.sql.*;
17 import java.util.*;
18 import java.math.*;
19
20 import org.compiere.process.*;
21 import org.compiere.model.*;
22 import org.compiere.print.*;
23 import org.compiere.util.*;
24
25 /**
26  * Financial Report Engine
27  *
28  * @author Jorg Janke
29  * @version $Id: FinReport.java,v 1.16 2003/08/25 02:31:57 jjanke Exp $
30  */
31 public class FinReport extends SvrProcess
32 {
33     /**
34      * Financial Report Constructor
35      */
36     public FinReport()
37     {
38         super();
39     } // FinReport
40
41     /** Period Parameter */
42     private int m_C_Period_ID = 0;
43     /** Org Parameter */
44     private int m_Org_ID = 0;
45     /** BPartner Parameter */
46     private int m_C_BPartner_ID = 0;
47     /** Product Parameter */
48     private int m_M_Product_ID = 0;
49     /** Project Parameter */
50     private int m_C_Project_ID = 0;
51     /** Activity Parameter */
52     private int m_C_Activity_ID = 0;
53     /** SalesRegion Parameter */
54     private int m_C_SalesRegion_ID = 0;
55     /** Campaign Parameter */
56     private int m_C_Campaign_ID = 0;
57     /** Start Time */
58     private long m_start = System.currentTimeMillis();
59
60     /** Report Definition */
61     private MReport m_report = null;
62     /** Periods in Calendar */
63     private FinReportPeriod[] m_periods = null;
64     /** Index of m_C_Period_ID in m_periods **/
65     private int m_reportPeriod = -1;
66     /** Parameter Where Clause */
67     private StringBuffer m_parameterWhere = new StringBuffer ();
68     /** The Report Columns */
69     private MReportColumn[] m_columns;
70     /** The Report Lines */
71     private MReportLine[] m_lines;
72
73
74     /**
75      * Prepare - e.g., get Parameters.
76      */
77     protected void prepare()
78     {
79         log.info("prepare");
80         StringBuffer sb = new StringBuffer ("prepare - Record_ID=")
81             .append(getRecord_ID());
82         // Parameter
83 ProcessInfoParameter[] para = getParameter();
84         for (int i = 0; i < para.length; i++)
85         {
86             String name = para[i].getParameterName();
87             if (para[i].getParameter() == null)
88                 ;
89             else if (name.equals("C_Period_ID"))
90                 m_C_Period_ID = ((BigDecimal)para[i].getParameter()).intValue();
91             else if (name.equals("Org_ID"))
92                 m_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
93             else if (name.equals("C_BPartner_ID"))
94                 m_C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue();
95             else if (name.equals("M_Product_ID"))
96                 m_M_Product_ID = ((BigDecimal)para[i].getParameter()).intValue();
97             else if (name.equals("C_Project_ID"))
98                 m_C_Project_ID = ((BigDecimal)para[i].getParameter()).intValue();
99             else if (name.equals("C_Activity_ID"))
100                 m_C_Activity_ID = ((BigDecimal)para[i].getParameter()).intValue();
101             else if (name.equals("C_SalesRegion_ID"))
102                 m_C_SalesRegion_ID = ((BigDecimal)para[i].getParameter()).intValue();
103             else if (name.equals("C_Campaign_ID"))
104                 m_C_Campaign_ID = ((BigDecimal)para[i].getParameter()).intValue();
105             else
106                 log.error("prepare - Unknown Parameter: " + name);
107         }
108         // Optional Org
109 if (m_Org_ID != 0)
110             m_parameterWhere.append(" AND AD_Org_ID=").append(m_Org_ID);
111         // Optional BPartner
112 if (m_C_BPartner_ID != 0)
113             m_parameterWhere.append(" AND C_BPartner_ID=").append(m_C_BPartner_ID);
114         // Optional Product
115 if (m_M_Product_ID != 0)
116             m_parameterWhere.append(" AND M_Product_ID=").append(m_M_Product_ID);
117         // Optional Project
118 if (m_C_Project_ID != 0)
119             m_parameterWhere.append(" AND C_Project_ID=").append(m_C_Project_ID);
120         // Optional Activity
121 if (m_C_Activity_ID != 0)
122             m_parameterWhere.append(" AND C_Activity_ID=").append(m_C_Activity_ID);
123         // Optional Campaign
124 if (m_C_Campaign_ID != 0)
125             m_parameterWhere.append(" AND C_Campaign_ID=").append(m_C_Campaign_ID);
126         // Optional Sales Region
127 if (m_C_SalesRegion_ID != 0)
128             m_parameterWhere.append(" AND C_SalesRegion_ID=").append(m_C_SalesRegion_ID);
129
130         // Load Report Definition
131 m_report = new MReport (getCtx(), getRecord_ID());
132         sb.append(" - ").append(m_report);
133         //
134 setPeriods();
135         sb.append(" - C_Period_ID=").append(m_C_Period_ID)
136             .append(" - ").append(m_parameterWhere);
137         //
138 log.info(sb.toString());
139     // m_report.list();
140 } // prepare
141
142     /**
143      * Set Periods
144      */
145     private void setPeriods()
146     {
147         log.info("setPeriods - C_Calendar_ID=" + m_report.getC_Calendar_ID());
148         Timestamp today = TimeUtil.getDay(System.currentTimeMillis());
149         ArrayList list = new ArrayList();
150
151         String sql = "SELECT p.C_Period_ID, p.Name, p.StartDate, p.EndDate, MIN(p1.StartDate) "
152             + "FROM C_Period p "
153             + " INNER JOIN C_Year y ON (p.C_Year_ID=y.C_Year_ID),"
154             + " C_Period p1 "
155             + "WHERE y.C_Calendar_ID=?"
156             + " AND p.PeriodType='S' "
157             + " AND p1.C_Year_ID=y.C_Year_ID AND p1.PeriodType='S' "
158             + "GROUP BY p.C_Period_ID, p.Name, p.StartDate, p.EndDate "
159             + "ORDER BY p.StartDate";
160
161         PreparedStatement pstmt = null;
162         try
163         {
164             pstmt = DB.prepareStatement(sql);
165             pstmt.setInt(1, m_report.getC_Calendar_ID());
166             ResultSet rs = pstmt.executeQuery();
167             while (rs.next())
168             {
169                 FinReportPeriod frp = new FinReportPeriod (rs.getInt(1), rs.getString(2),
170                     rs.getTimestamp(3), rs.getTimestamp(4), rs.getTimestamp(5));
171                 list.add(frp);
172                 if (m_C_Period_ID == 0 && frp.inPeriod(today))
173                     m_C_Period_ID = frp.getC_Period_ID();
174             }
175             rs.close();
176             pstmt.close();
177             pstmt = null;
178         }
179         catch (Exception e)
180         {
181             log.error("setPeriods", e);
182         }
183         finally
184         {
185             try
186             {
187                 if (pstmt != null)
188                     pstmt.close ();
189             }
190             catch (Exception e)
191             {}
192             pstmt = null;
193         }
194         // convert to Array
195 m_periods = new FinReportPeriod[list.size()];
196         list.toArray(m_periods);
197         // today after latest period
198 if (m_C_Period_ID == 0)
199         {
200             m_reportPeriod = m_periods.length - 1;
201             m_C_Period_ID = m_periods[m_reportPeriod].getC_Period_ID ();
202         }
203     } // setPeriods
204
205     /*************************************************************************/
206
207     /**
208      * Perform process.
209      * @return Message to be translated
210      * @throws Exception
211      */
212     protected String doIt()
213     {
214         // ** Create Temporary and empty Report Lines from PA_ReportLine
215 // - AD_PInstance_ID, PA_ReportLine_ID, 0, 0
216 int PA_ReportLineSet_ID = m_report.getLineSet().getPA_ReportLineSet_ID();
217         StringBuffer sql = new StringBuffer ("INSERT INTO T_Report "
218             + "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID, SeqNo,LevelNo, Name,Description) "
219             + "SELECT ").append(getAD_PInstance_ID()).append(", PA_ReportLine_ID, 0,0, SeqNo,0, Name,Description "
220             + "FROM PA_ReportLine "
221             + "WHERE IsActive='Y' AND PA_ReportLineSet_ID=").append(PA_ReportLineSet_ID);
222
223         int no = DB.executeUpdate(sql.toString());
224         log.debug("doIt - Report Lines = " + no);
225
226         int C_AcctSchema_ID = m_report.getC_AcctSchema_ID();
227         FinBalance.updateBalance (C_AcctSchema_ID, false);
228
229         // ** Get Data ** Segment Values
230 m_columns = m_report.getColumnSet().getColumns();
231         m_lines = m_report.getLineSet().getLiness();
232         // for all lines
233 for (int line = 0; line < m_lines.length; line++)
234         {
235             // Line Segment Value (i.e. not calculation)
236 if (m_lines[line].isLineTypeSegmentValue())
237             {
238                 // for all Columns in line with Relative Period
239 lineRelativePeriod (line);
240                 // Column Segment Value
241 lineSegmentValue (line);
242             }
243         } // for all lines
244
245         doCalculations();
246         setDetailLines();
247
248         deleteUnprintedLines();
249
250         // Create Report
251 getProcessInfo().setTransientObject (getPrintFormat());
252
253         log.debug("doIt - " + (System.currentTimeMillis() - m_start) + " ms");
254         return "";
255     } // doIt
256
257     /*************************************************************************/
258
259     /**
260      * For all columns (in a line) with relative period access
261      * @param line line
262      */
263     private void lineRelativePeriod (int line)
264     {
265         log.debug("lineRelativePeriod - " + m_lines[line]);
266
267         // No source lines
268 if (m_lines[line] == null || m_lines[line].getSources().length == 0)
269         {
270             log.warn("lineRelativePeriod - no Source lines: " + m_lines[line]);
271             return;
272         }
273
274         StringBuffer update = new StringBuffer ();
275         // for all columns
276 for (int col = 0; col < m_columns.length; col++)
277         {
278             // Only relative Period (not calculation or segment value)
279 if (!m_columns[col].isColumnTypeRelativePeriod ())
280                 continue;
281
282             // SELECT SUM()
283 StringBuffer select = new StringBuffer ("SELECT ");
284             if (m_lines[line].getAmountType() != null) // line amount type overwrites column
285 select.append (m_lines[line].getSelectClause (true));
286             else if (m_columns[col].getAmountType() != null)
287                 select.append (m_columns[col].getSelectClause (true));
288             else
289             {
290                 log.warn("lineRelativePeriod - no Amount Type in line: " + m_lines[line] + " or column: " + m_columns[col]);
291                 continue;
292             }
293
294             // Get Period info
295 select.append(" FROM Fact_Acct_Balance WHERE DateAcct ");
296             FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
297             if (m_lines[line].getAmountType() != null) // line amount type overwrites column
298 {
299                 if (m_lines[line].isPeriodBalance())
300                     select.append(frp.getPeriodBalanceWhere());
301                 else if (m_lines[line].isYearBalance())
302                     select.append(frp.getYearBalanceWhere());
303                 else
304                     select.append(frp.getTotalBalanceWhere());
305             }
306             else if (m_columns[col].getAmountType() != null)
307             {
308                 if (m_columns[col].isPeriodBalance())
309                     select.append(frp.getPeriodBalanceWhere());
310                 else if (m_columns[col].isYearBalance())
311                     select.append(frp.getYearBalanceWhere());
312                 else
313                     select.append(frp.getTotalBalanceWhere());
314             }
315             else
316                 continue;
317             // Line Where
318 String s = m_lines[line].getWhereClause();
319             if (s != null && s.length() > 0)
320                 select.append(" AND ").append(s);
321             // Report Where
322 s = m_report.getWhereClause();
323             if (s != null && s.length() > 0)
324                 select.append(" AND ").append(s);
325             // PostingType
326 String PostingType = m_columns[col].getPostingType();
327             if (PostingType != null && PostingType.length() > 0)
328                 select.append(" AND PostingType='").append(PostingType).append("'");
329             // Parameter Where
330 select.append(m_parameterWhere);
331         // System.out.println(" c=" + col + ", l=" + line + ": " + select);
332
333             // Update SET portion
334 if (update.length() > 0)
335                 update.append(", ");
336             update.append("Col_").append(col)
337                 .append(" = (").append(select).append(")");
338         }
339         // Update Line Values
340 if (update.length() > 0)
341         {
342             update.insert (0, "UPDATE T_Report SET ");
343             update.append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
344                 .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
345                 .append(" AND Record_ID=0 AND Fact_Acct_ID=0");
346             int no = DB.executeUpdate(update.toString());
347             if (no != 1)
348                 log.error("lineRelativePeriod - no=" + no + " for " + update);
349             else if (Log.isTraceLevel(10))
350                 log.debug(update);
351         }
352     } // lineRelativePeriod
353
354     /**
355      * For all columns (in a line) with segment values
356      * @param line line
357      */
358     private void lineSegmentValue (int line)
359     {
360         // for all columns
361 for (int col = 0; col < m_columns.length; col++)
362         {
363             // Only segment Values (from Column)
364 if (m_columns[col].isColumnTypeSegmentValue ())
365             {
366                 log.debug("lineSegmentValue - from column " + m_columns[col]
367                     + " - c=" + col + ", l=" + line);
368             }
369         }
370     } // lineSegmentValue
371
372     /*************************************************************************/
373
374     /**
375      * Line + Column calculation
376      */
377     private void doCalculations()
378     {
379         log.debug("doCalculations");
380
381         // for all lines ***************************************************
382 for (int line = 0; line < m_lines.length; line++)
383         {
384             if (!m_lines[line].isLineTypeCalculation ())
385                 continue;
386
387             int oper_1 = m_lines[line].getOper_1_ID();
388             int oper_2 = m_lines[line].getOper_2_ID();
389
390             // Adding
391 if (m_lines[line].isCalculationTypeAdd() || m_lines[line].isCalculationTypeRange())
392             {
393                 // Reverse range
394 if (oper_1 < oper_2)
395                 {
396                     int temp = oper_1;
397                     oper_1 = oper_2;
398                     oper_2 = temp;
399                 }
400                 StringBuffer sb = new StringBuffer ("UPDATE T_Report SET (");
401                 for (int col = 0; col < m_columns.length; col++)
402                 {
403                     if (col > 0)
404                         sb.append(",");
405                     sb.append ("Col_").append (col);
406                 }
407                 sb.append(") = (SELECT ");
408                 for (int col = 0; col < m_columns.length; col++)
409                 {
410                     if (col > 0)
411                         sb.append(",");
412                     sb.append ("COALESCE(SUM(Col_").append (col).append("),0)");
413                 }
414                 sb.append(" FROM T_Report WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
415                     .append(" AND PA_ReportLine_ID IN (");
416                 if (m_lines[line].isCalculationTypeAdd())
417                     sb.append(oper_1).append(",").append(oper_2);
418                 else
419                     sb.append(getLineIDs (oper_1, oper_2)); // list of columns to add up
420 sb.append(") AND Record_ID=0 AND Fact_Acct_ID=0) "
421                     + "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
422                     .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
423                     .append(" AND Record_ID=0 AND Fact_Acct_ID=0");
424                 int no = DB.executeUpdate(sb.toString());
425                 if (no != 1)
426                     log.error("doCalculations (+) #=" + no + " for " + m_lines[line] + " - " + sb.toString());
427                 else
428                 {
429                     log.debug ("doCalculations (lin) " + m_lines[line]);
430                     if (Log.isTraceLevel(8))
431                         log.debug ("doCalculations - " + sb.toString ());
432                 }
433             }
434             else // No Add (subtract, percent)
435 {
436                 // Step 1 - get First Value or 0 in there
437 StringBuffer sb = new StringBuffer ("UPDATE T_Report SET (");
438                 for (int col = 0; col < m_columns.length; col++)
439                 {
440                     if (col > 0)
441                         sb.append(",");
442                     sb.append ("Col_").append (col);
443                 }
444                 sb.append(") = (SELECT ");
445                 for (int col = 0; col < m_columns.length; col++)
446                 {
447                     if (col > 0)
448                         sb.append(",");
449                     sb.append ("COALESCE(r2.Col_").append (col).append(",0)");
450                 }
451                 sb.append(" FROM T_Report r2 WHERE r2.AD_PInstance_ID=").append(getAD_PInstance_ID())
452                     .append(" AND r2.PA_ReportLine_ID=").append(oper_1)
453                     .append(" AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0) "
454                 //
455 + "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
456                        .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
457                     .append(" AND Record_ID=0 AND Fact_Acct_ID=0");
458                 int no = DB.executeUpdate(sb.toString());
459                 if (no != 1)
460                 {
461                     log.error ("doCalculations (1) #=" + no + " for " + m_lines[line] + " - " + sb.toString ());
462                     continue;
463                 }
464
465                 // Step 2 - do Calculation with Second Value
466 sb = new StringBuffer ("UPDATE T_Report r1 SET (");
467                 for (int col = 0; col < m_columns.length; col++)
468                 {
469                     if (col > 0)
470                         sb.append(",");
471                     sb.append ("Col_").append (col);
472                 }
473                 sb.append(") = (SELECT ");
474                 for (int col = 0; col < m_columns.length; col++)
475                 {
476                     if (col > 0)
477                         sb.append(",");
478                     sb.append ("r1.Col_").append (col);
479                     if (m_lines[line].isCalculationTypeSubtract())
480                         sb.append("-");
481                     else
482                         sb.append("/");
483                     sb.append ("COALESCE(r2.Col_").append (col).append(",0)");
484                     if (m_lines[line].isCalculationTypePercent())
485                         sb.append(" *100");
486                 }
487                 sb.append(" FROM T_Report r2 WHERE r2.AD_PInstance_ID=").append(getAD_PInstance_ID())
488                     .append(" AND r2.PA_ReportLine_ID=").append(oper_2)
489                     .append(" AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0) "
490                 //
491 + "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
492                        .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
493                     .append(" AND Record_ID=0 AND Fact_Acct_ID=0");
494                 no = DB.executeUpdate(sb.toString());
495                 if (no != 1)
496                     log.error ("doCalculations (1) #=" + no + " for " + m_lines[line] + " - " + sb.toString ());
497                 else
498                 {
499                     log.debug ("doCalculations (lin) - " + m_lines[line]);
500                     if (Log.isTraceLevel(8))
501                         log.debug ("doCalculations - " + sb.toString());
502                 }
503             }
504         } // for all lines
505
506
507         // for all columns ***********************************************
508 for (int col = 0; col < m_columns.length; col++)
509         {
510             // Relative Period Column
511 if (!m_columns[col].isColumnTypeCalculation ())
512                 continue;
513
514             StringBuffer sb = new StringBuffer ("UPDATE T_Report SET ");
515             // Column to set
516 sb.append ("Col_").append (col).append("=");
517             // First Operand
518 int ii_1 = getColumnIndex(m_columns[col].getOper_1_ID());
519             if (ii_1 < 0)
520             {
521                 log.error("doCalculations - Column Index for Operator 1 not found - " + m_columns[col]);
522                 continue;
523             }
524             // Second Operand
525 int ii_2 = getColumnIndex(m_columns[col].getOper_2_ID());
526             if (ii_2 < 0)
527             {
528                 log.error("doCalculations - Column Index for Operator 2 not found - " + m_columns[col]);
529                 continue;
530             }
531             // Reverse Range
532 if (ii_1 > ii_2 && m_columns[col].isCalculationTypeRange())
533             {
534                 int temp = ii_1;
535                 ii_1 = ii_2;
536                 ii_2 = temp;
537             }
538
539             sb.append ("Col_").append (ii_1);
540             if (m_columns[col].isCalculationTypeAdd())
541                 sb.append("+");
542             else if (m_columns[col].isCalculationTypeSubtract())
543                 sb.append("-");
544             else if (m_columns[col].isCalculationTypePercent())
545                 sb.append("/");
546             if (m_columns[col].isCalculationTypeRange())
547             {
548                 for (int ii = ii_1+1; ii <= ii_2; ii++)
549                     sb.append("+Col_").append (ii);
550             }
551             else // single second operator
552 sb.append ("Col_").append (ii_2);
553             if (m_columns[col].isCalculationTypePercent())
554                 sb.append("*100");
555             //
556 sb.append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID()).append(" AND Record_ID=0 AND Fact_Acct_ID=0");
557             int no = DB.executeUpdate(sb.toString());
558             if (no < 1)
559                 log.error ("doCalculations #=" + no + " for " + m_columns[col] + " - " + sb.toString());
560             else
561             {
562                 log.debug ("doCalculations (col) - " + m_columns[col]);
563                 if (Log.isTraceLevel(8))
564                     log.debug ("doCalculations - " + sb.toString ());
565             }
566         } // for all columns
567
568     } // doCalculations
569
570     /**
571      * Get List of PA_ReportLine_ID from .. to
572      * @param fromID from ID
573      * @param toID to ID
574      * @return comma separated list
575      */
576     private String getLineIDs (int fromID, int toID)
577     {
578         StringBuffer sb = new StringBuffer ();
579         sb.append(fromID);
580         boolean addToList = false;
581         for (int line = 0; line < m_lines.length; line++)
582         {
583             int PA_ReportLine_ID = m_lines[line].getPA_ReportLine_ID();
584             if (addToList)
585             {
586                 sb.append (",").append (PA_ReportLine_ID);
587                 if (PA_ReportLine_ID == toID) // done
588 break;
589             }
590             else if (PA_ReportLine_ID == fromID) // from already added
591 addToList = true;
592         }
593         return sb.toString();
594     } // getLineIDs
595
596     /**
597      * Get Column Index
598      * @param PA_ReportColumn_ID PA_ReportColumn_ID
599      * @return zero based index or if not found
600      */
601     private int getColumnIndex (int PA_ReportColumn_ID)
602     {
603         for (int i = 0; i < m_columns.length; i++)
604         {
605             if (m_columns[i].getPA_ReportColumn_ID() == PA_ReportColumn_ID)
606                 return i;
607         }
608         return -1;
609     } // getColumnIndex
610
611     /*************************************************************************/
612
613     /**
614      * Get Financial Reporting Period based on reportong Period and offset.
615      * @param relativeOffset offset
616      * @return reporting period
617      */
618     private FinReportPeriod getPeriod (BigDecimal relativeOffset)
619     {
620         if (relativeOffset == null)
621             return getPeriod(0);
622         return getPeriod(relativeOffset.intValue());
623     } // getPeriod
624
625     /**
626      * Get Financial Reporting Period based on reportong Period and offset.
627      * @param relativeOffset offset
628      * @return reporting period
629      */
630     private FinReportPeriod getPeriod (int relativeOffset)
631     {
632         // find current reporting period C_Period_ID
633 if (m_reportPeriod < 0)
634         {
635             for (int i = 0; i < m_periods.length; i++)
636             {
637                 if (m_C_Period_ID == m_periods[i].getC_Period_ID())
638                 {
639                     m_reportPeriod = i;
640                     break;
641                 }
642             }
643         }
644         if (m_reportPeriod < 0 || m_reportPeriod >= m_periods.length)
645             throw new UnsupportedOperationException ("FinReport.getPeriod - Period index not found - ReportPeriod="
646                 + m_reportPeriod + ", C_Period_ID=" + m_C_Period_ID);
647
648         // Bounds check
649 int index = m_reportPeriod + relativeOffset;
650         if (index < 0)
651         {
652             log.error("getPeriod - Relative Offset(" + relativeOffset + ") not valid for selected Period(" + m_reportPeriod + ")");
653             index = 0;
654         }
655         else if (index >= m_periods.length)
656         {
657             log.error("getPeriod - Relative Offset(" + relativeOffset + ") not valid for selected Period(" + m_reportPeriod + ")");
658             index = m_periods.length - 1;
659         }
660         // Get Period
661 return m_periods[index];
662     } // getPeriod
663
664     /*************************************************************************/
665
666     /**
667      * Set Detail Lines if enabled
668      */
669     private void setDetailLines()
670     {
671         log.info("setDetailLines");
672         if (!m_report.isListSources())
673             return;
674
675         // for all source lines
676 for (int line = 0; line < m_lines.length; line++)
677         {
678             // Line Segment Value (i.e. not calculation)
679 if (m_lines[line].isLineTypeSegmentValue ())
680                 setDetailLineSource (line);
681         }
682
683         // Set SeqNo, LevelNo
684 StringBuffer sql = new StringBuffer ("UPDATE T_Report r1 "
685             + "SET (SeqNo,LevelNo) = (SELECT SeqNo, CASE Fact_Acct_ID WHEN 0 THEN 1 ELSE 2 END "
686             + "FROM T_Report r2 "
687             + "WHERE r1.AD_PInstance_ID=r2.AD_PInstance_ID AND r1.PA_ReportLine_ID=r2.PA_ReportLine_ID"
688             + " AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0)"
689             + "WHERE SeqNo IS NULL");
690         int no = DB.executeUpdate(sql.toString());
691         log.debug ("getDetailLines - SeqNo #=" + no);
692
693         if (!m_report.isListTrx())
694             return;
695
696         // Set Name,Description
697 String sql_select = "SELECT e.Name, fa.Description "
698             + "FROM Fact_Acct fa"
699             + " INNER JOIN AD_Table t ON (fa.AD_Table_ID=t.AD_Table_ID)"
700             + " INNER JOIN AD_Element e ON (t.TableName||'_ID'=e.ColumnName) "
701             + "WHERE r.Fact_Acct_ID=fa.Fact_Acct_ID";
702         // Translated Version ...
703 sql = new StringBuffer ("UPDATE T_Report r SET (Name,Description)=(")
704             .append(sql_select).append(") "
705             + "WHERE Fact_Acct_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID());
706         no = DB.executeUpdate(sql.toString());
707         if (Log.isTraceLevel(10))
708             log.debug("setDetailLines - Trx Name #=" + no + " - " + sql.toString());
709     } // setDetailLines
710
711     /**
712      * Create Detail Line per Source.
713      * For all columns (in a line) with relative period access
714      * - AD_PInstance_ID, PA_ReportLine_ID, variable, 0
715      * @param line line
716      */
717     private void setDetailLineSource (int line)
718     {
719         log.info("setDetailLineSource - Line=" + line + " - " + m_lines[line]);
720
721         // No source lines
722 if (m_lines[line] == null || m_lines[line].getSources().length == 0)
723             return;
724         String variable = m_lines[line].getSourceColumnName();
725         if (variable == null)
726             return;
727         log.debug("setDetailLineSeource - Variable=" + variable);
728
729         // Insert
730 StringBuffer insert = new StringBuffer ("INSERT INTO T_Report "
731             + "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID ");
732         for (int col = 0; col < m_columns.length; col++)
733             insert.append(",Col_").append(col);
734         // Select
735 insert.append(") SELECT ")
736             .append(getAD_PInstance_ID()).append(",")
737             .append(m_lines[line].getPA_ReportLine_ID()).append(",")
738             .append(variable).append(",0");
739
740         // for all columns create select statement
741 for (int col = 0; col < m_columns.length; col++)
742         {
743             insert.append(", ");
744             // Only relative Period (not calculation or segment value)
745 if (!m_columns[col].isColumnTypeRelativePeriod ())
746             {
747                 insert.append("NULL");
748                 continue;
749             }
750
751             // SELECT SUM()
752 StringBuffer select = new StringBuffer ("SELECT ");
753             if (m_lines[line].getAmountType() != null) // line amount type overwrites column
754 select.append (m_lines[line].getSelectClause (true));
755             else if (m_columns[col].getAmountType() != null)
756                 select.append (m_columns[col].getSelectClause (true));
757             else
758             {
759                 insert.append("NULL");
760                 continue;
761             }
762
763             // Get Period info
764 select.append(" FROM Fact_Acct_Balance fb WHERE DateAcct ");
765             FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
766             if (m_lines[line].getAmountType() != null) // line amount type overwrites column
767 {
768                 if (m_lines[line].isPeriodBalance())
769                     select.append(frp.getPeriodBalanceWhere());
770                 else if (m_lines[line].isYearBalance())
771                     select.append(frp.getYearBalanceWhere());
772                 else
773                     select.append(frp.getTotalBalanceWhere());
774             }
775             else if (m_columns[col].getAmountType() != null)
776             {
777                 if (m_columns[col].isPeriodBalance())
778                     select.append(frp.getPeriodBalanceWhere());
779                 else if (m_columns[col].isYearBalance())
780                     select.append(frp.getYearBalanceWhere());
781                 else
782                     select.append(frp.getTotalBalanceWhere());
783             }
784             // Link
785 select.append(" AND fb.").append(variable).append("=x.").append(variable);
786             // PostingType
787 String PostingType = m_columns[col].getPostingType();
788             if (PostingType != null && PostingType.length() > 0)
789                 select.append(" AND fb.PostingType='").append(PostingType).append("'");
790             // Report Where
791 String s = m_report.getWhereClause();
792             if (s != null && s.length() > 0)
793                 select.append(" AND ").append(s);
794             // Parameter Where
795 select.append(m_parameterWhere);
796         // System.out.println(" c=" + col + ", l=" + line + ": " + select);
797 //
798 insert.append("(").append(select).append(")");
799         }
800         //
801 insert.append(" FROM Fact_Acct_Balance x WHERE ")
802             .append(m_lines[line].getWhereClause());
803         String s = m_report.getWhereClause();
804         if (s != null && s.length() > 0)
805             insert.append(" AND ").append(s);
806         insert.append(m_parameterWhere)
807             .append(" GROUP BY ").append(variable);
808
809         int no = DB.executeUpdate(insert.toString());
810         if (Log.isTraceLevel(10))
811             log.debug("Insert #=" + no + " - " + insert);
812         if (no == 0)
813             return;
814
815         // Set Name,Description
816 StringBuffer sql = new StringBuffer ("UPDATE T_Report SET (Name,Description)=(")
817             .append(m_lines[line].getSourceValueQuery()).append("Record_ID) "
818             //
819 + "WHERE Record_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID())
820             .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
821             .append(" AND Fact_Acct_ID=0");
822         no = DB.executeUpdate(sql.toString());
823         if (Log.isTraceLevel(10))
824             log.debug("Name #=" + no + " - " + sql.toString());
825
826         if (m_report.isListTrx())
827             setDetailLineTrx (line, variable);
828     } // setDetailLineSource
829
830     /**
831      * Create Trx Line per Source Detail.
832      * - AD_PInstance_ID, PA_ReportLine_ID, variable, Fact_Acct_ID
833      * @param line line
834      * @param variable variable, e.g. Account_ID
835      */
836     private void setDetailLineTrx (int line, String variable)
837     {
838         log.info("setDetailLineTrx - Line=" + line + " - Variable=" + variable);
839
840         // Insert
841 StringBuffer insert = new StringBuffer ("INSERT INTO T_Report "
842             + "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID ");
843         for (int col = 0; col < m_columns.length; col++)
844             insert.append(",Col_").append(col);
845         // Select
846 insert.append(") SELECT ")
847             .append(getAD_PInstance_ID()).append(",")
848             .append(m_lines[line].getPA_ReportLine_ID()).append(",")
849             .append(variable).append(",Fact_Acct_ID");
850
851         // for all columns create select statement
852 for (int col = 0; col < m_columns.length; col++)
853         {
854             insert.append(", ");
855             // Only relative Period (not calculation or segment value)
856 if (!(m_columns[col].isColumnTypeRelativePeriod() && m_columns[col].getRelativePeriodAsInt() == 0))
857             {
858                 insert.append("NULL");
859                 continue;
860             }
861             // Amount Type ... Qty
862 if (m_lines[line].getAmountType() != null) // line amount type overwrites column
863 insert.append (m_lines[line].getSelectClause (false));
864             else if (m_columns[col].getAmountType() != null)
865                 insert.append (m_columns[col].getSelectClause (false));
866             else
867             {
868                 insert.append("NULL");
869                 continue;
870             }
871         }
872         //
873 insert.append(" FROM Fact_Acct WHERE ")
874             .append(m_lines[line].getWhereClause());
875         // Report Where
876 String s = m_report.getWhereClause();
877         if (s != null && s.length() > 0)
878             insert.append(" AND ").append(s);
879         // Period restriction
880 FinReportPeriod frp = getPeriod (0);
881         insert.append(" AND DateAcct ")
882             .append(frp.getPeriodBalanceWhere());
883         // PostingType ??
884 // String PostingType = m_columns[col].getPostingType();
885 // if (PostingType != null && PostingType.length() > 0)
886 // insert.append(" AND PostingType='").append(PostingType).append("'");
887
888         int no = DB.executeUpdate(insert.toString());
889         if (Log.isTraceLevel(8))
890             log.debug("Insert #=" + no + " - " + insert);
891         if (no == 0)
892             return;
893     } // setDetailLineTrx
894
895     /*************************************************************************/
896
897     /**
898      * Delete Unprinted Lines
899      */
900     private void deleteUnprintedLines()
901     {
902         for (int line = 0; line < m_lines.length; line++)
903         {
904             // Not Printed - Delete in T
905 if (!m_lines[line].isPrinted())
906             {
907                 String sql = "DELETE FROM T_Report WHERE AD_PInstance_ID=" + getAD_PInstance_ID()
908                     + " AND PA_ReportLine_ID=" + m_lines[line].getPA_ReportLine_ID();
909                 int no = DB.executeUpdate(sql);
910                 if (no > 0)
911                     log.debug("deleteUnprintedLines " + m_lines[line].getName() + " - #" + no);
912             }
913         } // for all lines
914 } // deleteUnprintedLines
915
916     /*************************************************************************/
917
918     /**
919      * Get/Create PrintFormat
920      * @return print format
921      */
922     private MPrintFormat getPrintFormat()
923     {
924         int AD_PrintFormat_ID = m_report.getAD_PrintFormat_ID();
925         log.info("getPrintFormat - ID=" + AD_PrintFormat_ID);
926         MPrintFormat pf = null;
927         boolean createNew = AD_PrintFormat_ID == 0;
928
929         // Create New
930 if (createNew)
931         {
932             int AD_Table_ID = 544; // T_Report
933 pf = MPrintFormat.createFromTable(Env.getCtx(), AD_Table_ID);
934             AD_PrintFormat_ID = pf.getAD_PrintFormat_ID();
935             m_report.setAD_PrintFormat_ID(AD_PrintFormat_ID);
936             m_report.save();
937         }
938         else
939             pf = MPrintFormat.get (AD_PrintFormat_ID, false); // use Cache
940
941         // Print Format Sync
942 if (!m_report.getName().equals(pf.getName()))
943             pf.setName(m_report.getName());
944         if (m_report.getDescription() == null)
945         {
946             if (pf.getDescription () != null)
947                 pf.setDescription (null);
948         }
949         else if (!m_report.getDescription().equals(pf.getDescription()))
950             pf.setDescription(m_report.getDescription());
951         pf.save();
952         log.debug("getPrintFormat - " + pf + " - #" + pf.getItemCount());
953
954         // Print Format Item Sync
955 int count = pf.getItemCount();
956         for (int i = 0; i < count; i++)
957         {
958             MPrintFormatItem pfi = pf.getItem(i);
959             String ColumnName = pfi.getColumnName();
960             //
961 if (ColumnName == null)
962             {
963                 log.error("getPrintFormat no ColumnName for #" + i + " - " + pfi);
964                 if (pfi.isPrinted())
965                     pfi.setIsPrinted(false);
966                 if (pfi.isOrderBy())
967                     pfi.setIsOrderBy(false);
968                 if (pfi.getSortNo() != 0)
969                     pfi.setSortNo(0);
970             }
971             else if (ColumnName.startsWith("Col"))
972             {
973                 int index = Integer.parseInt(ColumnName.substring(4));
974                 if (index < m_columns.length)
975                 {
976                     pfi.setIsPrinted(m_columns[index].isPrinted());
977                     String s = m_columns[index].getName();
978                     if (!pfi.getName().equals(s))
979                     {
980                         pfi.setName (s);
981                         pfi.setPrintName (s);
982                     }
983                     int seq = 30 + index;
984                     if (pfi.getSeqNo() != seq)
985                         pfi.setSeqNo(seq);
986                 }
987                 else // not printed
988 {
989                     if (pfi.isPrinted())
990                         pfi.setIsPrinted(false);
991                 }
992                 // Not Sorted
993 if (pfi.isOrderBy())
994                     pfi.setIsOrderBy(false);
995                 if (pfi.getSortNo() != 0)
996                     pfi.setSortNo(0);
997             }
998             else if (ColumnName.equals("SeqNo"))
999             {
1000                if (pfi.isPrinted())
1001                    pfi.setIsPrinted(false);
1002                if (!pfi.isOrderBy())
1003                    pfi.setIsOrderBy(true);
1004                if (pfi.getSortNo() != 10)
1005                    pfi.setSortNo(10);
1006            }
1007            else if (ColumnName.equals("LevelNo"))
1008            {
1009                if (pfi.isPrinted())
1010                    pfi.setIsPrinted(false);
1011                if (!pfi.isOrderBy())
1012                    pfi.setIsOrderBy(true);
1013                if (pfi.getSortNo() != 20)
1014                    pfi.setSortNo(20);
1015            }
1016            else if (ColumnName.equals("Name"))
1017            {
1018                if (pfi.getSeqNo() != 10)
1019                    pfi.setSeqNo(10);
1020                if (!pfi.isPrinted())
1021                    pfi.setIsPrinted(true);
1022                if (!pfi.isOrderBy())
1023                    pfi.setIsOrderBy(true);
1024                if (pfi.getSortNo() != 30)
1025                    pfi.setSortNo(30);
1026            }
1027            else if (ColumnName.equals("Description"))
1028            {
1029                if (pfi.getSeqNo() != 20)
1030                    pfi.setSeqNo(20);
1031                if (!pfi.isPrinted())
1032                    pfi.setIsPrinted(true);
1033                if (pfi.isOrderBy())
1034                    pfi.setIsOrderBy(false);
1035                if (pfi.getSortNo() != 0)
1036                    pfi.setSortNo(0);
1037            }
1038            else // Not Printed, No Sort
1039 {
1040                if (pfi.isPrinted())
1041                    pfi.setIsPrinted(false);
1042                if (pfi.isOrderBy())
1043                    pfi.setIsOrderBy(false);
1044                if (pfi.getSortNo() != 0)
1045                    pfi.setSortNo(0);
1046            }
1047            pfi.save();
1048            log.debug("getPrintFormat - " + pfi);
1049        }
1050        // set translated to original
1051 pf.setTranslation();
1052        // First one is unsorted - just re-load
1053 if (createNew)
1054            pf = MPrintFormat.get (AD_PrintFormat_ID, false); // use Cache
1055 return pf;
1056    } // getPrintFormat
1057
1058} // FinReport
1059

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags