} // getWhereClause /// <summary> /// Get Child IDs /// </summary> /// <param name="ctx">context</param> /// <param name="PA_Hierarchy_ID">optional hierarchie</param> /// <param name="ElementType">Account Schema Element Type</param> /// <param name="ID">id</param> /// <returns>array of IDs</returns> public static int?[] GetChildIDs(Ctx ctx, int PA_Hierarchy_ID, String ElementType, int ID) { MReportTree tree = Get(ctx, PA_Hierarchy_ID, ElementType); return(tree.GetChildIDs(ID)); } // getChildIDs
} // get /// <summary> /// Get Where Clause /// </summary> /// <param name="ctx">context</param> /// <param name="PA_Hierarchy_ID">optional hierarchy</param> /// <param name="ElementType">Account Schema Element Type</param> /// <param name="ID">leaf element id</param> /// <returns>where clause</returns> public static String GetWhereClause(Ctx ctx, int PA_Hierarchy_ID, String ElementType, int ID) { MReportTree tree = Get(ctx, PA_Hierarchy_ID, ElementType); return(tree.GetWhereClause(ID)); } // getWhereClause
/// <summary> /// Get Report Tree (cached) /// </summary> /// <param name="ctx">context</param> /// <param name="PA_Hierarchy_ID">optional hierarchy</param> /// <param name="ElementType">Account Schema Element Type</param> /// <returns>tree</returns> public static MReportTree Get(Ctx ctx, int PA_Hierarchy_ID, String ElementType) { String key = PA_Hierarchy_ID + ElementType; MReportTree tree = (MReportTree)s_trees[key];//.get(key); if (tree == null) { tree = new MReportTree(ctx, PA_Hierarchy_ID, ElementType); //s_trees.put(key, tree); s_trees.Add(key, tree); } return(tree); } // get
private long _start = CommonFunctions.CurrentTimeMillis(); //System.currentTimeMillis(); /// <summary> /// Prepare - e.g., get Parameters. /// </summary> protected override void Prepare() { StringBuilder sb = new StringBuilder("Record_ID=") .Append(GetRecord_ID()); // Parameter ProcessInfoParameter[] para = GetParameter(); for (int i = 0; i < para.Length; i++) { String name = para[i].GetParameterName(); if (para[i].GetParameter() == null) { ; } else if (name.Equals("C_AcctSchema_ID")) { _C_AcctSchema_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter()); } else if (name.Equals("PostingType")) { _PostingType = (String)para[i].GetParameter(); } else if (name.Equals("C_Period_ID")) { _C_Period_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter()); } else if (name.Equals("DateAcct")) { _DateAcct_From = Utility.Util.GetValueOfDateTime(para[i].GetParameter()); _DateAcct_To = Utility.Util.GetValueOfDateTime(para[i].GetParameter_To()); } else if (name.Equals("PA_Hierarchy_ID")) { _PA_Hierarchy_ID = para[i].GetParameterAsInt(); } else if (name.Equals("AD_Org_ID")) { _AD_Org_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter()); } else if (name.Equals("Account_ID")) { _Account_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter()); } else if (name.Equals("C_BPartner_ID")) { _C_BPartner_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("M_Product_ID")) { _M_Product_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("C_Project_ID")) { _C_Project_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("C_Activity_ID")) { _C_Activity_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("C_SalesRegion_ID")) { _C_SalesRegion_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("C_Campaign_ID")) { _C_Campaign_ID = Utility.Util.GetValueOfInt((Decimal)para[i].GetParameter());//.intValue(); } else if (name.Equals("UpdateBalances")) { _UpdateBalances = "Y".Equals(para[i].GetParameter()); } else { log.Log(Level.SEVERE, "Unknown Parameter: " + name); } } // Mandatory C_AcctSchema_ID, PostingType _parameterWhere.Append("C_AcctSchema_ID=").Append(_C_AcctSchema_ID) .Append(" AND PostingType='").Append(_PostingType).Append("'"); // Optional Account_ID if (_Account_ID != 0 && _Account_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Account, _Account_ID)); } // Optional Org if (_AD_Org_ID != 0 && _AD_Org_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Organization, _AD_Org_ID)); } // Optional BPartner if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_BPartner, _C_BPartner_ID)); } // Optional Product if (_M_Product_ID != 0 && _M_Product_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Product, _M_Product_ID)); } // Optional Project if (_C_Project_ID != 0 && _C_Project_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Project, _C_Project_ID)); } // Optional Activity if (_C_Activity_ID != 0 && _C_Activity_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Activity, _C_Activity_ID)); } // Optional Campaign if (_C_Campaign_ID != 0 && _C_Campaign_ID != -1) { _parameterWhere.Append(" AND C_Campaign_ID=").Append(_C_Campaign_ID); } // _parameterWhere.append(" AND ").append(MReportTree.getWhereClause(getCtx(), // MAcctSchemaElement.ELEMENTTYPE_Campaign, _C_Campaign_ID)); // Optional Sales Region if (_C_SalesRegion_ID != 0 && _C_SalesRegion_ID != -1) { _parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_SalesRegion, _C_SalesRegion_ID)); } // SetDateAcct(); sb.Append(" - DateAcct ").Append(_DateAcct_From).Append("-").Append(_DateAcct_To); sb.Append(" - Where=").Append(_parameterWhere); log.Fine(sb.ToString()); } // prepare
/// <summary> /// Prepare - e.g., get Parameters. /// </summary> protected override void Prepare() { StringBuilder sb = new StringBuilder("AD_PInstance_ID=") .Append(GetAD_PInstance_ID()); // Parameter ProcessInfoParameter[] para = GetParameter(); for (int i = 0; i < para.Length; i++) { String name = para[i].GetParameterName(); if (para[i].GetParameter() == null) { ; } else if (name.Equals("C_AcctSchema_ID")) { _C_AcctSchema_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("C_Period_ID")) { _C_Period_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("DateAcct")) { _DateAcct_From = Utility.Util.GetValueOfDateTime(para[i].GetParameter()); _DateAcct_To = Utility.Util.GetValueOfDateTime(para[i].GetParameter_To()); } else if (name.Equals("PA_Hierarchy_ID")) { _PA_Hierarchy_ID = para[i].GetParameterAsInt(); } else if (name.Equals("AD_Org_ID")) { _AD_Org_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("Account_ID")) { _Account_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("AccountValue")) { _AccountValue_From = Convert.ToString(para[i].GetParameter()); _AccountValue_To = Convert.ToString(para[i].GetParameter_To()); } else if (name.Equals("C_BPartner_ID")) { _C_BPartner_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("M_Product_ID")) { _M_Product_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("C_Project_ID")) { _C_Project_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("C_Activity_ID")) { _C_Activity_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("C_SalesRegion_ID")) { _C_SalesRegion_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("C_Campaign_ID")) { _C_Campaign_ID = Utility.Util.GetValueOfInt(para[i].GetParameter()); } else if (name.Equals("PostingType")) { _PostingType = (String)para[i].GetParameter(); } else { log.Log(Level.SEVERE, "Unknown Parameter: " + name); } } // Mandatory C_AcctSchema_ID m_parameterWhere.Append("C_AcctSchema_ID=").Append(_C_AcctSchema_ID); // Optional Account_ID if (_Account_ID != 0 && _Account_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Account, _Account_ID)); } if (_AccountValue_From != null && _AccountValue_From.Length == 0) { _AccountValue_From = null; } if (_AccountValue_To != null && _AccountValue_To.Length == 0) { _AccountValue_To = null; } if (_AccountValue_From != null && _AccountValue_To != null) { m_parameterWhere.Append(" AND (Account_ID IS NULL OR EXISTS (SELECT * FROM C_ElementValue ev ") .Append("WHERE Account_ID=ev.C_ElementValue_ID AND ev.Value >= ") .Append(DataBase.DB.TO_STRING(_AccountValue_From)).Append(" AND ev.Value <= ") .Append(DataBase.DB.TO_STRING(_AccountValue_To)).Append("))"); } else if (_AccountValue_From != null && _AccountValue_To == null) { m_parameterWhere.Append(" AND (Account_ID IS NULL OR EXISTS (SELECT * FROM C_ElementValue ev ") .Append("WHERE Account_ID=ev.C_ElementValue_ID AND ev.Value >= ") .Append(DataBase.DB.TO_STRING(_AccountValue_From)).Append("))"); } else if (_AccountValue_From == null && _AccountValue_To != null) { m_parameterWhere.Append(" AND (Account_ID IS NULL OR EXISTS (SELECT * FROM C_ElementValue ev ") .Append("WHERE Account_ID=ev.C_ElementValue_ID AND ev.Value <= ") .Append(DataBase.DB.TO_STRING(_AccountValue_To)).Append("))"); } // Optional Org if (_AD_Org_ID != 0 && _AD_Org_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Organization, _AD_Org_ID)); } // Optional BPartner if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_BPartner, _C_BPartner_ID)); } // Optional Product if (_M_Product_ID != 0 && _M_Product_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Product, _M_Product_ID)); } // Optional Project if (_C_Project_ID != 0 && _C_Project_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Project, _C_Project_ID)); } // Optional Activity if (_C_Activity_ID != 0 && _C_Activity_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Activity, _C_Activity_ID)); } // Optional Campaign if (_C_Campaign_ID != 0 && _C_Campaign_ID != -1) { m_parameterWhere.Append(" AND C_Campaign_ID=").Append(_C_Campaign_ID); } // m_parameterWhere.append(" AND ").append(MReportTree.getWhereClause(getCtx(), // MAcctSchemaElement.ELEMENTTYPE_Campaign, _C_Campaign_ID)); // Optional Sales Region if (_C_SalesRegion_ID != 0 && _C_SalesRegion_ID != -1) { m_parameterWhere.Append(" AND ").Append(MReportTree.GetWhereClause(GetCtx(), _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_SalesRegion, _C_SalesRegion_ID)); } // Mandatory Posting Type m_parameterWhere.Append(" AND PostingType='").Append(_PostingType).Append("'"); // SetDateAcct(); sb.Append(" - DateAcct ").Append(_DateAcct_From).Append("-").Append(_DateAcct_To); sb.Append(" - Where=").Append(m_parameterWhere); log.Fine(sb.ToString()); } // prepare
} // MReportSource /// <summary> /// Get SQL where clause /// </summary> /// <param name="PA_Hierarchy_ID">hierarchy </param> /// <returns> where clause</returns> public String GetWhereClause(int PA_Hierarchy_ID) { String et = GetElementType(); // ID for Tree Leaf Value int ID = 0; // if (MAcctSchemaElement.ELEMENTTYPE_Account.Equals(et)) { ID = GetC_ElementValue_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_Activity.Equals(et)) { ID = GetC_Activity_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_BPartner.Equals(et)) { ID = GetC_BPartner_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_Campaign.Equals(et)) { ID = GetC_Campaign_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_LocationFrom.Equals(et)) { ID = GetC_Location_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_LocationTo.Equals(et)) { ID = GetC_Location_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_Organization.Equals(et)) { ID = GetOrg_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_Product.Equals(et)) { ID = GetM_Product_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_Project.Equals(et)) { ID = GetC_Project_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_SalesRegion.Equals(et)) { ID = GetC_SalesRegion_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_OrgTrx.Equals(et)) { ID = GetOrg_ID(); // (re)uses Org_ID } else if (MAcctSchemaElement.ELEMENTTYPE_UserList1.Equals(et)) { ID = GetC_ElementValue_ID(); } else if (MAcctSchemaElement.ELEMENTTYPE_UserList2.Equals(et)) { ID = GetC_ElementValue_ID(); } // else if (MAcctSchemaElement.ELEMENTTYPE_UserElement1.equals(et)) // ID = getUserElement1_ID (); // else if (MAcctSchemaElement.ELEMENTTYPE_UserElement2.equals(et)) // ID = getUserElement2_ID (); if (ID == 0) { log.Fine("No Restrictions - No ID for EntityType=" + et); return(""); } // return(MReportTree.GetWhereClause(GetCtx(), PA_Hierarchy_ID, et, ID)); } // getWhereClause
} // isTotalBalance /// <summary> /// Get Segment Value Where Clause /// </summary> /// <param name="PA_Hierarchy_ID"> hierarchy</param> /// <returns> where clause</returns> public String GetWhereClause(int PA_Hierarchy_ID) { if (!IsColumnTypeSegmentValue()) { return(""); } String et = GetElementType(); int ID = 0; if (MReportColumn.ELEMENTTYPE_Organization.Equals(et)) { ID = GetOrg_ID(); } else if (MReportColumn.ELEMENTTYPE_BPartner.Equals(et)) { ID = GetC_BPartner_ID(); } else if (MReportColumn.ELEMENTTYPE_Product.Equals(et)) { ID = GetM_Product_ID(); } else if (MReportColumn.ELEMENTTYPE_Project.Equals(et)) { ID = GetC_Project_ID(); } else if (MReportColumn.ELEMENTTYPE_Activity.Equals(et)) { ID = GetC_Activity_ID(); } else if (MReportColumn.ELEMENTTYPE_Campaign.Equals(et)) { ID = GetC_Campaign_ID(); } else if (MReportColumn.ELEMENTTYPE_LocationFrom.Equals(et)) { ID = GetC_Location_ID(); } else if (MReportColumn.ELEMENTTYPE_LocationTo.Equals(et)) { ID = GetC_Location_ID(); } else if (MReportColumn.ELEMENTTYPE_OrgTrx.Equals(et)) { ID = GetOrg_ID(); } else if (MReportColumn.ELEMENTTYPE_SalesRegion.Equals(et)) { ID = GetC_SalesRegion_ID(); } else if (MReportColumn.ELEMENTTYPE_Account.Equals(et)) { ID = GetC_ElementValue_ID(); } else if (MReportColumn.ELEMENTTYPE_UserList1.Equals(et)) { ID = GetC_ElementValue_ID(); } else if (MReportColumn.ELEMENTTYPE_UserList2.Equals(et)) { ID = GetC_ElementValue_ID(); } // else if (MReportColumn.ELEMENTTYPE_UserElement1.equals(et)) // ID = getC_ElementValue_ID(); // else if (MReportColumn.ELEMENTTYPE_UserElement2.equals(et)) // ID = getC_ElementValue_ID(); else { log.Warning("Unsupported Element Type=" + et); } if (ID == 0) { log.Fine("No Restrictions - No ID for EntityType=" + et); return(""); } return(" AND " + MReportTree.GetWhereClause(GetCtx(), PA_Hierarchy_ID, et, ID)); } // getWhereClause