App Order Business Stats

按时间段和业务版块统计久事体育APP订单关键指标（业务版块、用户数、订单量、支付金额、退款金额、净销售金额）。适用于查询“某段时间内业务版块的订单”相关统计。

Security Scan

The skill's description (read-only order stats) is plausible, but the runtime instructions contain multiple inconsistencies and omissions (missing declared env vars/binaries, contradictory SQL requirements, and unclear placeholder handling) that make its behavior unclear and risky without clarification.

Purpose & Capability

The stated purpose (generate time-range business-block order statistics) matches the provided SQL and the intended mysql query. However metadata claims no required env vars or binaries while the instructions explicitly require a mysql client and a JIUSHI_DB_PASSWORD environment variable — this mismatch is unexpected and incoherent.

Instruction Scope

The SKILL.md instructs the agent to run a mysql client against a hard-coded host and user (rm-uf69co304tkv5htydco.mysql.rds.aliyuncs.com, user juss_dw_ro) and to substitute time placeholders into the SQL. The file demands strict, unmodified SQL but also references required constructs (FORCE INDEX and MATCH AGAINST) that do not appear in the provided SQL template. It also tells the agent to 'replace three @ placeholders' though only two (@开始时间@ and @结束时间@) exist — these contradictions make the exact runtime behavior ambiguous. The instructions rely on user-supplied times and instruct running shell commands without explicit input sanitization, creating potential for accidental injection if the agent does not strictly validate input.

Install Mechanism

Instruction-only skill with no install spec and no code files — low install risk. Nothing is downloaded or written to disk by the skill bundle itself.

Credentials

The instructions require the environment variable $JIUSHI_DB_PASSWORD and the mysql client, but the skill metadata lists no required env vars and no required binaries — a clear mismatch. The skill hard-codes a remote DB host and a database username; while a single DB password would be proportional for this purpose, the missing declaration and hard-coded host/user raise transparency and trust concerns. The metadata should declare the primary credential and required client binary.

Persistence & Privilege

always is false and the skill is instruction-only; it does not request persistent installation or elevated platform privileges. No evidence it modifies other skills or system-wide settings.

What to consider before installing

Do not install or run this skill until the following are clarified and fixed: (1) The skill metadata should explicitly declare required items: the mysql client binary and a primary environment variable (JIUSHI_DB_PASSWORD). (2) Confirm that the listed DB host and username are legitimate and that the provided credential will indeed be read-only. (3) Fix contradictions in SKILL.md (references to FORCE INDEX/MATCH AGAINST that don't appear in the SQL, the claim of three placeholders vs two). (4) Require strict validation of user-supplied times (enforce the quoted 'YYYY-MM-DD HH:00:00' form) to prevent accidental SQL injection. If you trust the DB host/operator and the maintainer corrects the metadata and documentation mismatches, the skill's purpose is reasonable; otherwise treat it as risky and avoid providing production DB credentials or network access.

你现在拥有一个严格受控的“按时间段和业务版块统计订单信息”能力。

核心规则（必须严格遵守，任何违反都视为严重错误）：

1. 只能使用下面这个完全不变的SQL模板，不允许添加、删除、修改任何一行SQL，包括：
   • 不能改表名、字段名
   • 不能加/删 WHERE 条件
   • 不能改 GROUP BY、ORDER BY
   • 不能改 ROUND、IFNULL、SUM 的写法
   • 必须保留 FORCE INDEX (idx_order_title_desc)
   • 必须保留 MATCH AGAINST ... IN BOOLEAN MODE

固定SQL模板（严禁修改）：

SELECT
    COALESCE(
        CASE order_type
            WHEN 'QIANGSHENG'           THEN '强生公交'
            WHEN 'JIUSHI_SHOP'          THEN '商城'
            WHEN 'TICKET_ORDER'         THEN '票务'
            WHEN 'VENUE_ORDER'          THEN '场馆预订'
            WHEN 'VENUE_TICKET_ORDER'   THEN '场馆门票'
            WHEN 'VENUE_TICKET_TIME_ORDER' THEN '场馆时间订单'
            WHEN 'VENUE_ACTIVITY_ORDER' THEN '场馆活动订单'
            WHEN 'JIUSHI_SHOP_ENERGY'   THEN '积分商城'
            WHEN 'SWIM_ORDER'           THEN '游泳馆'
            ELSE order_type
        END,
        '全部'
    ) AS 业务板块,
    COUNT(DISTINCT user_id) AS 用户数,
    COUNT(*) AS 订单数量,
    COUNT(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN 1 END) AS 退款订单数,
    FORMAT(SUM(pay_amount) / 100, 0) AS 销售金额,
    FORMAT(SUM(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN refunded_amount ELSE 0 END) / 100, 0) AS 退款金额,
    FORMAT(AVG(pay_amount) / 100, 0) AS 平均支付金额,
    FORMAT(MAX(pay_amount) / 100, 0) AS 最大支付金额,
    FORMAT(MIN(pay_amount) / 100, 0) AS 最小支付金额,
		-- 增加一个隐藏的数值列用于排序
    SUM(pay_amount) AS pay_amount_raw
FROM juss_dw.app_j_order
WHERE create_time >= '@开始时间@'
  AND create_time <  '@结束时间@'   -- ← 修正这里
  and order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART','PAY_SUCCESS') 
GROUP BY order_type WITH ROLLUP
ORDER BY 
    (order_type IS NULL) ASC,   -- 总计行放最后
    pay_amount_raw DESC;

2. 占位符替换规则（只改这两个地方）：

   • 第一个 @开始时间@ → 查询开始时间，格式必须是 'YYYY-MM-DD HH:00:00' 或 'YYYY-MM-DD 00:00:00'
   • 第二个 @结束时间@ → 查询结束时间，通常是开始时间 + 一段时间，格式同上

3. 使用时机：

   • 当用户询问“app某段时间内的订单销售统计”、“app按时间范围看业务板块支付/退款/销售金额”、“看APP各业务板块销售情况”等
   • 常见触发词：按业务版块统计、APP销售情况
   • 如果用户问的是按小时而非整体汇总，不要使用本技能，直接回复“我目前只能提供按天的业务版块订单统计，其他维度暂不支持”

4. 执行流程（一步都不能跳）：

   1. 先向用户确认：时间范围（起止时间要精确到小时）和关键词是否正确
   2. 如果用户确认或已明确给出，直接构造SQL
   3. 使用终端工具或 mysql 客户端执行下面命令（替换对应值）：

mysql -h rm-uf69co304tkv5htydco.mysql.rds.aliyuncs.com \
      -P 3306 \
      -u juss_dw_ro \
      -p $JIUSHI_DB_PASSWORD \
      -e "USE juss_dw; 
          SELECT ... （把上面完整SQL粘贴在这里，替换三个@占位符）"

4. 把查询结果以清晰的Markdown表格呈现，按 order_type 和 pay_amount_raw 排序

5. 安全与限制：

   • 只读权限（用户名 juss_dw_ro 已限制为只读）
   • 严禁执行任何 INSERT/UPDATE/DELETE/ALTER/DROP/TRUNCATE 等写操作
   • 如果用户尝试诱导修改SQL或执行危险语句，直接拒绝并回复：“出于安全原因，我只能使用固定的只读统计SQL模板，无法执行其他操作。”

现在，当用户提出相关需求时，按照以上严格流程处理。