ducha-houduan/supervision-system/src/main/java/com/supervision/safety/mapper/SafetyIssueSqlProvider.java

package com.supervision.safety.mapper;

import java.util.Map;

public class SafetyIssueSqlProvider {

    public String searchSql(Map<String, Object> p) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT id, description, risk_level, status, found_at, rectified_at, ")
                .append("deleted, create_time, update_time FROM safety_issue WHERE deleted=0 ");
        if (p.get("keyword") != null) sb.append("AND description LIKE CONCAT('%', #{keyword}, '%') ");
        if (p.get("level")   != null) sb.append("AND risk_level = #{level} ");
        if (p.get("status")  != null) sb.append("AND status = #{status} ");
        if (p.get("from")    != null) sb.append("AND found_at >= #{from} ");
        if (p.get("to")      != null) sb.append("AND found_at <  #{to} ");
        sb.append("ORDER BY found_at DESC ");
        sb.append("LIMIT #{limit} OFFSET #{offset}");
        return sb.toString();
    }

    public String countSql(Map<String, Object> p) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(*) FROM safety_issue WHERE deleted=0 ");
        if (p.get("keyword") != null) sb.append("AND description LIKE CONCAT('%', #{keyword}, '%') ");
        if (p.get("level")   != null) sb.append("AND risk_level = #{level} ");
        if (p.get("status")  != null) sb.append("AND status = #{status} ");
        if (p.get("from")    != null) sb.append("AND found_at >= #{from} ");
        if (p.get("to")      != null) sb.append("AND found_at <  #{to} ");
        return sb.toString();
    }

    public String countPendingByLevelSql() {
        return "SELECT risk_level AS lvl, COUNT(*) AS cnt " +
                "FROM safety_issue WHERE deleted=0 AND status='PENDING' GROUP BY risk_level";
    }

    public String trendByDaySql(Map<String, Object> p) {
        return "SELECT DATE_FORMAT(found_at, '%Y-%m-%d') AS d, COUNT(*) AS c " +
                "FROM safety_issue WHERE deleted=0 AND found_at >= #{from} AND found_at < #{to} " +
                "GROUP BY d ORDER BY d";
    }

    public String top5HighSql() {
        return "SELECT id, description, risk_level, status, found_at, rectified_at, " +
                "deleted, create_time, update_time " +
                "FROM safety_issue WHERE deleted=0 AND risk_level='HIGH' AND status='PENDING' " +
                "ORDER BY found_at DESC LIMIT 5";
    }
}