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