-- ============================================================ -- BI 大屏新增:出勤指标(人资办公大屏)+ 库存采购(采购销售大屏) -- 生成时间:2026-10-30 -- -- 1. 放开「人资办公大屏」菜单(原来 visible=0 隐藏,导致看不到) -- 2. 人资办公大屏 增加 出勤率 / 迟到 / 缺卡 / 请假工时 / 出勤趋势 -- 3. 采购销售大屏 增加 库存可用量 / 库存品种 / 已入库量 / 库存TOP10 -- -- 图表约定:number 类型 SQL 返回单列 value;bar/pie 返回 label + value -- 幂等:按 name+dashboard_id 判重,可重复执行 -- ============================================================ -- ---------- 1. 放开人资办公大屏菜单 ---------- UPDATE system_menu SET visible = b'1', updater = '1', update_time = NOW() WHERE id = 1063005 AND deleted = b'0'; -- ---------- 2. 人资办公大屏:出勤指标 ---------- -- 布局:number 卡片占用 y=0 行 x=12..23(原有 4 张只用到 x=0..11) INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '本月出勤率', 'number', NULL, 'SELECT ROUND(SUM(actual_days)/NULLIF(SUM(work_days),0)*100,1) AS value FROM hrm_attendance_summary WHERE deleted=0 AND `year_month`=DATE_FORMAT(NOW(),''%Y-%m'')', '{"h": 4, "w": 3, "x": 12, "y": 0}', NULL, 30, 20, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'hr_oa' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '本月出勤率' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '本月迟到次数', 'number', NULL, 'SELECT IFNULL(SUM(late_count),0) AS value FROM hrm_attendance_summary WHERE deleted=0 AND `year_month`=DATE_FORMAT(NOW(),''%Y-%m'')', '{"h": 4, "w": 3, "x": 15, "y": 0}', NULL, 30, 21, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'hr_oa' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '本月迟到次数' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '本月缺卡次数', 'number', NULL, 'SELECT IFNULL(SUM(absent_count),0) AS value FROM hrm_attendance_summary WHERE deleted=0 AND `year_month`=DATE_FORMAT(NOW(),''%Y-%m'')', '{"h": 4, "w": 3, "x": 18, "y": 0}', NULL, 30, 22, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'hr_oa' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '本月缺卡次数' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '本月请假工时', 'number', NULL, 'SELECT ROUND(IFNULL(SUM(leave_hours),0),1) AS value FROM hrm_attendance_summary WHERE deleted=0 AND `year_month`=DATE_FORMAT(NOW(),''%Y-%m'')', '{"h": 4, "w": 3, "x": 21, "y": 0}', NULL, 30, 23, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'hr_oa' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '本月请假工时' AND c.deleted = b'0'); -- 出勤趋势(月度出勤率折线,占 x=12..23 区域) INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '出勤率趋势', 'line', NULL, 'SELECT `year_month` AS label, ROUND(SUM(actual_days)/NULLIF(SUM(work_days),0)*100,1) AS value FROM hrm_attendance_summary WHERE deleted=0 GROUP BY `year_month` ORDER BY `year_month`', '{"h": 8, "w": 12, "x": 12, "y": 4}', NULL, 60, 24, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'hr_oa' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '出勤率趋势' AND c.deleted = b'0'); -- ---------- 3. 采购销售大屏:库存采购 ---------- INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '库存可用总量', 'number', NULL, 'SELECT ROUND(IFNULL(SUM(quantity-frozen_quantity-reserved_quantity),0),0) AS value FROM mes_wm_material_stock WHERE deleted=0 AND frozen=0', '{"h": 4, "w": 3, "x": 12, "y": 0}', NULL, 60, 20, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'purchase_sales' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '库存可用总量' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '库存品种数', 'number', NULL, 'SELECT COUNT(DISTINCT item_id) AS value FROM mes_wm_material_stock WHERE deleted=0', '{"h": 4, "w": 3, "x": 15, "y": 0}', NULL, 60, 21, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'purchase_sales' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '库存品种数' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '冻结库存量', 'number', NULL, 'SELECT ROUND(IFNULL(SUM(frozen_quantity+reserved_quantity),0),0) AS value FROM mes_wm_material_stock WHERE deleted=0', '{"h": 4, "w": 3, "x": 18, "y": 0}', NULL, 60, 22, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'purchase_sales' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '冻结库存量' AND c.deleted = b'0'); INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '供应商到货批次', 'number', NULL, 'SELECT COUNT(*) AS value FROM mes_wm_material_stock WHERE deleted=0 AND vendor_id IS NOT NULL', '{"h": 4, "w": 3, "x": 21, "y": 0}', NULL, 60, 23, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'purchase_sales' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '供应商到货批次' AND c.deleted = b'0'); -- 库存 TOP10(横向柱状) INSERT INTO `bi_chart_config` (`dashboard_id`, `name`, `chart_type`, `data_source_id`, `query_sql`, `position`, `chart_options`, `refresh_interval`, `sort`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT d.id, '库存数量 TOP10', 'bar', NULL, 'SELECT i.name AS label, ROUND(SUM(s.quantity-s.frozen_quantity-s.reserved_quantity),0) AS value FROM mes_wm_material_stock s JOIN mes_md_item i ON i.id=s.item_id AND i.deleted=0 WHERE s.deleted=0 GROUP BY i.name ORDER BY value DESC LIMIT 10', '{"h": 8, "w": 12, "x": 12, "y": 4}', NULL, 120, 24, 1, '1', NOW(), '1', NOW(), b'0' FROM `bi_dashboard` d WHERE d.`code` = 'purchase_sales' AND d.deleted = b'0' AND NOT EXISTS (SELECT 1 FROM `bi_chart_config` c WHERE c.`dashboard_id` = d.id AND c.`name` = '库存数量 TOP10' AND c.deleted = b'0'); -- ---------- 4. 校验 ---------- SELECT d.`code` AS 大屏, COUNT(*) AS 图表数 FROM bi_chart_config c JOIN bi_dashboard d ON d.id = c.dashboard_id WHERE d.`code` IN ('hr_oa','purchase_sales') AND c.deleted = b'0' GROUP BY d.`code`; SELECT id, name, visible+0 AS vis FROM system_menu WHERE id = 1063005;