Oracle APEX JET Charts, Metric Cards, and Visualizations

apex_add_jet_chart(
    page_id=5,
    region_name="Monthly Sales vs Returns",
    chart_type="line",
    sql_query="SELECT TO_CHAR(ORDER_DATE,'MM/YYYY') LABEL, SUM(AMOUNT) VALUE FROM ORDERS GROUP BY TO_CHAR(ORDER_DATE,'MM/YYYY') ORDER BY 1",
    series_name="Sales",
    y_axis_title="Amount ($)",
    x_axis_title="Month",
    extra_series=[
        {
            "sql": "SELECT TO_CHAR(RETURN_DATE,'MM/YYYY') LABEL, SUM(AMOUNT) VALUE FROM RETURNS GROUP BY TO_CHAR(RETURN_DATE,'MM/YYYY') ORDER BY 1",
            "series_name": "Returns",
        }
    ],
)

apex_add_gauge(
    page_id=5,
    region_name="SLA Compliance",
    sql_query="SELECT ROUND(SUM(CASE WHEN RESOLVED_IN_SLA='Y' THEN 1 ELSE 0 END) / COUNT(*) * 100) AS VALUE FROM TICKETS",
    min_value=0,
    max_value=100,
    thresholds=[
        {"value": 60,  "color": "#e53935"},
        {"value": 80,  "color": "#fb8c00"},
        {"value": 100, "color": "#43a047"},
    ],
)

apex_add_metric_cards(
    page_id=1,
    region_name="KPIs",
    metrics=[
        {"label": "Total Orders",    "sql": "SELECT COUNT(*) FROM ORDERS",                          "icon": "fa-shopping-cart", "color": "blue"},
        {"label": "Revenue MTD",     "sql": "SELECT TO_CHAR(SUM(AMOUNT),'FM$999,990') FROM ORDERS WHERE TRUNC(ORDER_DATE,'MM')=TRUNC(SYSDATE,'MM')", "icon": "fa-dollar", "color": "green", "prefix": "$"},
        {"label": "Pending Orders",  "sql": "SELECT COUNT(*) FROM ORDERS WHERE STATUS='PENDING'",   "icon": "fa-clock",         "color": "orange"},
        {"label": "Cancelled",       "sql": "SELECT COUNT(*) FROM ORDERS WHERE STATUS='CANCELLED'", "icon": "fa-times-circle",  "color": "red"},
    ],
    columns=4,
    style="gradient",
)

apex_add_stacked_chart(
    page_id=5,
    region_name="Tasks by Department & Status",
    series_list=[
        {"name": "Done",        "sql": "SELECT DEPT LABEL, COUNT(*) VALUE FROM TASKS WHERE STATUS='DONE'   GROUP BY DEPT ORDER BY 1"},
        {"name": "In Progress", "sql": "SELECT DEPT LABEL, COUNT(*) VALUE FROM TASKS WHERE STATUS='ACTIVE' GROUP BY DEPT ORDER BY 1"},
        {"name": "Blocked",     "sql": "SELECT DEPT LABEL, COUNT(*) VALUE FROM TASKS WHERE STATUS='HOLD'   GROUP BY DEPT ORDER BY 1"},
    ],
)

apex_add_animated_counter(
    page_id=1,
    region_name="Total Employees Counter",
    sql_query="SELECT COUNT(*) FROM EMPLOYEES WHERE ACTIVE = 'Y'",
    label="Active Employees",
    color="green",
    icon="fa-users",
    suffix=" staff",
    duration_ms=1500,
)

apex_add_bubble_chart(
    page_id=5,
    region_name="Rep Performance",
    sql_query="""
        SELECT REP_NAME LABEL,
               DEALS_CLOSED X,
               ROUND(AVG_DEAL_SIZE, 0) Y,
               TOTAL_REVENUE Z
          FROM SALES_REPS
         ORDER BY TOTAL_REVENUE DESC
    """,
    x_axis_title="Deals Closed",
    y_axis_title="Avg Deal Size ($)",
)