📄 finreport.java
字号:
else if (MReportColumn.ELEMENTTYPE_Product.equals(elementType))
select.append(" AND M_Product_ID=").append(m_columns[col].getM_Product_ID());
else if (MReportColumn.ELEMENTTYPE_Project.equals(elementType))
select.append(" AND C_Project_ID=").append(m_columns[col].getC_Project_ID());
else if (MReportColumn.ELEMENTTYPE_Activity.equals(elementType))
select.append(" AND C_Activity_ID=").append(m_columns[col].getC_Activity_ID());
else if (MReportColumn.ELEMENTTYPE_Campaign.equals(elementType))
select.append(" AND C_Campaign_ID=").append(m_columns[col].getC_Campaign_ID());
else if (MReportColumn.ELEMENTTYPE_LocationFrom.equals(elementType))
select.append(" AND C_LocFrom_ID=").append(m_columns[col].getC_Location_ID());
else if (MReportColumn.ELEMENTTYPE_LocationTo.equals(elementType))
select.append(" AND C_LocTo_ID=").append(m_columns[col].getC_Location_ID());
else if (MReportColumn.ELEMENTTYPE_OrgTrx.equals(elementType))
select.append(" AND AD_OrgTrx_ID=").append(m_columns[col].getOrg_ID());
else if (MReportColumn.ELEMENTTYPE_SalesRegion.equals(elementType))
select.append(" AND C_SalesRegion_ID=").append(m_columns[col].getC_SalesRegion_ID());
else if (MReportColumn.ELEMENTTYPE_Account.equals(elementType))
select.append(" AND Account_ID=").append(m_columns[col].getC_ElementValue_ID());
else if (MReportColumn.ELEMENTTYPE_UserList1.equals(elementType))
select.append(" AND User1_ID=").append(m_columns[col].getC_ElementValue_ID());
else if (MReportColumn.ELEMENTTYPE_UserList2.equals(elementType))
select.append(" AND User2_ID=").append(m_columns[col].getC_ElementValue_ID());
else if (MReportColumn.ELEMENTTYPE_UserElement1.equals(elementType))
select.append(" AND UserElement1_ID=").append(m_columns[col].getC_ElementValue_ID());
else if (MReportColumn.ELEMENTTYPE_UserElement2.equals(elementType))
select.append(" AND UserElement2_ID=").append(m_columns[col].getC_ElementValue_ID());
}
// Parameter Where
select.append(m_parameterWhere);
log.finest("Line=" + line + ",Col=" + line + ": " + select);
// Update SET portion
if (update.length() > 0)
update.append(", ");
update.append("Col_").append(col)
.append(" = (").append(select).append(")");
//
log.finest(info.toString());
}
// Update Line Values
if (update.length() > 0)
{
update.insert (0, "UPDATE T_Report SET ");
update.append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
.append(" AND ABS(LevelNo)<2"); // 0=Line 1=Acct
int no = DB.executeUpdate(update.toString(), get_TrxName());
if (no != 1)
log.log(Level.SEVERE, "#=" + no + " for " + update);
log.finest(update.toString());
}
} // insertLine
/**************************************************************************
* Line + Column calculation
*/
private void doCalculations()
{
// for all lines ***************************************************
for (int line = 0; line < m_lines.length; line++)
{
if (!m_lines[line].isLineTypeCalculation ())
continue;
int oper_1 = m_lines[line].getOper_1_ID();
int oper_2 = m_lines[line].getOper_2_ID();
log.fine("Line " + line + " = #" + oper_1 + " "
+ m_lines[line].getCalculationType() + " #" + oper_2);
// Adding
if (m_lines[line].isCalculationTypeAdd()
|| m_lines[line].isCalculationTypeRange())
{
// Reverse range
if (oper_1 > oper_2)
{
int temp = oper_1;
oper_1 = oper_2;
oper_2 = temp;
}
StringBuffer sb = new StringBuffer ("UPDATE T_Report SET (");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("Col_").append (col);
}
sb.append(") = (SELECT ");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("COALESCE(SUM(Col_").append (col).append("),0)");
}
sb.append(" FROM T_Report WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID IN (");
if (m_lines[line].isCalculationTypeAdd())
sb.append(oper_1).append(",").append(oper_2);
else
sb.append(getLineIDs (oper_1, oper_2)); // list of columns to add up
sb.append(") AND ABS(LevelNo)<1) " // 0=Line 1=Acct
+ "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
.append(" AND ABS(LevelNo)<1"); // not trx
int no = DB.executeUpdate(sb.toString(), get_TrxName());
if (no != 1)
log.log(Level.SEVERE, "(+) #=" + no + " for " + m_lines[line] + " - " + sb.toString());
else
{
log.fine("(+) Line=" + line + " - " + m_lines[line]);
log.finest ("(+) " + sb.toString ());
}
}
else // No Add (subtract, percent)
{
// Step 1 - get First Value or 0 in there
StringBuffer sb = new StringBuffer ("UPDATE T_Report SET (");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("Col_").append (col);
}
sb.append(") = (SELECT ");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("COALESCE(r2.Col_").append (col).append(",0)");
}
sb.append(" FROM T_Report r2 WHERE r2.AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND r2.PA_ReportLine_ID=").append(oper_1)
.append(" AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0) "
//
+ "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
.append(" AND ABS(LevelNo)<1"); // 0=Line 1=Acct
int no = DB.executeUpdate(sb.toString(), get_TrxName());
if (no != 1)
{
log.severe ("(x) #=" + no + " for " + m_lines[line] + " - " + sb.toString ());
continue;
}
// Step 2 - do Calculation with Second Value
sb = new StringBuffer ("UPDATE T_Report r1 SET (");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("Col_").append (col);
}
sb.append(") = (SELECT ");
for (int col = 0; col < m_columns.length; col++)
{
if (col > 0)
sb.append(",");
sb.append ("COALESCE(r1.Col_").append (col).append(",0)");
if (m_lines[line].isCalculationTypeSubtract())
sb.append("-");
else
sb.append("/");
sb.append ("COALESCE(r2.Col_").append (col).append(",0.000000001)");
if (m_lines[line].isCalculationTypePercent())
sb.append(" *100");
}
sb.append(" FROM T_Report r2 WHERE r2.AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND r2.PA_ReportLine_ID=").append(oper_2)
.append(" AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0) "
//
+ "WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
.append(" AND ABS(LevelNo)<1"); // 0=Line 1=Acct
no = DB.executeUpdate(sb.toString(), get_TrxName());
if (no != 1)
log.severe ("(x) #=" + no + " for " + m_lines[line] + " - " + sb.toString ());
else
{
log.fine("(x) Line=" + line + " - " + m_lines[line]);
log.finest (sb.toString());
}
}
} // for all lines
// for all columns ***********************************************
for (int col = 0; col < m_columns.length; col++)
{
// Only Calculations
if (!m_columns[col].isColumnTypeCalculation ())
continue;
StringBuffer sb = new StringBuffer ("UPDATE T_Report SET ");
// Column to set
sb.append ("Col_").append (col).append("=");
// First Operand
int ii_1 = getColumnIndex(m_columns[col].getOper_1_ID());
if (ii_1 < 0)
{
log.log(Level.SEVERE, "Column Index for Operator 1 not found - " + m_columns[col]);
continue;
}
// Second Operand
int ii_2 = getColumnIndex(m_columns[col].getOper_2_ID());
if (ii_2 < 0)
{
log.log(Level.SEVERE, "Column Index for Operator 2 not found - " + m_columns[col]);
continue;
}
log.fine("Column " + col + " = #" + ii_1 + " "
+ m_columns[col].getCalculationType() + " #" + ii_2);
// Reverse Range
if (ii_1 > ii_2 && m_columns[col].isCalculationTypeRange())
{
log.fine("Swap operands from " + ii_1 + " op " + ii_2);
int temp = ii_1;
ii_1 = ii_2;
ii_2 = temp;
}
// +
if (m_columns[col].isCalculationTypeAdd())
sb.append ("COALESCE(Col_").append (ii_1).append(",0)")
.append("+")
.append ("COALESCE(Col_").append (ii_2).append(",0)");
// -
else if (m_columns[col].isCalculationTypeSubtract())
sb.append ("COALESCE(Col_").append (ii_1).append(",0)")
.append("-")
.append ("COALESCE(Col_").append (ii_2).append(",0)");
// /
if (m_columns[col].isCalculationTypePercent())
sb.append ("CASE WHEN COALESCE(Col_").append(ii_2)
.append(",0)=0 THEN NULL ELSE ")
.append("COALESCE(Col_").append (ii_1).append(",0)")
.append("/")
.append ("Col_").append (ii_2)
.append("*100 END"); // Zero Divide
// Range
else if (m_columns[col].isCalculationTypeRange())
{
sb.append ("COALESCE(Col_").append (ii_1).append(",0)");
for (int ii = ii_1+1; ii <= ii_2; ii++)
sb.append("+COALESCE(Col_").append (ii).append(",0)");
}
//
sb.append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND ABS(LevelNo)<2"); // 0=Line 1=Acct
int no = DB.executeUpdate(sb.toString(), get_TrxName());
if (no < 1)
log.severe ("#=" + no + " for " + m_columns[col]
+ " - " + sb.toString());
else
{
log.fine("Col=" + col + " - " + m_columns[col]);
log.finest (sb.toString ());
}
} // for all columns
} // doCalculations
/**
* Get List of PA_ReportLine_ID from .. to
* @param fromID from ID
* @param toID to ID
* @return comma separated list
*/
private String getLineIDs (int fromID, int toID)
{
log.finest("From=" + fromID + " To=" + toID);
StringBuffer sb = new StringBuffer();
sb.append(fromID);
boolean addToList = false;
for (int line = 0; line < m_lines.length; line++)
{
int PA_ReportLine_ID = m_lines[line].getPA_ReportLine_ID();
log.finest("Add=" + addToList
+ " ID=" + PA_ReportLine_ID + " - " + m_lines[line]);
if (addToList)
{
sb.append (",").append (PA_ReportLine_ID);
if (PA_ReportLine_ID == toID) // done
break;
}
else if (PA_ReportLine_ID == fromID) // from already added
addToList = true;
}
return sb.toString();
} // getLineIDs
/**
* Get Column Index
* @param PA_ReportColumn_ID PA_ReportColumn_ID
* @return zero based index or if not found
*/
private int getColumnIndex (int PA_ReportColumn_ID)
{
for (int i = 0; i < m_columns.length; i++)
{
if (m_columns[i].getPA_ReportColumn_ID() == PA_ReportColumn_ID)
return i;
}
return -1;
} // getColumnIndex
/**************************************************************************
* Get Financial Reporting Period based on reportong Period and offset.
* @param relativeOffset offset
* @return reporting period
*/
private FinReportPeriod getPeriod (BigDecimal relativeOffset)
{
if (relativeOffset == null)
return getPeriod(0);
return getPeriod(relativeOffset.intValue());
} // getPeriod
/**
* Get Financial Reporting Period based on reporting Period and offset.
* @param relativeOffset offset
* @return reporting period
*/
private FinReportPeriod getPeriod (int relativeOffset)
{
// find current reporting period C_Period_ID
if (m_reportPeriod < 0)
{
for (int i = 0; i < m_periods.length; i++)
{
if (p_C_Period_ID == m_periods[i].getC_Period_ID())
{
m_reportPeriod = i;
break;
}
}
}
if (m_reportPeriod < 0 || m_reportPeriod >= m_periods.length)
throw new UnsupportedOperationException ("Period index not found - ReportPeriod="
+ m_reportPeriod + ", C_Period_ID=" + p_C_Period_ID);
// Bounds check
int index = m_reportPeriod + relativeOffset;
if (index < 0)
{
log.log(Level.SEVERE, "Relative Offset(" + relativeOffset
+ ") not valid for selected Period(" + m_reportPeriod + ")");
index = 0;
}
else if (index >= m_periods.length)
{
log.log(Level.SEVERE, "Relative Offset(" + relativeOffset
+ ") not valid for selected Period(" + m_reportPeriod + ")");
index = m_periods.length - 1;
}
// Get Period
return m_periods[index];
} // getPeriod
/**************************************************************************
* Insert Detail Lines if enabled
*/
private void insertLineDetail()
{
if (!m_report.isListSources())
return;
log.info("");
// for all source lines
for (int line = 0; line < m_lines.length; line++)
{
// Line Segment Value (i.e. not calculation)
if (m_lines[line].isLineTypeSegmentValue ())
insertLineSource (line);
}
// Set SeqNo
StringBuffer sql = new StringBuffer ("UPDATE T_Report r1 "
+ "SET SeqNo = (SELECT SeqNo "
+ "FROM T_Report r2 "
+ "WHERE r1.AD_PInstance_ID=r2.AD_PInstance_ID AND r1.PA_ReportLine_ID=r2.PA_ReportLine_ID"
+ " AND r2.Record_ID=0 AND r2.Fact_Acct_ID=0)"
+ "WHERE SeqNo IS NULL");
int no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("SeqNo #=" + no);
if (!m_report.isListTrx())
return;
// Set Name,Description
String sql_select = "SELECT e.Name, fa.Description "
+ "FROM Fact_Acct fa"
+ " INNER JOIN AD_Table t ON (fa.AD_Table_ID=t.AD_Table_ID)"
+ " INNER JOIN AD_Element e ON (t.TableName||'_ID'=e.ColumnName) "
+ "WHERE r.Fact_Acct_ID=fa.Fact_Acct_ID";
// Translated Version ...
sql = new StringBuffer ("UPDATE T_Report r SET (Name,Description)=(")
.append(sql_select).append(") "
+ "WHERE Fact_Acct_ID <> 0 AND AD_PInstance_ID=")
.append(getAD_PInstance_ID());
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -