import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;

public class Probe41 {
    static final String URL = "jdbc:mysql://haiershi.a1.luyouxia.net:23844/smartor_sltd"
            + "?useUnicode=true&characterEncoding=utf8&useSSL=false"
            + "&serverTimezone=GMT%2B8&connectTimeout=15000&socketTimeout=90000";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (Connection c = DriverManager.getConnection(URL, "smartor", "Smartor.2023");
             PrintWriter out = new PrintWriter(new OutputStreamWriter(
                     new FileOutputStream("probe41.txt"), StandardCharsets.UTF_8))) {
            run(c, out, "select d.questiontext, sum(case when d.asrtext is null or trim(d.asrtext)='' then 1 else 0 end) blank, count(*) total "
                    + "from service_subtask s join service_subtask_detail d on d.sub_id=s.id "
                    + "where s.del_flag='0' and d.del_flag='0' and s.service_type in ('6','14') and s.sendstate=6 "
                    + "and s.finishtime >= '2026-07-29' and s.finishtime < '2026-10-29' "
                    + "and d.id = (select max(d2.id) from service_subtask_detail d2 where d2.sub_id=d.sub_id and d2.questiontext <=> d.questiontext and d2.del_flag='0') "
                    + "group by d.questiontext order by blank desc");
            out.flush();
        }
        System.out.println("done");
    }

    static void run(Connection c, PrintWriter out, String sql) {
        out.println("\n--- " + sql);
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            ResultSetMetaData md = rs.getMetaData();
            int n = md.getColumnCount();
            StringBuilder head = new StringBuilder();
            for (int i = 1; i <= n; i++) head.append(md.getColumnLabel(i)).append(i == n ? "" : " | ");
            out.println(head);
            int rows = 0;
            while (rs.next() && rows < 30) {
                StringBuilder line = new StringBuilder();
                for (int i = 1; i <= n; i++) line.append(rs.getString(i)).append(i == n ? "" : " | ");
                out.println(line);
                rows++;
            }
        } catch (Exception e) {
            out.println("SQL ERROR: " + e.getMessage());
        }
    }
}