finreport.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (CLogMgt.isLevelFinest())
			log.fine("Trx Name #=" + no + " - " + sql.toString());
	}	//	insertLineDetail

	/**
	 * 	Insert Detail Line per Source.
	 * 	For all columns (in a line) with relative period access
	 * 	- AD_PInstance_ID, PA_ReportLine_ID, variable, 0 - Level 1
	 * 	@param line line
	 */
	private void insertLineSource (int line)
	{
		log.info("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.fine("Variable=" + variable);

		//	Insert
		StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
			+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID,LevelNo ");
		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,");
		if (p_DetailsSourceFirst)
			insert.append("-1 ");
		else
			insert.append("1 ");

		//	for all columns create select statement
		for (int col = 0; col < m_columns.length; col++)
		{
			insert.append(", ");
			//	No calculation
			if (m_columns[col].isColumnTypeCalculation())
			{
				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].isPeriod())
					select.append(frp.getPeriodWhere());
				else if (m_lines[line].isYear())
					select.append(frp.getYearWhere());
				else
					select.append(frp.getTotalWhere());
			}
			else if (m_columns[col].getAmountType() != null)
			{
				if (m_columns[col].isPeriod())
					select.append(frp.getPeriodWhere());
				else if (m_columns[col].isYear())
					select.append(frp.getYearWhere());
				else
					select.append(frp.getTotalWhere());
			}
			//	Link
			select.append(" AND fb.").append(variable).append("=x.").append(variable);
			//	PostingType
			if (!m_lines[line].isPostingType())		//	only if not defined on line
			{
				String PostingType = m_columns[col].getPostingType();
				if (PostingType != null && PostingType.length() > 0)
					select.append(" AND fb.PostingType='").append(PostingType).append("'");
			}
			//	Report Where
			String s = m_report.getWhereClause();
			if (s != null && s.length() > 0)
				select.append(" AND ").append(s);
			//	Limited Segment Values
			if (m_columns[col].isColumnTypeSegmentValue())
			{
				String elementType = m_columns[col].getElementType();
				if (MReportColumn.ELEMENTTYPE_Organization.equals(elementType))
					select.append(" AND AD_Org_ID=").append(m_columns[col].getOrg_ID());
				else if (MReportColumn.ELEMENTTYPE_BPartner.equals(elementType))
					select.append(" AND C_BPartner_ID=").append(m_columns[col].getC_BPartner_ID());
				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);
		//	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(p_PA_Hierarchy_ID));	//	(sources, posting type)
		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(), get_TrxName());
		if (CLogMgt.isLevelFinest())
			log.fine("Source #=" + 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(), get_TrxName());
		if (CLogMgt.isLevelFinest())
			log.fine("Name #=" + no + " - " + sql.toString());

		if (m_report.isListTrx())
			insertLineTrx (line, variable);
	}	//	insertLineSource

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

		//	Insert
		StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
			+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID,LevelNo ");
		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, ");
		if (p_DetailsSourceFirst)
			insert.append("-2 ");
		else
			insert.append("2 ");

		//	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].getRelativePeriodAsInt() == 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(p_PA_Hierarchy_ID));	//	(sources, posting type)
		//	Report Where
		String s = m_report.getWhereClause();
		if (s != null && s.length() > 0)
			insert.append(" AND ").append(s);
		//	Period restriction
		FinReportPeriod frp = getPeriod (0);
		insert.append(" AND DateAcct ")
			.append(frp.getPeriodWhere());
		//	PostingType ??
//		if (!m_lines[line].isPostingType())		//	only if not defined on line
//		{
//	      String PostingType = m_columns[col].getPostingType();
//  	    if (PostingType != null && PostingType.length() > 0)
//      	  	insert.append(" AND PostingType='").append(PostingType).append("'");
//		}

		int no = DB.executeUpdate(insert.toString(), get_TrxName());
		log.finest("Trx #=" + no + " - " + insert);
		if (no == 0)
			return;
	}	//	insertLineTrx

	
	/**************************************************************************
	 *	Delete Unprinted Lines
	 */
	private void deleteUnprintedLines()
	{
		for (int line = 0; line < m_lines.length; line++)
		{
			//	Not Printed - Delete in T
			if (!m_lines[line].isPrinted())
			{
				String sql = "DELETE FROM T_Report WHERE AD_PInstance_ID=" + getAD_PInstance_ID()
					+ " AND PA_ReportLine_ID=" + m_lines[line].getPA_ReportLine_ID();
				int no = DB.executeUpdate(sql, get_TrxName());
				if (no > 0)
					log.fine(m_lines[line].getName() + " - #" + no);
			}
		}	//	for all lines
	}	//	deleteUnprintedLines

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

		//	Create New
		if (createNew)
		{
			int AD_Table_ID = 544;		//	T_Report
			pf = MPrintFormat.createFromTable(Env.getCtx(), AD_Table_ID);
			AD_PrintFormat_ID = pf.getAD_PrintFormat_ID();
			m_report.setAD_PrintFormat_ID(AD_PrintFormat_ID);
			m_report.save();
		}
		else
			pf = MPrintFormat.get (getCtx(), 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.fine(pf + " - #" + pf.getItemCount());

		//	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 == null)
			{
				log.log(Level.SEVERE, "No ColumnName for #" + i + " - " + pfi);
				if (pfi.isPrinted())
					pfi.setIsPrinted(false);
				if (pfi.isOrderBy())
					pfi.setIsOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			else if (ColumnName.startsWith("Col"))
			{
				int index = Integer.parseInt(ColumnName.substring(4));
				if (index < m_columns.length)
				{
					pfi.setIsPrinted(m_columns[index].isPrinted());
					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.setIsPrinted(false);
				}
				//	Not Sorted
				if (pfi.isOrderBy())
					pfi.setIsOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			else if (ColumnName.equals("SeqNo"))
			{
				if (pfi.isPrinted())
					pfi.setIsPrinted(false);
				if (!pfi.isOrderBy())
					pfi.setIsOrderBy(true);
				if (pfi.getSortNo() != 10)
					pfi.setSortNo(10);
			}
			else if (ColumnName.equals("LevelNo"))
			{
				if (pfi.isPrinted())
					pfi.setIsPrinted(false);
				if (!pfi.isOrderBy())
					pfi.setIsOrderBy(true);
				if (pfi.getSortNo() != 20)
					pfi.setSortNo(20);
			}
			else if (ColumnName.equals("Name"))
			{
				if (pfi.getSeqNo() != 10)
					pfi.setSeqNo(10);
				if (!pfi.isPrinted())
					pfi.setIsPrinted(true);
				if (!pfi.isOrderBy())
					pfi.setIsOrderBy(true);
				if (pfi.getSortNo() != 30)
					pfi.setSortNo(30);
			}
			else if (ColumnName.equals("Description"))
			{
				if (pfi.getSeqNo() != 20)
					pfi.setSeqNo(20);
				if (!pfi.isPrinted())
					pfi.setIsPrinted(true);
				if (pfi.isOrderBy())
					pfi.setIsOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			else	//	Not Printed, No Sort
			{
				if (pfi.isPrinted())
					pfi.setIsPrinted(false);
				if (pfi.isOrderBy())
					pfi.setIsOrderBy(false);
				if (pfi.getSortNo() != 0)
					pfi.setSortNo(0);
			}
			pfi.save();
			log.fine(pfi.toString());
		}
		//	set translated to original
		pf.setTranslation();
		//	First one is unsorted - just re-load
		if (createNew)
			pf = MPrintFormat.get (getCtx(), AD_PrintFormat_ID, false);	//	use Cache
		return pf;
	}	//	getPrintFormat

}	//	FinReport