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