} // 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
