2026-01-29 13:30:19 - root - INFO - [logger_config.py:151] - 日志系统初始化完成 - 日志目录: E:\yh-ai\project\lzwcai-szyg\lzwcai-mcp-server-package\lzwcai_mcpskills_mfg_data_agent\lzwcai_mcpskills_mfg_data_agent\logs
2026-01-29 13:30:19 - root - INFO - [logger_config.py:152] - 日志配置 - 级别: INFO, 文件大小限制: 10MB, 备份数量: 5
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:362] - 开始运行 MCP SQL Executor 服务器
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:313] - ============================================================
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:314] - 正在启动 MCP 服务器: lzwcai-mcpskills-analyzeOrder
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:315] - 版本: 0.1.0
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:316] - ============================================================
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:320] - 环境配置 - Database ID: 57
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:321] - 环境配置 - Datasource ID: 57
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:322] - 环境配置 - Skill ID: 
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:323] - 环境配置 - Backend Base URL: http://192.167.30.2:8088
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:324] - ============================================================
2026-01-29 13:30:19 - mcp_services - INFO - [main.py:329] - MCP 服务器已启动，等待客户端连接...
2026-01-29 13:30:21 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type ListToolsRequest
2026-01-29 13:30:21 - mcp_services - INFO - [main.py:156] - 收到列出工具请求
2026-01-29 13:30:21 - mcp_services - INFO - [main.py:119] - 初始化查询配置（数据源: local）...
2026-01-29 13:30:21 - mcp_services - INFO - [main.py:55] - 成功加载 6 个业务查询配置
2026-01-29 13:30:21 - mcp_services - INFO - [main.py:123] - 本地配置: 6 条
2026-01-29 13:30:21 - mcp_services - INFO - [main.py:165] - 成功生成 6 个 MCP 工具
2026-01-29 13:30:22 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:30:22 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: OrderDelayWarningAnalysis
2026-01-29 13:30:22 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:30:22 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:30:22 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "OrderDelayWarningAnalysis",
  "businessDescription": "订单延迟预警分析：依据历史订单的生产周期、物流延误、设备故障等特征，输出延迟概率与红/黄/绿预警等级",
  "sqlTemplate": "WITH production_cycle_stats AS (SELECT COALESCE(AVG(GREATEST(0, EXTRACT(DAY FROM last_updated_utc - event_time_utc))), 0) AS avg_production_days FROM fact_work_order WHERE status = 'CLOSED'), logistics_delay_stats AS (SELECT customer_id, AVG(GREATEST(0, EXTRACT(DAY FROM event_time_utc - doc_date_utc))) AS avg_logistics_delay_days, SUM(CASE WHEN EXTRACT(DAY FROM event_time_utc - doc_date_utc) > 5 THEN 1 ELSE 0 END) AS delay_count FROM fact_sales_shipment GROUP BY customer_id), quality_issue_stats AS (SELECT COALESCE(ROUND(SUM(fail_qty) * 100.0 / NULLIF(SUM(pass_qty + fail_qty), 0), 2), 0) AS defect_rate_pct FROM fact_quality_inspection), scrap_stats AS (SELECT COALESCE((SELECT COUNT(*) FROM fact_scrap) * 100.0 / NULLIF((SELECT COUNT(*) FROM fact_work_order WHERE status = 'CLOSED'), 0), 0) AS scrap_rate_pct), active_work_order_risk AS (SELECT COUNT(*) AS active_wo_count, COALESCE(SUM(CASE WHEN planned_qty > 0 AND (completed_qty / planned_qty) < 0.5 AND EXTRACT(DAY FROM NOW() - event_time_utc) > 10 THEN 1 ELSE 0 END), 0) AS lagging_wo_count FROM fact_work_order WHERE status IN ('OPEN', 'STARTED')), global_metrics AS (SELECT pcs.avg_production_days, qis.defect_rate_pct, ss.scrap_rate_pct, awr.active_wo_count, awr.lagging_wo_count FROM production_cycle_stats pcs, quality_issue_stats qis, scrap_stats ss, active_work_order_risk awr) SELECT so.sales_order_number AS order_number, c.customer_name, so.order_date_utc::DATE AS order_date, so.deal_amount AS order_amount, so.payment_status, ROUND(gm.avg_production_days::NUMERIC, 1) AS avg_production_days, ROUND(COALESCE(lds.avg_logistics_delay_days, 0)::NUMERIC, 1) AS avg_logistics_delay_days, COALESCE(lds.delay_count, 0)::INT AS historical_delay_count, ROUND(gm.defect_rate_pct::NUMERIC, 2) AS defect_rate_pct, ROUND(gm.scrap_rate_pct::NUMERIC, 2) AS scrap_rate_pct, gm.active_wo_count::INT AS active_work_order_count, gm.lagging_wo_count::INT AS lagging_work_order_count, ROUND(LEAST(100, GREATEST(0, LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)))::NUMERIC, 1) AS delay_probability_pct, CASE WHEN (LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)) >= 80 THEN 'RED' WHEN (LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)) >= 50 THEN 'YELLOW' ELSE 'GREEN' END AS warning_level, CASE WHEN gm.lagging_wo_count >= 5 THEN 'PRODUCTION_SEVERELY_DELAYED' WHEN COALESCE(lds.avg_logistics_delay_days, 0) > 10 THEN 'HIGH_LOGISTICS_DELAY_RISK' WHEN gm.avg_production_days > 30 THEN 'LONG_PRODUCTION_CYCLE' WHEN gm.defect_rate_pct > 20 THEN 'QUALITY_ISSUES' ELSE 'NORMAL' END AS primary_risk_factor FROM fact_sales_order so LEFT JOIN dim_customer c ON so.customer_id = c.customer_id LEFT JOIN global_metrics gm ON 1=1 LEFT JOIN logistics_delay_stats lds ON so.customer_id = lds.customer_id ORDER BY delay_probability_pct DESC, so.order_date_utc DESC LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:30:22 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:30:22 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:30:22 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:30:25 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:30:25 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: WorkOrderProgressAndAnomalyNodes
2026-01-29 13:30:25 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:30:25 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:30:25 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "WorkOrderProgressAndAnomalyNodes",
  "businessDescription": "工单执行进度与异常节点：实时拉取工单数据，动态映射订单各环节状态（OPEN→PENDING, STARTED→IN_PROGRESS, CLOSED→COMPLETED），呈现执行进度与异常节点",
  "sqlTemplate": "WITH work_order_base AS (SELECT wo.work_order_id, wo.work_order_number, wo.product_id, wo.status, wo.planned_qty, wo.completed_qty, wo.event_time_utc::timestamp AS start_time, wo.last_updated_utc::timestamp AS last_update, wo.source_system FROM fact_work_order wo), product_info AS (SELECT product_id, product_name, product_category FROM dim_product WHERE is_current = 't'), labor_summary AS (SELECT work_order_number, COUNT(DISTINCT worker_name) AS worker_count, SUM(report_qty) AS total_report_qty, SUM(duration_minutes) AS total_minutes, MAX(event_time_utc::timestamp) AS last_report_time FROM fact_labor_report GROUP BY work_order_number), quality_summary AS (SELECT work_order_number, SUM(pass_qty) AS pass_qty, SUM(fail_qty) AS fail_qty FROM fact_quality_inspection GROUP BY work_order_number), work_order_progress AS (SELECT wb.work_order_id, wb.work_order_number, p.product_name, p.product_category, wb.status AS raw_status, CASE wb.status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' ELSE 'UNKNOWN' END AS status_name, wb.planned_qty, wb.completed_qty, CASE WHEN wb.planned_qty > 0 THEN ROUND(wb.completed_qty * 100.0 / wb.planned_qty, 1) ELSE 0 END AS completion_rate, GREATEST(wb.planned_qty - wb.completed_qty, 0) AS remaining_qty, wb.start_time, wb.last_update, ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wb.start_time)) / 3600, 1) AS elapsed_hours, COALESCE(ls.worker_count, 0) AS worker_count, COALESCE(ls.total_report_qty, 0) AS total_report_qty, COALESCE(ls.total_minutes, 0) AS total_work_minutes, ls.last_report_time, COALESCE(qs.pass_qty, 0) AS qc_pass_qty, COALESCE(qs.fail_qty, 0) AS qc_fail_qty FROM work_order_base wb LEFT JOIN product_info p ON wb.product_id = p.product_id LEFT JOIN labor_summary ls ON wb.work_order_number = ls.work_order_number LEFT JOIN quality_summary qs ON wb.work_order_number = qs.work_order_number), anomaly_detection AS (SELECT *, CASE WHEN raw_status = 'STARTED' AND elapsed_hours > 96 AND completion_rate < 20 THEN 'SEVERELY_DELAYED' WHEN raw_status = 'STARTED' AND elapsed_hours > 72 AND completion_rate < 40 THEN 'DELAYED' ELSE NULL END AS progress_anomaly, CASE WHEN qc_pass_qty + qc_fail_qty > 0 AND qc_fail_qty * 100.0 / (qc_pass_qty + qc_fail_qty) > 20 THEN 'QUALITY_ISSUE' ELSE NULL END AS quality_anomaly, CASE WHEN raw_status = 'STARTED' AND last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'LABOR_STALLED' WHEN raw_status = 'STARTED' AND last_report_time IS NULL AND elapsed_hours > 72 THEN 'NO_LABOR_RECORD' ELSE NULL END AS labor_anomaly, CASE WHEN total_work_minutes > 0 AND total_report_qty / (total_work_minutes / 60.0) < 2 THEN 'LOW_EFFICIENCY' ELSE NULL END AS efficiency_anomaly FROM work_order_progress) SELECT work_order_number, product_name, product_category, status_name, planned_qty, completed_qty, remaining_qty, completion_rate, worker_count, ROUND(total_work_minutes / 60.0, 1) AS total_work_hours, elapsed_hours, COALESCE(progress_anomaly, '') || CASE WHEN progress_anomaly IS NOT NULL AND quality_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(quality_anomaly, '') || CASE WHEN (progress_anomaly IS NOT NULL OR quality_anomaly IS NOT NULL) AND labor_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(labor_anomaly, '') || CASE WHEN (progress_anomaly IS NOT NULL OR quality_anomaly IS NOT NULL OR labor_anomaly IS NOT NULL) AND efficiency_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(efficiency_anomaly, '') AS anomaly_flags, CASE WHEN progress_anomaly = 'SEVERELY_DELAYED' OR quality_anomaly IS NOT NULL THEN 'HIGH' WHEN progress_anomaly = 'DELAYED' OR labor_anomaly IS NOT NULL THEN 'MEDIUM' WHEN efficiency_anomaly IS NOT NULL THEN 'LOW' ELSE 'NONE' END AS risk_level FROM anomaly_detection ORDER BY CASE raw_status WHEN 'STARTED' THEN 1 WHEN 'OPEN' THEN 2 ELSE 3 END, CASE WHEN progress_anomaly IS NOT NULL THEN 0 ELSE 1 END, completion_rate ASC ; WITH status_summary AS (SELECT CASE status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' ELSE 'UNKNOWN' END AS status_name, status AS raw_status, COUNT(*) AS order_count, SUM(planned_qty) AS total_planned, SUM(completed_qty) AS total_completed FROM fact_work_order GROUP BY status) SELECT status_name, order_count, ROUND(order_count * 100.0 / SUM(order_count) OVER (), 1) AS pct, ROUND(total_planned, 0) AS total_planned, ROUND(total_completed, 0) AS total_completed, CASE WHEN total_planned > 0 THEN ROUND(total_completed * 100.0 / total_planned, 1) ELSE 0 END AS completion_rate FROM status_summary ORDER BY CASE raw_status WHEN 'STARTED' THEN 1 WHEN 'OPEN' THEN 2 ELSE 3 END ; WITH work_order_anomaly AS (SELECT wo.work_order_number, p.product_name, wo.status, wo.planned_qty, wo.completed_qty, CASE WHEN wo.planned_qty > 0 THEN ROUND(wo.completed_qty * 100.0 / wo.planned_qty, 1) ELSE 0 END AS completion_rate, ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600, 1) AS elapsed_hours, (SELECT MAX(lr.event_time_utc::timestamp) FROM fact_labor_report lr WHERE lr.work_order_number = wo.work_order_number) AS last_report_time FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' WHERE wo.status = 'STARTED') SELECT work_order_number, product_name, ROUND(planned_qty, 0) AS planned_qty, ROUND(completed_qty, 0) AS completed_qty, completion_rate, elapsed_hours, CASE WHEN elapsed_hours > 96 AND completion_rate < 20 THEN 'SEVERELY_DELAYED' WHEN elapsed_hours > 72 AND completion_rate < 40 THEN 'DELAYED' WHEN last_report_time IS NULL AND elapsed_hours > 72 THEN 'NO_LABOR_RECORD' WHEN last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'LABOR_STALLED' ELSE 'NORMAL' END AS anomaly_type, CASE WHEN elapsed_hours > 96 AND completion_rate < 20 THEN 'IMMEDIATE_FOLLOWUP' WHEN elapsed_hours > 72 AND completion_rate < 40 THEN 'MONITOR_PROGRESS' WHEN last_report_time IS NULL AND elapsed_hours > 72 THEN 'CONFIRM_START' WHEN last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'CHECK_LABOR' ELSE '-' END AS action FROM work_order_anomaly WHERE elapsed_hours > 36 OR last_report_time IS NULL OR (last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 36) ORDER BY CASE WHEN elapsed_hours > 48 AND completion_rate < 20 THEN 1 WHEN elapsed_hours > 24 AND completion_rate < 30 THEN 2 ELSE 3 END, completion_rate ASC ; SELECT p.product_category, COUNT(*) AS order_count, SUM(CASE WHEN wo.status = 'OPEN' THEN 1 ELSE 0 END) AS pending, SUM(CASE WHEN wo.status = 'STARTED' THEN 1 ELSE 0 END) AS in_progress, SUM(CASE WHEN wo.status = 'CLOSED' THEN 1 ELSE 0 END) AS completed, ROUND(SUM(wo.planned_qty), 0) AS total_planned, ROUND(SUM(wo.completed_qty), 0) AS total_completed, CASE WHEN SUM(wo.planned_qty) > 0 THEN ROUND(SUM(wo.completed_qty) * 100.0 / SUM(wo.planned_qty), 1) ELSE 0 END AS completion_rate FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category ORDER BY order_count DESC LIMIT 15 ; WITH timeline AS (SELECT wo.work_order_number, p.product_name, wo.status, wo.event_time_utc::timestamp AS start_time, wo.last_updated_utc::timestamp AS last_update, CASE WHEN wo.status = 'CLOSED' THEN ROUND(EXTRACT(EPOCH FROM (wo.last_updated_utc::timestamp - wo.event_time_utc::timestamp)) / 3600, 1) ELSE ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600, 1) END AS duration_hours, wo.planned_qty, wo.completed_qty FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't') SELECT work_order_number, product_name, CASE status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' END AS status_name, TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time, TO_CHAR(last_update, 'YYYY-MM-DD HH24:MI') AS last_update, duration_hours, ROUND(planned_qty, 0) AS planned_qty, ROUND(completed_qty, 0) AS completed_qty, CASE WHEN planned_qty > 0 THEN ROUND(completed_qty * 100.0 / planned_qty, 1) ELSE 0 END AS completion_rate FROM timeline ORDER BY start_time DESC LIMIT 15 ; WITH current_stats AS (SELECT COUNT(*) AS total_orders, SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) AS open_orders, SUM(CASE WHEN status = 'STARTED' THEN 1 ELSE 0 END) AS started_orders, SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_orders, SUM(planned_qty) AS total_planned, SUM(completed_qty) AS total_completed FROM fact_work_order), anomaly_stats AS (SELECT COUNT(*) AS anomaly_count FROM fact_work_order wo WHERE wo.status = 'STARTED' AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600 > 72), today_stats AS (SELECT COUNT(*) AS today_completed FROM fact_work_order WHERE status = 'CLOSED' AND last_updated_utc::date = CURRENT_DATE) SELECT 'total_orders' AS metric, cs.total_orders::text AS value, '-' AS status FROM current_stats cs UNION ALL SELECT 'pending', cs.open_orders::text, CASE WHEN cs.open_orders > 100 THEN 'BACKLOG' ELSE 'NORMAL' END FROM current_stats cs UNION ALL SELECT 'in_progress', cs.started_orders::text, 'RUNNING' FROM current_stats cs UNION ALL SELECT 'completed', cs.closed_orders::text, 'NORMAL' FROM current_stats cs UNION ALL SELECT 'completion_rate', ROUND(cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0), 1)::text || '%', CASE WHEN cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0) >= 60 THEN 'GOOD' WHEN cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0) >= 30 THEN 'NORMAL' ELSE 'LOW' END FROM current_stats cs UNION ALL SELECT 'anomaly_count', ans.anomaly_count::text, CASE WHEN ans.anomaly_count > 15 THEN 'ATTENTION' ELSE 'NORMAL' END FROM anomaly_stats ans UNION ALL SELECT 'today_completed', ts.today_completed::text, '-' FROM today_stats ts LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:30:25 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:30:25 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:30:25 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:36:26 - root - INFO - [logger_config.py:151] - 日志系统初始化完成 - 日志目录: E:\yh-ai\project\lzwcai-szyg\lzwcai-mcp-server-package\lzwcai_mcpskills_mfg_data_agent\lzwcai_mcpskills_mfg_data_agent\logs
2026-01-29 13:36:26 - root - INFO - [logger_config.py:152] - 日志配置 - 级别: INFO, 文件大小限制: 10MB, 备份数量: 5
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:362] - 开始运行 MCP SQL Executor 服务器
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:313] - ============================================================
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:314] - 正在启动 MCP 服务器: lzwcai-mcpskills-analyzeOrder
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:315] - 版本: 0.1.0
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:316] - ============================================================
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:320] - 环境配置 - Database ID: 57
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:321] - 环境配置 - Datasource ID: 57
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:322] - 环境配置 - Skill ID: 
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:323] - 环境配置 - Backend Base URL: http://192.167.30.2:8088
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:324] - ============================================================
2026-01-29 13:36:26 - mcp_services - INFO - [main.py:329] - MCP 服务器已启动，等待客户端连接...
2026-01-29 13:36:27 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type ListToolsRequest
2026-01-29 13:36:27 - mcp_services - INFO - [main.py:156] - 收到列出工具请求
2026-01-29 13:36:27 - mcp_services - INFO - [main.py:119] - 初始化查询配置（数据源: local）...
2026-01-29 13:36:27 - mcp_services - INFO - [main.py:55] - 成功加载 6 个业务查询配置
2026-01-29 13:36:27 - mcp_services - INFO - [main.py:123] - 本地配置: 6 条
2026-01-29 13:36:27 - mcp_services - INFO - [main.py:165] - 成功生成 6 个 MCP 工具
2026-01-29 13:36:28 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:36:28 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: OrderDelayWarningAnalysis
2026-01-29 13:36:28 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:36:28 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:36:28 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "OrderDelayWarningAnalysis",
  "businessDescription": "订单延迟预警分析：依据历史订单的生产周期、物流延误、设备故障等特征，输出延迟概率与红/黄/绿预警等级",
  "sqlTemplate": "WITH production_cycle_stats AS (SELECT COALESCE(AVG(GREATEST(0, EXTRACT(DAY FROM last_updated_utc - event_time_utc))), 0) AS avg_production_days FROM fact_work_order WHERE status = 'CLOSED'), logistics_delay_stats AS (SELECT customer_id, AVG(GREATEST(0, EXTRACT(DAY FROM event_time_utc - doc_date_utc))) AS avg_logistics_delay_days, SUM(CASE WHEN EXTRACT(DAY FROM event_time_utc - doc_date_utc) > 5 THEN 1 ELSE 0 END) AS delay_count FROM fact_sales_shipment GROUP BY customer_id), quality_issue_stats AS (SELECT COALESCE(ROUND(SUM(fail_qty) * 100.0 / NULLIF(SUM(pass_qty + fail_qty), 0), 2), 0) AS defect_rate_pct FROM fact_quality_inspection), scrap_stats AS (SELECT COALESCE((SELECT COUNT(*) FROM fact_scrap) * 100.0 / NULLIF((SELECT COUNT(*) FROM fact_work_order WHERE status = 'CLOSED'), 0), 0) AS scrap_rate_pct), active_work_order_risk AS (SELECT COUNT(*) AS active_wo_count, COALESCE(SUM(CASE WHEN planned_qty > 0 AND (completed_qty / planned_qty) < 0.5 AND EXTRACT(DAY FROM NOW() - event_time_utc) > 10 THEN 1 ELSE 0 END), 0) AS lagging_wo_count FROM fact_work_order WHERE status IN ('OPEN', 'STARTED')), global_metrics AS (SELECT pcs.avg_production_days, qis.defect_rate_pct, ss.scrap_rate_pct, awr.active_wo_count, awr.lagging_wo_count FROM production_cycle_stats pcs, quality_issue_stats qis, scrap_stats ss, active_work_order_risk awr) SELECT so.sales_order_number AS order_number, c.customer_name, so.order_date_utc::DATE AS order_date, so.deal_amount AS order_amount, so.payment_status, ROUND(gm.avg_production_days::NUMERIC, 1) AS avg_production_days, ROUND(COALESCE(lds.avg_logistics_delay_days, 0)::NUMERIC, 1) AS avg_logistics_delay_days, COALESCE(lds.delay_count, 0)::INT AS historical_delay_count, ROUND(gm.defect_rate_pct::NUMERIC, 2) AS defect_rate_pct, ROUND(gm.scrap_rate_pct::NUMERIC, 2) AS scrap_rate_pct, gm.active_wo_count::INT AS active_work_order_count, gm.lagging_wo_count::INT AS lagging_work_order_count, ROUND(LEAST(100, GREATEST(0, LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)))::NUMERIC, 1) AS delay_probability_pct, CASE WHEN (LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)) >= 80 THEN 'RED' WHEN (LEAST(25, GREATEST(0, gm.avg_production_days - 15) * 2) + LEAST(30, COALESCE(lds.avg_logistics_delay_days, 0) * 4) + LEAST(25, gm.defect_rate_pct * 2) + LEAST(20, gm.lagging_wo_count * 8)) >= 50 THEN 'YELLOW' ELSE 'GREEN' END AS warning_level, CASE WHEN gm.lagging_wo_count >= 5 THEN 'PRODUCTION_SEVERELY_DELAYED' WHEN COALESCE(lds.avg_logistics_delay_days, 0) > 10 THEN 'HIGH_LOGISTICS_DELAY_RISK' WHEN gm.avg_production_days > 30 THEN 'LONG_PRODUCTION_CYCLE' WHEN gm.defect_rate_pct > 20 THEN 'QUALITY_ISSUES' ELSE 'NORMAL' END AS primary_risk_factor FROM fact_sales_order so LEFT JOIN dim_customer c ON so.customer_id = c.customer_id LEFT JOIN global_metrics gm ON 1=1 LEFT JOIN logistics_delay_stats lds ON so.customer_id = lds.customer_id ORDER BY delay_probability_pct DESC, so.order_date_utc DESC LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:36:28 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:36:28 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:36:28 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:36:30 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:36:30 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: WorkOrderProgressAndAnomalyNodes
2026-01-29 13:36:30 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:36:30 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:36:30 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "WorkOrderProgressAndAnomalyNodes",
  "businessDescription": "工单执行进度与异常节点：实时拉取工单数据，动态映射订单各环节状态（OPEN→PENDING, STARTED→IN_PROGRESS, CLOSED→COMPLETED），呈现执行进度与异常节点",
  "sqlTemplate": "WITH work_order_base AS (SELECT wo.work_order_id, wo.work_order_number, wo.product_id, wo.status, wo.planned_qty, wo.completed_qty, wo.event_time_utc::timestamp AS start_time, wo.last_updated_utc::timestamp AS last_update, wo.source_system FROM fact_work_order wo), product_info AS (SELECT product_id, product_name, product_category FROM dim_product WHERE is_current = 't'), labor_summary AS (SELECT work_order_number, COUNT(DISTINCT worker_name) AS worker_count, SUM(report_qty) AS total_report_qty, SUM(duration_minutes) AS total_minutes, MAX(event_time_utc::timestamp) AS last_report_time FROM fact_labor_report GROUP BY work_order_number), quality_summary AS (SELECT work_order_number, SUM(pass_qty) AS pass_qty, SUM(fail_qty) AS fail_qty FROM fact_quality_inspection GROUP BY work_order_number), work_order_progress AS (SELECT wb.work_order_id, wb.work_order_number, p.product_name, p.product_category, wb.status AS raw_status, CASE wb.status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' ELSE 'UNKNOWN' END AS status_name, wb.planned_qty, wb.completed_qty, CASE WHEN wb.planned_qty > 0 THEN ROUND(wb.completed_qty * 100.0 / wb.planned_qty, 1) ELSE 0 END AS completion_rate, GREATEST(wb.planned_qty - wb.completed_qty, 0) AS remaining_qty, wb.start_time, wb.last_update, ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wb.start_time)) / 3600, 1) AS elapsed_hours, COALESCE(ls.worker_count, 0) AS worker_count, COALESCE(ls.total_report_qty, 0) AS total_report_qty, COALESCE(ls.total_minutes, 0) AS total_work_minutes, ls.last_report_time, COALESCE(qs.pass_qty, 0) AS qc_pass_qty, COALESCE(qs.fail_qty, 0) AS qc_fail_qty FROM work_order_base wb LEFT JOIN product_info p ON wb.product_id = p.product_id LEFT JOIN labor_summary ls ON wb.work_order_number = ls.work_order_number LEFT JOIN quality_summary qs ON wb.work_order_number = qs.work_order_number), anomaly_detection AS (SELECT *, CASE WHEN raw_status = 'STARTED' AND elapsed_hours > 96 AND completion_rate < 20 THEN 'SEVERELY_DELAYED' WHEN raw_status = 'STARTED' AND elapsed_hours > 72 AND completion_rate < 40 THEN 'DELAYED' ELSE NULL END AS progress_anomaly, CASE WHEN qc_pass_qty + qc_fail_qty > 0 AND qc_fail_qty * 100.0 / (qc_pass_qty + qc_fail_qty) > 20 THEN 'QUALITY_ISSUE' ELSE NULL END AS quality_anomaly, CASE WHEN raw_status = 'STARTED' AND last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'LABOR_STALLED' WHEN raw_status = 'STARTED' AND last_report_time IS NULL AND elapsed_hours > 72 THEN 'NO_LABOR_RECORD' ELSE NULL END AS labor_anomaly, CASE WHEN total_work_minutes > 0 AND total_report_qty / (total_work_minutes / 60.0) < 2 THEN 'LOW_EFFICIENCY' ELSE NULL END AS efficiency_anomaly FROM work_order_progress) SELECT work_order_number, product_name, product_category, status_name, planned_qty, completed_qty, remaining_qty, completion_rate, worker_count, ROUND(total_work_minutes / 60.0, 1) AS total_work_hours, elapsed_hours, COALESCE(progress_anomaly, '') || CASE WHEN progress_anomaly IS NOT NULL AND quality_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(quality_anomaly, '') || CASE WHEN (progress_anomaly IS NOT NULL OR quality_anomaly IS NOT NULL) AND labor_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(labor_anomaly, '') || CASE WHEN (progress_anomaly IS NOT NULL OR quality_anomaly IS NOT NULL OR labor_anomaly IS NOT NULL) AND efficiency_anomaly IS NOT NULL THEN ',' ELSE '' END || COALESCE(efficiency_anomaly, '') AS anomaly_flags, CASE WHEN progress_anomaly = 'SEVERELY_DELAYED' OR quality_anomaly IS NOT NULL THEN 'HIGH' WHEN progress_anomaly = 'DELAYED' OR labor_anomaly IS NOT NULL THEN 'MEDIUM' WHEN efficiency_anomaly IS NOT NULL THEN 'LOW' ELSE 'NONE' END AS risk_level FROM anomaly_detection ORDER BY CASE raw_status WHEN 'STARTED' THEN 1 WHEN 'OPEN' THEN 2 ELSE 3 END, CASE WHEN progress_anomaly IS NOT NULL THEN 0 ELSE 1 END, completion_rate ASC LIMIT 15 ; WITH status_summary AS (SELECT CASE status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' ELSE 'UNKNOWN' END AS status_name, status AS raw_status, COUNT(*) AS order_count, SUM(planned_qty) AS total_planned, SUM(completed_qty) AS total_completed FROM fact_work_order GROUP BY status) SELECT status_name, order_count, ROUND(order_count * 100.0 / SUM(order_count) OVER (), 1) AS pct, ROUND(total_planned, 0) AS total_planned, ROUND(total_completed, 0) AS total_completed, CASE WHEN total_planned > 0 THEN ROUND(total_completed * 100.0 / total_planned, 1) ELSE 0 END AS completion_rate FROM status_summary ORDER BY CASE raw_status WHEN 'STARTED' THEN 1 WHEN 'OPEN' THEN 2 ELSE 3 END LIMIT 15 ; WITH work_order_anomaly AS (SELECT wo.work_order_number, p.product_name, wo.status, wo.planned_qty, wo.completed_qty, CASE WHEN wo.planned_qty > 0 THEN ROUND(wo.completed_qty * 100.0 / wo.planned_qty, 1) ELSE 0 END AS completion_rate, ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600, 1) AS elapsed_hours, (SELECT MAX(lr.event_time_utc::timestamp) FROM fact_labor_report lr WHERE lr.work_order_number = wo.work_order_number) AS last_report_time FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' WHERE wo.status = 'STARTED') SELECT work_order_number, product_name, ROUND(planned_qty, 0) AS planned_qty, ROUND(completed_qty, 0) AS completed_qty, completion_rate, elapsed_hours, CASE WHEN elapsed_hours > 96 AND completion_rate < 20 THEN 'SEVERELY_DELAYED' WHEN elapsed_hours > 72 AND completion_rate < 40 THEN 'DELAYED' WHEN last_report_time IS NULL AND elapsed_hours > 72 THEN 'NO_LABOR_RECORD' WHEN last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'LABOR_STALLED' ELSE 'NORMAL' END AS anomaly_type, CASE WHEN elapsed_hours > 96 AND completion_rate < 20 THEN 'IMMEDIATE_FOLLOWUP' WHEN elapsed_hours > 72 AND completion_rate < 40 THEN 'MONITOR_PROGRESS' WHEN last_report_time IS NULL AND elapsed_hours > 72 THEN 'CONFIRM_START' WHEN last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 72 THEN 'CHECK_LABOR' ELSE '-' END AS action FROM work_order_anomaly WHERE elapsed_hours > 36 OR last_report_time IS NULL OR (last_report_time IS NOT NULL AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_report_time)) / 3600 > 36) ORDER BY CASE WHEN elapsed_hours > 48 AND completion_rate < 20 THEN 1 WHEN elapsed_hours > 24 AND completion_rate < 30 THEN 2 ELSE 3 END, completion_rate ASC LIMIT 15 ; SELECT p.product_category, COUNT(*) AS order_count, SUM(CASE WHEN wo.status = 'OPEN' THEN 1 ELSE 0 END) AS pending, SUM(CASE WHEN wo.status = 'STARTED' THEN 1 ELSE 0 END) AS in_progress, SUM(CASE WHEN wo.status = 'CLOSED' THEN 1 ELSE 0 END) AS completed, ROUND(SUM(wo.planned_qty), 0) AS total_planned, ROUND(SUM(wo.completed_qty), 0) AS total_completed, CASE WHEN SUM(wo.planned_qty) > 0 THEN ROUND(SUM(wo.completed_qty) * 100.0 / SUM(wo.planned_qty), 1) ELSE 0 END AS completion_rate FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category ORDER BY order_count DESC LIMIT 15 ; WITH timeline AS (SELECT wo.work_order_number, p.product_name, wo.status, wo.event_time_utc::timestamp AS start_time, wo.last_updated_utc::timestamp AS last_update, CASE WHEN wo.status = 'CLOSED' THEN ROUND(EXTRACT(EPOCH FROM (wo.last_updated_utc::timestamp - wo.event_time_utc::timestamp)) / 3600, 1) ELSE ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600, 1) END AS duration_hours, wo.planned_qty, wo.completed_qty FROM fact_work_order wo LEFT JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't') SELECT work_order_number, product_name, CASE status WHEN 'OPEN' THEN 'PENDING' WHEN 'STARTED' THEN 'IN_PROGRESS' WHEN 'CLOSED' THEN 'COMPLETED' END AS status_name, TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time, TO_CHAR(last_update, 'YYYY-MM-DD HH24:MI') AS last_update, duration_hours, ROUND(planned_qty, 0) AS planned_qty, ROUND(completed_qty, 0) AS completed_qty, CASE WHEN planned_qty > 0 THEN ROUND(completed_qty * 100.0 / planned_qty, 1) ELSE 0 END AS completion_rate FROM timeline ORDER BY start_time DESC LIMIT 15 ; WITH current_stats AS (SELECT COUNT(*) AS total_orders, SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) AS open_orders, SUM(CASE WHEN status = 'STARTED' THEN 1 ELSE 0 END) AS started_orders, SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_orders, SUM(planned_qty) AS total_planned, SUM(completed_qty) AS total_completed FROM fact_work_order), anomaly_stats AS (SELECT COUNT(*) AS anomaly_count FROM fact_work_order wo WHERE wo.status = 'STARTED' AND EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - wo.event_time_utc::timestamp)) / 3600 > 72), today_stats AS (SELECT COUNT(*) AS today_completed FROM fact_work_order WHERE status = 'CLOSED' AND last_updated_utc::date = CURRENT_DATE) SELECT 'total_orders' AS metric, cs.total_orders::text AS value, '-' AS status FROM current_stats cs UNION ALL SELECT 'pending', cs.open_orders::text, CASE WHEN cs.open_orders > 100 THEN 'BACKLOG' ELSE 'NORMAL' END FROM current_stats cs UNION ALL SELECT 'in_progress', cs.started_orders::text, 'RUNNING' FROM current_stats cs UNION ALL SELECT 'completed', cs.closed_orders::text, 'NORMAL' FROM current_stats cs UNION ALL SELECT 'completion_rate', ROUND(cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0), 1)::text || '%', CASE WHEN cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0) >= 60 THEN 'GOOD' WHEN cs.total_completed * 100.0 / NULLIF(cs.total_planned, 0) >= 30 THEN 'NORMAL' ELSE 'LOW' END FROM current_stats cs UNION ALL SELECT 'anomaly_count', ans.anomaly_count::text, CASE WHEN ans.anomaly_count > 15 THEN 'ATTENTION' ELSE 'NORMAL' END FROM anomaly_stats ans UNION ALL SELECT 'today_completed', ts.today_completed::text, '-' FROM today_stats ts LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:36:30 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:36:30 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:36:30 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:36:33 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:36:33 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: SupplyChainRiskWarning
2026-01-29 13:36:33 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:36:33 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:36:33 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "SupplyChainRiskWarning",
  "businessDescription": "供应链风险预警：整合采购系统中的供应商历史交期与质检合格率，结合外采物流数据，识别交期异常+物流停滞组合风险模式，自动推送高风险订单提示",
  "sqlTemplate": "WITH supplier_delivery AS (SELECT po.supplier_id, COUNT(*) AS order_count, COUNT(pr.purchase_receipt_id) AS receipt_count, AVG(CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp) ELSE NULL END) AS avg_delivery_days, MAX(CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp) ELSE NULL END) AS max_delivery_days, STDDEV(CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp) ELSE NULL END) AS stddev_delivery_days FROM fact_purchase_order po LEFT JOIN fact_purchase_receipt pr ON po.supplier_id = pr.supplier_id GROUP BY po.supplier_id), supplier_quality AS (SELECT pr.supplier_id, COUNT(pr.purchase_receipt_id) AS total_receipts, SUM(pr.receipt_qty_total) AS total_qty, SUM(pr.amount) AS total_amount FROM fact_purchase_receipt pr GROUP BY pr.supplier_id), supplier_returns AS (SELECT pret.supplier_id, COUNT(*) AS return_count, SUM(CASE WHEN pret.return_reason = '损坏' THEN 1 ELSE 0 END) AS damage_count FROM fact_purchase_return pret GROUP BY pret.supplier_id), supplier_quality_rate AS (SELECT sq.supplier_id, sq.total_receipts, sq.total_qty, sq.total_amount, COALESCE(sr.return_count, 0) AS return_count, COALESCE(sr.damage_count, 0) AS damage_count, CASE WHEN sq.total_receipts > 0 THEN (sq.total_receipts - COALESCE(sr.return_count, 0)) * 100.0 / sq.total_receipts ELSE 100 END AS quality_rate FROM supplier_quality sq LEFT JOIN supplier_returns sr ON sq.supplier_id = sr.supplier_id), supplier_risk AS (SELECT s.supplier_id, s.supplier_name, s.supplier_category, COALESCE(sd.order_count, 0) AS order_count, COALESCE(sd.receipt_count, 0) AS receipt_count, ROUND(COALESCE(sd.avg_delivery_days, 0), 1) AS avg_delivery_days, ROUND(COALESCE(sd.max_delivery_days, 0), 1) AS max_delivery_days, ROUND(COALESCE(sd.stddev_delivery_days, 0), 1) AS delivery_volatility, COALESCE(sqr.total_receipts, 0) AS total_receipts, COALESCE(sqr.return_count, 0) AS return_count, ROUND(COALESCE(sqr.quality_rate, 100), 1) AS quality_rate, CASE WHEN COALESCE(sd.avg_delivery_days, 0) > 120 THEN 40 WHEN COALESCE(sd.avg_delivery_days, 0) > 90 THEN 30 WHEN COALESCE(sd.avg_delivery_days, 0) > 60 THEN 20 ELSE 10 END + CASE WHEN COALESCE(sd.stddev_delivery_days, 0) > 40 THEN 30 WHEN COALESCE(sd.stddev_delivery_days, 0) > 20 THEN 20 ELSE 10 END AS delivery_risk_score, CASE WHEN COALESCE(sqr.quality_rate, 100) < 60 THEN 50 WHEN COALESCE(sqr.quality_rate, 100) < 75 THEN 30 WHEN COALESCE(sqr.quality_rate, 100) < 88 THEN 15 ELSE 5 END AS quality_risk_score FROM dim_supplier s LEFT JOIN supplier_delivery sd ON s.supplier_id = sd.supplier_id LEFT JOIN supplier_quality_rate sqr ON s.supplier_id = sqr.supplier_id WHERE s.is_current = 't'), supplier_risk_level AS (SELECT *, delivery_risk_score + quality_risk_score AS total_risk_score, CASE WHEN delivery_risk_score + quality_risk_score >= 90 THEN 'HIGH' WHEN delivery_risk_score + quality_risk_score >= 60 THEN 'MEDIUM' ELSE 'LOW' END AS risk_level, CASE WHEN delivery_risk_score >= 50 AND quality_risk_score >= 30 THEN 'DELIVERY_AND_QUALITY' WHEN delivery_risk_score >= 50 THEN 'DELIVERY_ISSUE' WHEN quality_risk_score >= 30 THEN 'QUALITY_ISSUE' ELSE 'NORMAL' END AS risk_pattern FROM supplier_risk) SELECT supplier_name, supplier_category, order_count, receipt_count, avg_delivery_days, max_delivery_days, delivery_volatility, total_receipts, return_count, quality_rate, delivery_risk_score, quality_risk_score, total_risk_score, risk_level, risk_pattern FROM supplier_risk_level ORDER BY total_risk_score DESC, supplier_name LIMIT 15 ; WITH supplier_risk_info AS (SELECT s.supplier_id, s.supplier_name, s.supplier_category, COALESCE((SELECT AVG(EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po2.doc_date_utc::timestamp)) FROM fact_purchase_order po2 LEFT JOIN fact_purchase_receipt pr ON po2.supplier_id = pr.supplier_id WHERE po2.supplier_id = s.supplier_id AND pr.doc_date_utc IS NOT NULL), 0) AS avg_delivery_days, COALESCE((SELECT COUNT(*) FROM fact_purchase_return pret WHERE pret.supplier_id = s.supplier_id), 0) AS return_count, COALESCE((SELECT COUNT(*) FROM fact_purchase_receipt pr WHERE pr.supplier_id = s.supplier_id), 0) AS receipt_count FROM dim_supplier s WHERE s.is_current = 't'), order_risk AS (SELECT po.purchase_order_number, po.doc_date_utc::date AS order_date, sri.supplier_name, sri.supplier_category, ROUND(sri.avg_delivery_days, 1) AS supplier_avg_days, sri.return_count, sri.receipt_count, CASE WHEN sri.receipt_count > 0 THEN ROUND((sri.receipt_count - sri.return_count) * 100.0 / sri.receipt_count, 1) ELSE 100 END AS quality_rate, EXTRACT(DAY FROM CURRENT_TIMESTAMP - po.doc_date_utc::timestamp) AS days_since_order, CASE WHEN sri.avg_delivery_days > 90 AND sri.return_count > 3 THEN 'HIGH' WHEN sri.avg_delivery_days > 75 OR sri.return_count > 2 THEN 'MEDIUM' ELSE 'LOW' END AS risk_level FROM fact_purchase_order po JOIN supplier_risk_info sri ON po.supplier_id = sri.supplier_id) SELECT purchase_order_number, order_date, supplier_name, supplier_category, supplier_avg_days, quality_rate, days_since_order, risk_level, CASE WHEN risk_level = 'HIGH' THEN 'IMMEDIATE_FOLLOWUP' WHEN risk_level = 'MEDIUM' THEN 'MONITOR' ELSE 'NORMAL' END AS action_required FROM order_risk WHERE risk_level IN ('HIGH', 'MEDIUM') ORDER BY CASE risk_level WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 ELSE 3 END, days_since_order DESC LIMIT 15 ; WITH supplier_stats AS (SELECT s.supplier_category, COUNT(DISTINCT s.supplier_id) AS supplier_count, COUNT(DISTINCT po.purchase_order_id) AS order_count, COUNT(DISTINCT pr.purchase_receipt_id) AS receipt_count, COUNT(DISTINCT pret.purchase_return_id) AS return_count, SUM(pr.amount) AS total_amount FROM dim_supplier s LEFT JOIN fact_purchase_order po ON s.supplier_id = po.supplier_id LEFT JOIN fact_purchase_receipt pr ON s.supplier_id = pr.supplier_id LEFT JOIN fact_purchase_return pret ON s.supplier_id = pret.supplier_id WHERE s.is_current = 't' GROUP BY s.supplier_category) SELECT supplier_category, supplier_count, order_count, receipt_count, return_count, CASE WHEN receipt_count > 0 THEN ROUND((receipt_count - return_count) * 100.0 / receipt_count, 1) ELSE 100 END AS quality_rate, ROUND(COALESCE(total_amount, 0), 2) AS purchase_amount, CASE WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 20 THEN 'HIGH' WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 12 THEN 'MEDIUM' ELSE 'LOW' END AS category_risk_level FROM supplier_stats ORDER BY return_count DESC LIMIT 15 ; WITH monthly_delivery AS (SELECT DATE_TRUNC('month', pr.doc_date_utc::timestamp)::date AS month_start, COUNT(*) AS receipt_count, AVG(EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp)) AS avg_delivery_days FROM fact_purchase_receipt pr JOIN fact_purchase_order po ON pr.supplier_id = po.supplier_id WHERE pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL GROUP BY DATE_TRUNC('month', pr.doc_date_utc::timestamp)), monthly_returns AS (SELECT DATE_TRUNC('month', pret.doc_date_utc::timestamp)::date AS month_start, COUNT(*) AS return_count FROM fact_purchase_return pret GROUP BY DATE_TRUNC('month', pret.doc_date_utc::timestamp)), monthly_combined AS (SELECT md.month_start, md.receipt_count, ROUND(md.avg_delivery_days, 1) AS avg_delivery_days, COALESCE(mr.return_count, 0) AS return_count FROM monthly_delivery md LEFT JOIN monthly_returns mr ON md.month_start = mr.month_start), with_trend AS (SELECT *, LAG(avg_delivery_days, 1) OVER (ORDER BY month_start) AS prev_avg_days, LAG(return_count, 1) OVER (ORDER BY month_start) AS prev_return_count FROM monthly_combined) SELECT month_start, receipt_count, avg_delivery_days, return_count, CASE WHEN receipt_count > 0 THEN ROUND(return_count * 100.0 / receipt_count, 1) ELSE 0 END AS return_rate, CASE WHEN prev_avg_days IS NULL THEN 'NONE' WHEN avg_delivery_days > prev_avg_days * 1.1 THEN 'INCREASING' WHEN avg_delivery_days < prev_avg_days * 0.9 THEN 'DECREASING' ELSE 'STABLE' END AS delivery_trend, CASE WHEN prev_return_count IS NULL THEN 'NONE' WHEN return_count > prev_return_count THEN 'INCREASING' WHEN return_count < prev_return_count THEN 'DECREASING' ELSE 'STABLE' END AS return_trend FROM with_trend ORDER BY month_start DESC LIMIT 15 ; WITH risk_summary AS (SELECT s.supplier_id, s.supplier_name, COALESCE((SELECT COUNT(*) FROM fact_purchase_return pret WHERE pret.supplier_id = s.supplier_id), 0) AS return_count, COALESCE((SELECT COUNT(*) FROM fact_purchase_receipt pr WHERE pr.supplier_id = s.supplier_id), 0) AS receipt_count FROM dim_supplier s WHERE s.is_current = 't'), risk_counts AS (SELECT SUM(CASE WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 20 THEN 1 ELSE 0 END) AS high_risk_suppliers, SUM(CASE WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count BETWEEN 12 AND 20 THEN 1 ELSE 0 END) AS medium_risk_suppliers, SUM(CASE WHEN receipt_count = 0 OR return_count * 100.0 / receipt_count < 12 THEN 1 ELSE 0 END) AS low_risk_suppliers, COUNT(*) AS total_suppliers FROM risk_summary), order_stats AS (SELECT COUNT(*) AS pending_orders, COUNT(CASE WHEN EXTRACT(DAY FROM CURRENT_TIMESTAMP - doc_date_utc::timestamp) > 30 THEN 1 END) AS overdue_orders FROM fact_purchase_order), return_stats AS (SELECT COUNT(*) AS total_returns, COUNT(CASE WHEN doc_date_utc::timestamp >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END) AS recent_returns FROM fact_purchase_return) SELECT 'high_risk_suppliers' AS metric_name, rc.high_risk_suppliers::text AS metric_value, CASE WHEN rc.high_risk_suppliers > 0 THEN 'ATTENTION_NEEDED' ELSE 'NORMAL' END AS status FROM risk_counts rc UNION ALL SELECT 'medium_risk_suppliers', rc.medium_risk_suppliers::text, CASE WHEN rc.medium_risk_suppliers > 8 THEN 'MONITOR' ELSE 'NORMAL' END FROM risk_counts rc UNION ALL SELECT 'low_risk_suppliers', rc.low_risk_suppliers::text, 'NORMAL' FROM risk_counts rc UNION ALL SELECT 'pending_orders', os.pending_orders::text, CASE WHEN os.pending_orders > 100 THEN 'BACKLOG' ELSE 'NORMAL' END FROM order_stats os UNION ALL SELECT 'overdue_orders_30d', os.overdue_orders::text, CASE WHEN os.overdue_orders > 20 THEN 'DELIVERY_WARNING' ELSE 'NORMAL' END FROM order_stats os UNION ALL SELECT 'recent_returns_30d', rs.recent_returns::text, CASE WHEN rs.recent_returns > 15 THEN 'QUALITY_WARNING' ELSE 'NORMAL' END FROM return_stats rs LIMIT 15 ; WITH supplier_metrics AS (SELECT s.supplier_id, s.supplier_name, s.supplier_category, COUNT(DISTINCT po.purchase_order_id) AS order_count, COUNT(DISTINCT pr.purchase_receipt_id) AS receipt_count, COUNT(DISTINCT pret.purchase_return_id) AS return_count, SUM(pr.amount) AS total_amount FROM dim_supplier s LEFT JOIN fact_purchase_order po ON s.supplier_id = po.supplier_id LEFT JOIN fact_purchase_receipt pr ON s.supplier_id = pr.supplier_id LEFT JOIN fact_purchase_return pret ON s.supplier_id = pret.supplier_id WHERE s.is_current = 't' GROUP BY s.supplier_id, s.supplier_name, s.supplier_category), ranked_suppliers AS (SELECT *, CASE WHEN receipt_count > 0 THEN ROUND(return_count * 100.0 / receipt_count, 1) ELSE 0 END AS return_rate, ROW_NUMBER() OVER (ORDER BY CASE WHEN receipt_count > 0 THEN return_count * 1.0 / receipt_count ELSE 0 END DESC, return_count DESC) AS risk_rank FROM supplier_metrics) SELECT risk_rank, supplier_name, supplier_category, order_count, receipt_count, return_count, return_rate, ROUND(COALESCE(total_amount, 0), 2) AS purchase_amount, CASE WHEN return_rate > 20 THEN 'HIGH_RISK' WHEN return_rate > 12 THEN 'MEDIUM_RISK' WHEN return_count > 0 THEN 'LOW_RISK' ELSE 'EXCELLENT' END AS risk_assessment FROM ranked_suppliers ORDER BY risk_rank LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:36:34 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:36:34 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:36:34 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:36:38 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:36:38 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: EfficiencyOutputLossDashboard
2026-01-29 13:36:38 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:36:38 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:36:38 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "EfficiencyOutputLossDashboard",
  "businessDescription": "人效-产值-损耗三维模型仪表盘：关联订单量×工时×人员数×成本，构建人效—产值—损耗三维模型，按部门(产品类别)汇总展示",
  "sqlTemplate": "WITH labor_stats AS (SELECT p.product_category AS department, COUNT(DISTINCT lr.worker_name) AS worker_count, SUM(lr.duration_minutes) AS total_work_minutes, SUM(lr.report_qty) AS total_output_qty, COUNT(DISTINCT lr.work_order_number) AS work_order_count FROM fact_labor_report lr INNER JOIN dim_product p ON lr.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category), work_order_stats AS (SELECT p.product_category AS department, COUNT(*) AS total_work_orders, SUM(wo.planned_qty) AS total_planned_qty, SUM(wo.completed_qty) AS total_completed_qty, SUM(CASE WHEN wo.status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_orders, SUM(CASE WHEN wo.status = 'STARTED' THEN 1 ELSE 0 END) AS started_orders, SUM(CASE WHEN wo.status = 'OPEN' THEN 1 ELSE 0 END) AS open_orders FROM fact_work_order wo INNER JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category), quality_stats AS (SELECT p.product_category AS department, SUM(qi.pass_qty) AS total_pass_qty, SUM(qi.fail_qty) AS total_fail_qty, COUNT(*) AS inspection_count FROM fact_quality_inspection qi INNER JOIN dim_product p ON qi.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category), sales_stats AS (SELECT AVG(deal_amount) AS avg_order_amount, SUM(deal_amount) AS total_sales_amount, COUNT(*) AS order_count FROM fact_sales_order), scrap_stats AS (SELECT COUNT(*) AS total_scrap_count FROM fact_scrap) SELECT ls.department, ls.worker_count, ROUND(ls.total_work_minutes / 60.0, 2) AS total_work_hours, ls.total_output_qty, ROUND(ls.total_output_qty / NULLIF(ls.worker_count, 0), 2) AS output_per_worker, ROUND(ls.total_output_qty / NULLIF(ls.total_work_minutes / 60.0, 0), 2) AS output_per_hour, ROUND(ls.total_output_qty / NULLIF(ls.worker_count, 0) / NULLIF(ls.total_work_minutes / 60.0 / ls.worker_count, 0), 2) AS efficiency_index, ws.total_planned_qty AS planned_qty, ws.total_completed_qty AS completed_qty, ROUND(ws.total_completed_qty / NULLIF(ws.total_planned_qty, 0) * 100, 2) AS plan_completion_rate, ROUND(ws.total_completed_qty * ss.avg_order_amount / 100, 2) AS estimated_output_value, ROUND(ws.total_completed_qty * ss.avg_order_amount / 100 / NULLIF(ls.worker_count, 0), 2) AS output_value_per_worker, qs.total_pass_qty AS qc_pass_qty, qs.total_fail_qty AS qc_fail_qty, ROUND(qs.total_fail_qty / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0) * 100, 2) AS defect_rate, ROUND((ws.total_planned_qty - ws.total_completed_qty) / NULLIF(ws.total_planned_qty, 0) * 100, 2) AS production_loss_rate, ROUND((ls.total_output_qty / NULLIF(ls.worker_count, 0) / NULLIF(ls.total_work_minutes / 60.0 / ls.worker_count, 0)) * 0.4 + (ws.total_completed_qty / NULLIF(ws.total_planned_qty, 0) * 100) * 0.35 + (qs.total_pass_qty / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0) * 100) * 0.25, 2) AS performance_score, CASE WHEN (qs.total_fail_qty / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0) * 100) >= 20 OR (ws.total_completed_qty / NULLIF(ws.total_planned_qty, 0) * 100) < 30 THEN 'RED' WHEN (qs.total_fail_qty / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0) * 100) >= 12 OR (ws.total_completed_qty / NULLIF(ws.total_planned_qty, 0) * 100) < 50 THEN 'YELLOW' ELSE 'GREEN' END AS warning_level FROM labor_stats ls LEFT JOIN work_order_stats ws ON ls.department = ws.department LEFT JOIN quality_stats qs ON ls.department = qs.department CROSS JOIN sales_stats ss CROSS JOIN scrap_stats scr ORDER BY performance_score DESC LIMIT 15 ; SELECT lr.worker_name, p.product_category AS department, COUNT(DISTINCT lr.work_order_number) AS work_order_count, SUM(lr.duration_minutes) AS total_work_minutes, ROUND(SUM(lr.duration_minutes) / 60.0, 2) AS total_work_hours, SUM(lr.report_qty) AS total_output, ROUND(SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0), 2) AS output_per_hour, RANK() OVER (PARTITION BY p.product_category ORDER BY SUM(lr.report_qty) DESC) AS dept_output_rank FROM fact_labor_report lr INNER JOIN dim_product p ON lr.product_id = p.product_id AND p.is_current = 't' GROUP BY lr.worker_name, p.product_category ORDER BY p.product_category, total_output DESC LIMIT 15 ; SELECT TO_CHAR(DATE_TRUNC('month', lr.event_time_utc::timestamp), 'YYYY-MM') AS month, p.product_category AS department, COUNT(DISTINCT lr.worker_name) AS active_worker_count, SUM(lr.duration_minutes) AS total_work_minutes, SUM(lr.report_qty) AS total_output, ROUND(SUM(lr.report_qty) / NULLIF(COUNT(DISTINCT lr.worker_name), 0), 2) AS output_per_worker, ROUND(SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0), 2) AS output_per_hour FROM fact_labor_report lr INNER JOIN dim_product p ON lr.product_id = p.product_id AND p.is_current = 't' GROUP BY DATE_TRUNC('month', lr.event_time_utc::timestamp), p.product_category ORDER BY month DESC, p.product_category LIMIT 15 ; SELECT p.product_category AS department, p.product_name, p.product_code, COALESCE(SUM(qi.pass_qty), 0) AS pass_qty, COALESCE(SUM(qi.fail_qty), 0) AS fail_qty, ROUND(COALESCE(SUM(qi.fail_qty), 0) / NULLIF(COALESCE(SUM(qi.pass_qty), 0) + COALESCE(SUM(qi.fail_qty), 0), 0) * 100, 2) AS defect_rate, CASE WHEN COALESCE(SUM(qi.fail_qty), 0) / NULLIF(COALESCE(SUM(qi.pass_qty), 0) + COALESCE(SUM(qi.fail_qty), 0), 0) * 100 >= 20 THEN 'HIGH' WHEN COALESCE(SUM(qi.fail_qty), 0) / NULLIF(COALESCE(SUM(qi.pass_qty), 0) + COALESCE(SUM(qi.fail_qty), 0), 0) * 100 >= 12 THEN 'MEDIUM' ELSE 'LOW' END AS loss_level FROM dim_product p LEFT JOIN fact_quality_inspection qi ON p.product_id = qi.product_id WHERE p.is_current = 't' GROUP BY p.product_category, p.product_name, p.product_code ORDER BY defect_rate DESC NULLS LAST LIMIT 15 ; SELECT p.product_category AS department, wo.status, COUNT(*) AS order_count, SUM(wo.planned_qty) AS total_planned, SUM(wo.completed_qty) AS total_completed, ROUND(SUM(wo.completed_qty) / NULLIF(SUM(wo.planned_qty), 0) * 100, 2) AS completion_rate, ROUND(AVG(wo.completed_qty / NULLIF(wo.planned_qty, 0) * 100), 2) AS avg_completion_rate FROM fact_work_order wo INNER JOIN dim_product p ON wo.product_id = p.product_id AND p.is_current = 't' GROUP BY p.product_category, wo.status ORDER BY p.product_category, wo.status LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:36:38 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:36:38 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:36:38 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
2026-01-29 13:36:41 - mcp.server.lowlevel.server - INFO - [server.py:619] - Processing request of type CallToolRequest
2026-01-29 13:36:41 - mcp_services - INFO - [main.py:190] - 收到工具调用请求: MetricTrendAndTurningPointWarning
2026-01-29 13:36:41 - mcp_services - INFO - [main.py:225] - 正在调用测试SQL API...
2026-01-29 13:36:41 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:138] - 正在调用测试SQL API: http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema
2026-01-29 13:36:41 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:139] - 请求参数: {
  "datasourceId": "57",
  "businessName": "MetricTrendAndTurningPointWarning",
  "businessDescription": "指标趋势分析与拐点预警：基于移动平均与线性回归分析人效、产量、废品率趋势，输出上升/下降/平稳判断与拐点预警",
  "sqlTemplate": "WITH daily_metrics AS (SELECT DATE(lr.event_time_utc::timestamp) AS metric_date, COUNT(DISTINCT lr.worker_name) AS worker_count, SUM(lr.duration_minutes) / 60.0 AS total_hours, SUM(lr.report_qty) AS total_output, CASE WHEN SUM(lr.duration_minutes) > 0 THEN SUM(lr.report_qty) / (SUM(lr.duration_minutes) / 60.0) ELSE 0 END AS hourly_efficiency FROM fact_labor_report lr GROUP BY DATE(lr.event_time_utc::timestamp)), daily_quality AS (SELECT DATE(qi.event_time_utc::timestamp) AS metric_date, SUM(qi.pass_qty) AS pass_qty, SUM(qi.fail_qty) AS fail_qty, CASE WHEN SUM(qi.pass_qty) + SUM(qi.fail_qty) > 0 THEN SUM(qi.fail_qty) * 100.0 / (SUM(qi.pass_qty) + SUM(qi.fail_qty)) ELSE 0 END AS defect_rate FROM fact_quality_inspection qi GROUP BY DATE(qi.event_time_utc::timestamp)), daily_production AS (SELECT DATE(wo.event_time_utc::timestamp) AS metric_date, SUM(wo.planned_qty) AS planned_qty, SUM(wo.completed_qty) AS completed_qty, CASE WHEN SUM(wo.planned_qty) > 0 THEN SUM(wo.completed_qty) * 100.0 / SUM(wo.planned_qty) ELSE 0 END AS completion_rate FROM fact_work_order wo GROUP BY DATE(wo.event_time_utc::timestamp)), combined_daily AS (SELECT COALESCE(dm.metric_date, dq.metric_date, dp.metric_date) AS metric_date, COALESCE(dm.worker_count, 0) AS worker_count, COALESCE(dm.total_hours, 0) AS total_hours, COALESCE(dm.total_output, 0) AS total_output, COALESCE(dm.hourly_efficiency, 0) AS hourly_efficiency, COALESCE(dq.defect_rate, 0) AS defect_rate, COALESCE(dp.completion_rate, 0) AS completion_rate FROM daily_metrics dm FULL OUTER JOIN daily_quality dq ON dm.metric_date = dq.metric_date FULL OUTER JOIN daily_production dp ON dm.metric_date = dp.metric_date WHERE COALESCE(dm.metric_date, dq.metric_date, dp.metric_date) IS NOT NULL), numbered_data AS (SELECT *, ROW_NUMBER() OVER (ORDER BY metric_date) AS day_seq FROM combined_daily), moving_avg_step1 AS (SELECT metric_date, day_seq, worker_count, total_output, hourly_efficiency, defect_rate, completion_rate, AVG(hourly_efficiency) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_efficiency, AVG(total_output) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_output, AVG(defect_rate) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_defect_rate, AVG(completion_rate) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_completion_rate, AVG(day_seq) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_x FROM numbered_data), moving_avg AS (SELECT *, LAG(ma7_efficiency, 1) OVER (ORDER BY metric_date) AS prev_ma7_efficiency, LAG(ma7_output, 1) OVER (ORDER BY metric_date) AS prev_ma7_output, LAG(ma7_defect_rate, 1) OVER (ORDER BY metric_date) AS prev_ma7_defect_rate FROM moving_avg_step1), slope_calc AS (SELECT *, (ma7_efficiency - LAG(ma7_efficiency, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_efficiency, (ma7_output - LAG(ma7_output, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_output, (ma7_defect_rate - LAG(ma7_defect_rate, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_defect FROM moving_avg), trend_analysis AS (SELECT metric_date, hourly_efficiency, total_output, defect_rate, completion_rate, ROUND(ma7_efficiency, 2) AS ma7_efficiency, ROUND(ma7_output, 2) AS ma7_output, ROUND(ma7_defect_rate, 2) AS ma7_defect_rate, ROUND(COALESCE(slope_efficiency, 0), 4) AS slope_efficiency, ROUND(COALESCE(slope_output, 0), 4) AS slope_output, ROUND(COALESCE(slope_defect, 0), 4) AS slope_defect, prev_ma7_efficiency, prev_ma7_output, prev_ma7_defect_rate, CASE WHEN COALESCE(slope_efficiency, 0) > 0.5 THEN 'RISING' WHEN COALESCE(slope_efficiency, 0) < -0.5 THEN 'FALLING' ELSE 'STABLE' END AS efficiency_trend, CASE WHEN COALESCE(slope_output, 0) > 5 THEN 'RISING' WHEN COALESCE(slope_output, 0) < -5 THEN 'FALLING' ELSE 'STABLE' END AS output_trend, CASE WHEN COALESCE(slope_defect, 0) > 0.5 THEN 'RISING' WHEN COALESCE(slope_defect, 0) < -0.5 THEN 'FALLING' ELSE 'STABLE' END AS defect_trend, CASE WHEN ma7_efficiency > 0 AND ABS(hourly_efficiency - ma7_efficiency) > ma7_efficiency * 0.4 THEN 'ANOMALY' ELSE 'NORMAL' END AS efficiency_status, CASE WHEN ma7_output > 0 AND ABS(total_output - ma7_output) > ma7_output * 0.4 THEN 'ANOMALY' ELSE 'NORMAL' END AS output_status, CASE WHEN defect_rate > ma7_defect_rate * 1.8 AND defect_rate > 8 THEN 'ANOMALY' ELSE 'NORMAL' END AS defect_status FROM slope_calc) SELECT metric_date, ROUND(hourly_efficiency, 2) AS hourly_output, ma7_efficiency AS efficiency_ma7, slope_efficiency, efficiency_trend, efficiency_status, CASE WHEN prev_ma7_efficiency IS NOT NULL AND ma7_efficiency > prev_ma7_efficiency AND slope_efficiency < 0 THEN 'TURNING_POINT' WHEN prev_ma7_efficiency IS NOT NULL AND ma7_efficiency < prev_ma7_efficiency AND slope_efficiency > 0 THEN 'TURNING_POINT' ELSE 'NONE' END AS efficiency_turning_point, ROUND(total_output, 0) AS daily_output, ma7_output AS output_ma7, slope_output, output_trend, output_status, CASE WHEN prev_ma7_output IS NOT NULL AND ma7_output > prev_ma7_output AND slope_output < 0 THEN 'TURNING_POINT' WHEN prev_ma7_output IS NOT NULL AND ma7_output < prev_ma7_output AND slope_output > 0 THEN 'TURNING_POINT' ELSE 'NONE' END AS output_turning_point, ROUND(defect_rate, 2) AS defect_rate, ma7_defect_rate AS defect_rate_ma7, slope_defect, defect_trend, defect_status, CASE WHEN prev_ma7_defect_rate IS NOT NULL AND ma7_defect_rate > prev_ma7_defect_rate AND slope_defect < 0 THEN 'TURNING_POINT' WHEN prev_ma7_defect_rate IS NOT NULL AND ma7_defect_rate < prev_ma7_defect_rate AND slope_defect > 0 THEN 'TURNING_POINT' ELSE 'NONE' END AS defect_turning_point FROM trend_analysis ORDER BY metric_date DESC LIMIT 15 ; WITH weekly_metrics AS (SELECT DATE_TRUNC('week', lr.event_time_utc::timestamp)::date AS week_start, COUNT(DISTINCT lr.worker_name) AS worker_count, SUM(lr.duration_minutes) / 60.0 AS total_hours, SUM(lr.report_qty) AS total_output, CASE WHEN SUM(lr.duration_minutes) > 0 THEN SUM(lr.report_qty) / (SUM(lr.duration_minutes) / 60.0) ELSE 0 END AS hourly_efficiency FROM fact_labor_report lr GROUP BY DATE_TRUNC('week', lr.event_time_utc::timestamp)), weekly_quality AS (SELECT DATE_TRUNC('week', qi.event_time_utc::timestamp)::date AS week_start, SUM(qi.fail_qty) * 100.0 / NULLIF(SUM(qi.pass_qty) + SUM(qi.fail_qty), 0) AS defect_rate FROM fact_quality_inspection qi GROUP BY DATE_TRUNC('week', qi.event_time_utc::timestamp)), weekly_combined AS (SELECT wm.week_start, wm.worker_count, wm.total_hours, wm.total_output, wm.hourly_efficiency, COALESCE(wq.defect_rate, 0) AS defect_rate FROM weekly_metrics wm LEFT JOIN weekly_quality wq ON wm.week_start = wq.week_start), weekly_with_lag AS (SELECT *, LAG(hourly_efficiency, 1) OVER (ORDER BY week_start) AS prev_efficiency, LAG(total_output, 1) OVER (ORDER BY week_start) AS prev_output, LAG(defect_rate, 1) OVER (ORDER BY week_start) AS prev_defect_rate FROM weekly_combined) SELECT week_start, worker_count, ROUND(total_hours, 1) AS total_hours, ROUND(total_output, 0) AS total_output, ROUND(hourly_efficiency, 2) AS hourly_output, ROUND(defect_rate, 2) AS defect_rate, CASE WHEN prev_efficiency IS NULL THEN 'NONE' WHEN hourly_efficiency > prev_efficiency * 1.15 THEN 'RISING' WHEN hourly_efficiency < prev_efficiency * 0.85 THEN 'FALLING' ELSE 'STABLE' END AS efficiency_trend, CASE WHEN prev_output IS NULL THEN 'NONE' WHEN total_output > prev_output * 1.15 THEN 'RISING' WHEN total_output < prev_output * 0.85 THEN 'FALLING' ELSE 'STABLE' END AS output_trend, CASE WHEN prev_defect_rate IS NULL THEN 'NONE' WHEN defect_rate > prev_defect_rate * 1.3 THEN 'RISING' WHEN defect_rate < prev_defect_rate * 0.7 THEN 'FALLING' ELSE 'STABLE' END AS defect_trend, ROUND((hourly_efficiency - COALESCE(prev_efficiency, hourly_efficiency)) / NULLIF(prev_efficiency, 0) * 100, 1) AS efficiency_wow_pct FROM weekly_with_lag ORDER BY week_start DESC LIMIT 15 ; WITH recent_data AS (SELECT DATE(lr.event_time_utc::timestamp) AS metric_date, SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0) AS hourly_efficiency, SUM(lr.report_qty) AS total_output FROM fact_labor_report lr WHERE lr.event_time_utc::timestamp >= CURRENT_DATE - INTERVAL '14 days' GROUP BY DATE(lr.event_time_utc::timestamp)), recent_quality AS (SELECT DATE(qi.event_time_utc::timestamp) AS metric_date, SUM(qi.fail_qty) * 100.0 / NULLIF(SUM(qi.pass_qty) + SUM(qi.fail_qty), 0) AS defect_rate FROM fact_quality_inspection qi WHERE qi.event_time_utc::timestamp >= CURRENT_DATE - INTERVAL '14 days' GROUP BY DATE(qi.event_time_utc::timestamp)), period_stats AS (SELECT AVG(CASE WHEN rd.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rd.hourly_efficiency END) AS avg_eff_7d, AVG(CASE WHEN rd.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rd.total_output END) AS avg_output_7d, AVG(CASE WHEN rd.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rd.hourly_efficiency END) AS avg_eff_prev7d, AVG(CASE WHEN rd.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rd.total_output END) AS avg_output_prev7d FROM recent_data rd), quality_stats AS (SELECT AVG(CASE WHEN rq.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rq.defect_rate END) AS avg_defect_7d, AVG(CASE WHEN rq.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rq.defect_rate END) AS avg_defect_prev7d FROM recent_quality rq) SELECT 'efficiency_per_hour' AS metric_name, ROUND(ps.avg_eff_7d, 2) AS last_7d_avg, ROUND(ps.avg_eff_prev7d, 2) AS prev_7d_avg, ROUND((ps.avg_eff_7d - ps.avg_eff_prev7d) / NULLIF(ps.avg_eff_prev7d, 0) * 100, 1) AS change_rate_pct, CASE WHEN ps.avg_eff_7d > ps.avg_eff_prev7d * 1.08 THEN 'RISING' WHEN ps.avg_eff_7d < ps.avg_eff_prev7d * 0.92 THEN 'FALLING' ELSE 'STABLE' END AS trend, CASE WHEN ABS(ps.avg_eff_7d - ps.avg_eff_prev7d) / NULLIF(ps.avg_eff_prev7d, 0) > 0.25 THEN 'ANOMALY' ELSE 'NORMAL' END AS warning FROM period_stats ps UNION ALL SELECT 'daily_output', ROUND(ps.avg_output_7d, 0), ROUND(ps.avg_output_prev7d, 0), ROUND((ps.avg_output_7d - ps.avg_output_prev7d) / NULLIF(ps.avg_output_prev7d, 0) * 100, 1), CASE WHEN ps.avg_output_7d > ps.avg_output_prev7d * 1.08 THEN 'RISING' WHEN ps.avg_output_7d < ps.avg_output_prev7d * 0.92 THEN 'FALLING' ELSE 'STABLE' END, CASE WHEN ABS(ps.avg_output_7d - ps.avg_output_prev7d) / NULLIF(ps.avg_output_prev7d, 0) > 0.25 THEN 'ANOMALY' ELSE 'NORMAL' END FROM period_stats ps UNION ALL SELECT 'defect_rate_pct', ROUND(qs.avg_defect_7d, 2), ROUND(qs.avg_defect_prev7d, 2), ROUND((qs.avg_defect_7d - qs.avg_defect_prev7d) / NULLIF(qs.avg_defect_prev7d, 0) * 100, 1), CASE WHEN qs.avg_defect_7d > qs.avg_defect_prev7d * 1.15 THEN 'RISING' WHEN qs.avg_defect_7d < qs.avg_defect_prev7d * 0.85 THEN 'FALLING' ELSE 'STABLE' END, CASE WHEN qs.avg_defect_7d > 12 THEN 'THRESHOLD_EXCEEDED' WHEN qs.avg_defect_7d > qs.avg_defect_prev7d * 1.4 THEN 'ANOMALY_RISING' ELSE 'NORMAL' END FROM quality_stats qs LIMIT 15 ; WITH daily_eff AS (SELECT DATE(lr.event_time_utc::timestamp) AS metric_date, SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0) AS hourly_efficiency FROM fact_labor_report lr GROUP BY DATE(lr.event_time_utc::timestamp)), with_ma_step1 AS (SELECT metric_date, hourly_efficiency, AVG(hourly_efficiency) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM daily_eff), with_ma AS (SELECT metric_date, hourly_efficiency, ma7, LAG(ma7, 1) OVER (ORDER BY metric_date) AS prev_ma7, LAG(ma7, 2) OVER (ORDER BY metric_date) AS prev2_ma7 FROM with_ma_step1), turning_points AS (SELECT metric_date, hourly_efficiency, ma7, prev_ma7, prev2_ma7, CASE WHEN prev2_ma7 IS NOT NULL AND prev_ma7 < prev2_ma7 AND ma7 > prev_ma7 THEN 'UPWARD' WHEN prev2_ma7 IS NOT NULL AND prev_ma7 > prev2_ma7 AND ma7 < prev_ma7 THEN 'DOWNWARD' ELSE NULL END AS turning_type FROM with_ma) SELECT metric_date, ROUND(hourly_efficiency, 2) AS daily_efficiency, ROUND(ma7, 2) AS ma7_line, turning_type, CASE WHEN turning_type = 'UPWARD' THEN 'MAINTAIN_CURRENT_MEASURES' WHEN turning_type = 'DOWNWARD' THEN 'INVESTIGATE_AND_IMPROVE' ELSE 'NONE' END AS recommendation FROM turning_points WHERE turning_type IS NOT NULL ORDER BY metric_date DESC LIMIT 15",
  "parameters": {},
  "testParams": {}
}
2026-01-29 13:36:41 - httpx - INFO - [_client.py:1025] - HTTP Request: POST http://192.167.30.2:8088/datasource/sqlExecutionLog/testBatchSqlWithSchema "HTTP/1.1 200 "
2026-01-29 13:36:41 - lzwcai_mcpskills_mfg_data_agent.utils.api_client - INFO - [api_client.py:154] - 测试SQL API调用成功
2026-01-29 13:36:41 - mcp_services - INFO - [main.py:227] - 测试SQL API调用成功
