/// <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);
}
/**
* Get MMeasureCalc from Cache
* @param ctx Ctx
* @param PA_MeasureCalc_ID id
* @return MMeasureCalc
*/
public static MMeasureCalc Get(Ctx ctx, int PA_MeasureCalc_ID)
{
int key = PA_MeasureCalc_ID;
MMeasureCalc retValue = (MMeasureCalc)_cache[key];
if (retValue != null)
{
return(retValue);
}
retValue = new MMeasureCalc(ctx, PA_MeasureCalc_ID, null);
if (retValue.Get_ID() != 0)
{
_cache.Add(key, retValue);
}
return(retValue);
}
/// <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);
}
/**
* Update/save Goals with Calculation
* @return true if updated
*/
private Boolean UpdateCalculatedGoals()
{
if (!MEASURETYPE_Calculated.Equals(GetMeasureType()))
{
return(false);
}
MGoal[] goals = MGoal.GetMeasureGoals(GetCtx(), GetPA_Measure_ID());
for (int i = 0; i < goals.Length; i++)
{
MGoal goal = goals[i];
// Find Role
MRole role = null;
if (goal.GetAD_Role_ID() != 0)
{
role = MRole.Get(GetCtx(), goal.GetAD_Role_ID());
}
else if (goal.GetAD_User_ID() != 0)
{
MUser user = MUser.Get(GetCtx(), goal.GetAD_User_ID());
MRole[] roles = user.GetRoles(goal.GetAD_Org_ID());
if (roles.Length > 0)
{
role = roles[0];
}
}
if (role == null)
{
role = MRole.GetDefault(GetCtx(), false); // could result in wrong data
}
//
MMeasureCalc mc = MMeasureCalc.Get(GetCtx(), GetPA_MeasureCalc_ID());
if (mc == null || mc.Get_ID() == 0 || mc.Get_ID() != GetPA_MeasureCalc_ID())
{
log.Log(Level.SEVERE, "Not found PA_MeasureCalc_ID=" + GetPA_MeasureCalc_ID());
return(false);
}
Decimal?ManualActual = null;
String sql = mc.GetSqlPI(goal.GetRestrictions(false),
goal.GetMeasureScope(), GetMeasureDataType(), null, role);
IDataReader idr = null;
try // SQL statement could be wrong
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
ManualActual = Utility.Util.GetValueOfDecimal(idr[0]);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
// SQL may return no rows or null
if (ManualActual == null)
{
ManualActual = Env.ZERO;
log.Fine("No Value = " + sql);
}
goal.SetMeasureActual(ManualActual);
goal.Save();
}
return(true);
}