/// <summary>
/// Get Sql to return single value for the Performance Indicator
/// </summary>
/// <param name="restrictions">array of goal restrictions</param>
/// <param name="measureScope">scope of this value</param>
/// <param name="measureDataType">data type</param>
/// <param name="reportDate">optional report date</param>
/// <param name="role">role</param>
/// <returns>sql for performance indicator</returns>
public String GetSqlPI(MGoalRestriction[] restrictions,
String measureScope, String measureDataType, DateTime?reportDate, MRole role)
{
String dateColumn = "Created";
String orgColumn = "AD_Org_ID";
String bpColumn = "C_BPartner_ID";
String pColumn = null;
// PlannedAmt -> PlannedQty -> Count
StringBuilder sb = new StringBuilder("SELECT COALESCE(SUM(PlannedAmt),COALESCE(SUM(PlannedQty),COUNT(*))) "
+ "FROM C_Project WHERE C_ProjectType_ID=" + GetC_ProjectType_ID()
+ " AND Processed<>'Y')");
// Date Restriction
if (MMeasure.MEASUREDATATYPE_QtyAmountInTime.Equals(measureDataType) &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureScope))
{
if (reportDate == null)
{
reportDate = DateTime.Now;
}
String dateString = DataBase.DB.TO_DATE((DateTime?)reportDate);
String trunc = "D";
if (MGoal.MEASUREDISPLAY_Year.Equals(measureScope))
{
trunc = "Y";
}
else if (MGoal.MEASUREDISPLAY_Quarter.Equals(measureScope))
{
trunc = "Q";
}
else if (MGoal.MEASUREDISPLAY_Month.Equals(measureScope))
{
trunc = "MM";
}
else if (MGoal.MEASUREDISPLAY_Week.Equals(measureScope))
{
trunc = "W";
}
// else if (MGoal.MEASUREDISPLAY_Day.equals(MeasureDisplay))
// ;
sb.Append(" AND TRUNC(")
.Append(dateColumn).Append(",'").Append(trunc).Append("')=TRUNC(")
.Append(DataBase.DB.TO_DATE((DateTime?)reportDate)).Append(",'").Append(trunc).Append("')");
} // date
//
String sql = MMeasureCalc.AddRestrictions(sb.ToString(), false, restrictions, role,
"C_Project", orgColumn, bpColumn, pColumn, GetCtx());
log.Fine(sql);
return(sql);
}
/// <summary>
/// Get Zoom Query
/// </summary>
/// <param name="restrictions">restrictions</param>
/// <param name="measureDisplay">display</param>
/// <param name="date">date</param>
/// <param name="C_Phase_ID">phase</param>
/// <param name="role">role</param>
/// <returns>query</returns>
public Query GetQuery(MGoalRestriction[] restrictions,
String measureDisplay, DateTime?date, int C_Phase_ID, MRole role)
{
String dateColumn = "Created";
String orgColumn = "AD_Org_ID";
String bpColumn = "C_BPartner_ID";
String pColumn = null;
//
Query query = new Query("C_Project");
query.AddRangeRestriction("C_ProjectType_ID", "=", GetC_ProjectType_ID());
//
String where = null;
if (C_Phase_ID != 0)
{
where = "C_Phase_ID=" + C_Phase_ID;
}
else
{
String trunc = "D";
if (MGoal.MEASUREDISPLAY_Year.Equals(measureDisplay))
{
trunc = "Y";
}
else if (MGoal.MEASUREDISPLAY_Quarter.Equals(measureDisplay))
{
trunc = "Q";
}
else if (MGoal.MEASUREDISPLAY_Month.Equals(measureDisplay))
{
trunc = "MM";
}
else if (MGoal.MEASUREDISPLAY_Week.Equals(measureDisplay))
{
trunc = "W";
}
// else if (MGoal.MEASUREDISPLAY_Day.equals(measureDisplay))
// trunc = "D";
where = "TRUNC(" + dateColumn + ",'" + trunc
+ "')=TRUNC(" + DataBase.DB.TO_DATE(date) + ",'" + trunc + "')";
}
String sql = MMeasureCalc.AddRestrictions(where + " AND Processed<>'Y' ",
true, restrictions, role,
"C_Project", orgColumn, bpColumn, pColumn, GetCtx());
query.AddRestriction(sql);
query.SetRecordCount(1);
return(query);
}
/// <summary>
/// Get Sql to value for the bar chart
/// </summary>
/// <param name="restrictions">array of goal restrictions</param>
/// <param name="measureDisplay">scope of this value</param>
/// <param name="measureDataType">data type</param>
/// <param name="startDate">optional report start date</param>
/// <param name="role">role</param>
/// <returns>sql for Bar Chart</returns>
public String GetSqlBarChart(MGoalRestriction[] restrictions, String measureDisplay,
String measureDataType, DateTime?startDate, MRole role)
{
String dateColumn = "Created";
String orgColumn = "AD_Org_ID";
String bpColumn = "C_BPartner_ID";
String pColumn = null;
//
StringBuilder sb = new StringBuilder("SELECT COALESCE(SUM(PlannedAmt),COALESCE(SUM(PlannedQty),COUNT(*))), ");
String orderBy = null;
String groupBy = null;
//
if (MMeasure.MEASUREDATATYPE_QtyAmountInTime.Equals(measureDataType) &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String trunc = "D";
if (MGoal.MEASUREDISPLAY_Year.Equals(measureDisplay))
{
trunc = "Y";
}
else if (MGoal.MEASUREDISPLAY_Quarter.Equals(measureDisplay))
{
trunc = "Q";
}
else if (MGoal.MEASUREDISPLAY_Month.Equals(measureDisplay))
{
trunc = "MM";
}
else if (MGoal.MEASUREDISPLAY_Week.Equals(measureDisplay))
{
trunc = "W";
}
// else if (MGoal.MEASUREDISPLAY_Day.equals(measureDisplay))
// ;
orderBy = "TRUNC(" + dateColumn + ",'" + trunc + "')";
groupBy = orderBy + ", 0 ";
sb.Append(groupBy)
.Append("FROM C_Project ");
}
else
{
orderBy = "p.SeqNo";
groupBy = "COALESCE(p.Name,TO_NCHAR('-')), p.C_Phase_ID, p.SeqNo ";
sb.Append(groupBy)
.Append("FROM C_Project LEFT OUTER JOIN C_Phase p ON (C_Project.C_Phase_ID=p.C_Phase_ID) ");
}
// Where
sb.Append("WHERE C_Project.C_ProjectType_ID=").Append(GetC_ProjectType_ID())
.Append(" AND C_Project.Processed<>'Y'");
// Date Restriction
if (startDate != null &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String dateString = DataBase.DB.TO_DATE(startDate);
sb.Append(" AND ").Append(dateColumn)
.Append(">=").Append(dateString);
} // date
//
String sql = MMeasureCalc.AddRestrictions(sb.ToString(), false, restrictions, role,
"C_Project", orgColumn, bpColumn, pColumn, GetCtx());
if (groupBy != null)
{
sql += " GROUP BY " + groupBy + " ORDER BY " + orderBy;
}
//
log.Fine(sql);
return(sql);
}
/**
* Get Sql to value for the bar chart
* @param restrictions array of goal restrictions
* @param MeasureDisplay scope of this value
* @param MeasureDataType data type
* @param startDate optional report start date
* @param role role
* @return sql for Bar Chart
*/
public String GetSqlBarChart(MGoalRestriction[] restrictions, String measureDisplay,
String measureDataType, DateTime?startDate, MRole role)
{
String dateColumn = "Created";
String orgColumn = "AD_Org_ID";
String bpColumn = "C_BPartner_ID";
String pColumn = "M_Product_ID";
//
StringBuilder sb = new StringBuilder("SELECT COUNT(*), ");
String groupBy = null;
String orderBy = null;
//
if (MMeasure.MEASUREDATATYPE_QtyAmountInTime.Equals(measureDataType) &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String trunc = "D";
if (MGoal.MEASUREDISPLAY_Year.Equals(measureDisplay))
{
trunc = "Y";
}
else if (MGoal.MEASUREDISPLAY_Quarter.Equals(measureDisplay))
{
trunc = "Q";
}
else if (MGoal.MEASUREDISPLAY_Month.Equals(measureDisplay))
{
trunc = "MM";
}
else if (MGoal.MEASUREDISPLAY_Week.Equals(measureDisplay))
{
trunc = "W";
}
// else if (MGoal.MEASUREDISPLAY_Day.equals(MeasureDisplay))
// ;
orderBy = "TRUNC(" + dateColumn + ",'" + trunc + "')";
//jz 0 is column position in EDB, Oracle doesn't take alias in group by
// groupBy = orderBy + ", 0 ";
// sb.append(groupBy)
groupBy = orderBy + ", CAST(0 AS INTEGER) ";
sb.Append(groupBy)
.Append("FROM R_Request ");
}
else
{
orderBy = "s.SeqNo";
groupBy = "COALESCE(s.Name,TO_NCHAR('-')), s.R_Status_ID, s.SeqNo ";
sb.Append(groupBy)
.Append("FROM R_Request LEFT OUTER JOIN R_Status s ON (R_Request.R_Status_ID=s.R_Status_ID) ");
}
// Where
sb.Append("WHERE R_Request.R_RequestType_ID=").Append(GetR_RequestType_ID())
.Append(" AND R_Request.Processed<>'Y'");
// Date Restriction
if (startDate != null &&
!MGoal.MEASUREDISPLAY_Total.Equals(measureDisplay))
{
String dateString = DataBase.DB.TO_DATE((DateTime?)startDate);
sb.Append(" AND ").Append(dateColumn)
.Append(">=").Append(dateString);
} // date
//
String sql = MMeasureCalc.AddRestrictions(sb.ToString(), false, restrictions, role,
"R_Request", orgColumn, bpColumn, pColumn, GetCtx());
if (groupBy != null)
{
sql += " GROUP BY " + groupBy + " ORDER BY " + orderBy;
}
//
log.Fine(sql);
return(sql);
}