finreport.java

Java写的ERP系统

				setDetailLineSource (line);
		}

		//	Set SeqNo, LevelNo
		StringBuffer sql = new StringBuffer ("UPDATE T_Report r1 "
			+ "SET (SeqNo,LevelNo) = (SELECT SeqNo, CASE Fact_Acct_ID WHEN 0 THEN 1 ELSE 2 END "
			+ "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());
		log.debug ("getDetailLines - 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());
		no = DB.executeUpdate(sql.toString());
		if (Log.isTraceLevel(10))
			log.debug("setDetailLines - Trx Name #=" + no + " - " + sql.toString());
	}	//	setDetailLines

	/**
	 * 	Create Detail Line per Source.
	 * 	For all columns (in a line) with relative period access
	 * 	- AD_PInstance_ID, PA_ReportLine_ID, variable, 0
	 * 	@param line line
	 */
	private void setDetailLineSource (int line)
	{
		log.info("setDetailLineSource - Line=" + line + " - " + m_lines[line]);

		//	No source lines
		if (m_lines[line] == null || m_lines[line].getSources().length == 0)
			return;
		String variable = m_lines[line].getSourceColumnName();
		if (variable == null)
			return;
		log.debug("setDetailLineSeource - Variable=" + variable);

		//	Insert
		StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
			+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID ");
		for (int col = 0; col < m_columns.length; col++)
			insert.append(",Col_").append(col);
		//	Select
		insert.append(") SELECT ")
			.append(getAD_PInstance_ID()).append(",")
			.append(m_lines[line].getPA_ReportLine_ID()).append(",")
			.append(variable).append(",0");

		//	for all columns create select statement
		for (int col = 0; col < m_columns.length; col++)
		{
			insert.append(", ");
			//	Only relative Period (not calculation or segment value)
			if (!m_columns[col].isColumnTypeRelativePeriod ())
			{
				insert.append("NULL");
				continue;
			}

			//	SELECT SUM()
			StringBuffer select = new StringBuffer ("SELECT ");
			if (m_lines[line].getAmountType() != null)				//	line amount type overwrites column
				select.append (m_lines[line].getSelectClause (true));
			else if (m_columns[col].getAmountType() != null)
				select.append (m_columns[col].getSelectClause (true));
			else
			{
				insert.append("NULL");
				continue;
			}

			//	Get Period info
			select.append(" FROM Fact_Acct_Balance fb WHERE DateAcct ");
			FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
			if (m_lines[line].getAmountType() != null)			//	line amount type overwrites column
			{
				if (m_lines[line].isPeriodBalance())
					select.append(frp.getPeriodBalanceWhere());
				else if (m_lines[line].isYearBalance())
					select.append(frp.getYearBalanceWhere());
				else
					select.append(frp.getTotalBalanceWhere());
			}
			else if (m_columns[col].getAmountType() != null)
			{
				if (m_columns[col].isPeriodBalance())
					select.append(frp.getPeriodBalanceWhere());
				else if (m_columns[col].isYearBalance())
					select.append(frp.getYearBalanceWhere());
				else
					select.append(frp.getTotalBalanceWhere());
			}
			//	Link
			select.append(" AND fb.").append(variable).append("=x.").append(variable);
			String s = m_report.getWhereClause();
			if (s != null && s.length() > 0)
				select.append(" AND ").append(s);
			select.append(m_parameterWhere);
		//	System.out.println("    c=" + col + ", l=" + line + ": " + select);
			//
			insert.append("(").append(select).append(")");
		}
		//
		insert.append(" FROM Fact_Acct_Balance x WHERE ")
			.append(m_lines[line].getWhereClause());
		String s = m_report.getWhereClause();
		if (s != null && s.length() > 0)
			insert.append(" AND ").append(s);
		insert.append(m_parameterWhere)
			.append(" GROUP BY ").append(variable);

		int no = DB.executeUpdate(insert.toString());
		if (Log.isTraceLevel(10))
			log.debug("Insert #=" + no + " - " + insert);
		if (no == 0)
			return;

		//	Set Name,Description
		StringBuffer sql = new StringBuffer ("UPDATE T_Report SET (Name,Description)=(")
			.append(m_lines[line].getSourceValueQuery()).append("Record_ID) "
			//
			+ "WHERE Record_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID())
			.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
			.append(" AND Fact_Acct_ID=0");
		no = DB.executeUpdate(sql.toString());
		if (Log.isTraceLevel(10))
			log.debug("Name #=" + no + " - " + sql.toString());

		if (m_report.isListTrx())
			setDetailLineTrx (line, variable);
	}	//	setDetailLineSource

	/**
	 * 	Create Trx Line per Source Detail.
	 * 	- AD_PInstance_ID, PA_ReportLine_ID, variable, Fact_Acct_ID
	 * 	@param line line
	 * 	@param variable variable, e.g. Account_ID
	 */
	private void setDetailLineTrx (int line, String variable)
	{
		log.info("setDetailLineTrx - Line=" + line + " - Variable=" + variable);

		//	Insert
		StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
			+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID ");
		for (int col = 0; col < m_columns.length; col++)
			insert.append(",Col_").append(col);
		//	Select
		insert.append(") SELECT ")
			.append(getAD_PInstance_ID()).append(",")
			.append(m_lines[line].getPA_ReportLine_ID()).append(",")
			.append(variable).append(",Fact_Acct_ID");

		//	for all columns create select statement
		for (int col = 0; col < m_columns.length; col++)
		{
			insert.append(", ");
			//	Only relative Period (not calculation or segment value)
			if (!(m_columns[col].isColumnTypeRelativePeriod() && m_columns[col].getRelativePeriod() == 0))
			{
				insert.append("NULL");
				continue;
			}
			//	Amount Type ... Qty
			if (m_lines[line].getAmountType() != null)				//	line amount type overwrites column
				insert.append (m_lines[line].getSelectClause (false));
			else if (m_columns[col].getAmountType() != null)
				insert.append (m_columns[col].getSelectClause (false));
			else
			{
				insert.append("NULL");
				continue;
			}
		}
		//
		insert.append(" FROM Fact_Acct WHERE ")
			.append(m_lines[line].getWhereClause());
		String s = m_report.getWhereClause();
		if (s != null && s.length() > 0)
			insert.append(" AND ").append(s);
		//
		FinReportPeriod frp = getPeriod (0);
		insert.append(" AND DateAcct ")
			.append(frp.getPeriodBalanceWhere());

		int no = DB.executeUpdate(insert.toString());
		if (Log.isTraceLevel(8))
			log.debug("Insert #=" + no + " - " + insert);
		if (no == 0)
			return;
	}	//	setDetailLineTrx

	/*************************************************************************/

	/**
	 *	Get/Create PrintFormat
	 * 	@return print format
	 */
	private MPrintFormat getPrintFormat()
	{
		int AD_PrintFormat_ID = m_report.getAD_PrintFormat_ID();
		log.info("getPrintFormat - ID=" + AD_PrintFormat_ID);
		MPrintFormat pf = null;

		//	Create New
		if (AD_PrintFormat_ID == 0)
		{
			int AD_Table_ID = 544;		//	T_Report
			pf = MPrintFormat.createFromTable(Env.getCtx(), AD_Table_ID);
			m_report.setAD_PrintFormat_ID(pf.getID());
			m_report.save();
		}
		else
			pf = MPrintFormat.get (AD_PrintFormat_ID, false);	//	use Cache

		//	Print Format Sync
		if (!m_report.getName().equals(pf.getName()))
			pf.setName(m_report.getName());
		if (m_report.getDescription() == null)
		{
			if (pf.getDescription () != null)
				pf.setDescription (null);
		}
		else if (!m_report.getDescription().equals(pf.getDescription()))
			pf.setDescription(m_report.getDescription());
		pf.save();
		log.debug("getPrintFormat - " + pf);

		//	Print Format Item Sync
		int count = pf.getItemCount();
		for (int i = 0; i < count; i++)
		{
			MPrintFormatItem pfi = pf.getItem(i);
			String ColumnName = pfi.getColumnName();
			//
			if (ColumnName.startsWith("Col"))
			{
				int index = Integer.parseInt(ColumnName.substring(4));
				if (index < m_columns.length)
				{
					if (!pfi.isPrinted())
						pfi.setPrinted(true);
					String s = m_columns[index].getName();
					if (!pfi.getName().equals(s))
					{
						pfi.setName (s);
						pfi.setPrintName (s);
					}
					int seq = 30 + index;
					if (pfi.getSeqNo() != seq)
						pfi.setSeqNo(seq);
				}
				else	//	not printed
				{
					if (pfi.isPrinted())
						pfi.setPrinted(false);
				}
				//	Not Sorted
				if (pfi.isOrderBy())
					pfi.setOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			else if (ColumnName.equals("SeqNo"))
			{
				if (pfi.isPrinted())
					pfi.setPrinted(false);
				if (!pfi.isOrderBy())
					pfi.setOrderBy(true);
				if (pfi.getSortNo() != 10)
					pfi.setSortNo(10);
			}
			else if (ColumnName.equals("LevelNo"))
			{
				if (pfi.isPrinted())
					pfi.setPrinted(false);
				if (!pfi.isOrderBy())
					pfi.setOrderBy(true);
				if (pfi.getSortNo() != 20)
					pfi.setSortNo(20);
			}
			else if (ColumnName.equals("Name"))
			{
				if (pfi.getSeqNo() != 10)
					pfi.setSeqNo(10);
				if (!pfi.isPrinted())
					pfi.setPrinted(true);
				if (!pfi.isOrderBy())
					pfi.setOrderBy(true);
				if (pfi.getSortNo() != 30)
					pfi.setSortNo(30);
			}
			else if (ColumnName.equals("Description"))
			{
				if (pfi.getSeqNo() != 20)
					pfi.setSeqNo(20);
				if (!pfi.isPrinted())
					pfi.setPrinted(true);
				if (pfi.isOrderBy())
					pfi.setOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			else	//	Not Printed, No Sort
			{
				if (pfi.isPrinted())
					pfi.setPrinted(false);
				if (pfi.isOrderBy())
					pfi.setOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			pfi.save();
			log.debug("getPrintFormat - " + pfi);
		}
		//	set translated to original
		pf.setTranslation();
		return pf;
	}	//	getPrintFormat

}	//	FinReport