图表和数据库联动

(async () => { // 关联的数据库块id，这里的id根据需要改成自己的 const avBlockId = '20240727220211-p1awn4b'; // 关联的图表块id，这里的id根据需要改成自己的 const chartBlockId = '20240805130000-vhz1aei'; // 自动刷新延迟，单位是毫秒，默认是1秒，0则不自动刷新 const autoFreshDelay = 1000; // 定义图表选项，这里的数据是什么都没关系，这里只是参考数据，下面的getAVDataByBlockId函数会覆盖这里的数据 let option = { "title": { "text": "网站访问统计", "left": "center", "top": "top" }, "tooltip": { "trigger": "axis" }, "xAxis": { "type": "category", "data": [ "第一季度", "第二季度", "第三季度", "第四季度" ], "axisLabel": { "rotate": 0 } }, "yAxis": { "type": "value" }, "series": [ { "data": [ 1495, 1678, 1750, 1096 ], "type": "bar", "barWidth": 30, "label": { "show": true, "position": "top" }, "name": "2020" }, { "data": [ 1208, 1225, 1098, 1326 ], "type": "bar", "barWidth": 30, "label": { "show": true, "position": "top" }, "name": "2021" }, { "data": [ 2548, 1574, 2534, 1038 ], "type": "bar", "barWidth": 30, "label": { "show": true, "position": "top" }, "name": "2022" } ], "legend": { "data": [ "2020", "2021", "2022" ], "left": "center", "top": "bottom" } } // 获取数据库信息并格式化数据，这里av是从数据库获取的数据 await getAVDataByBlockId(avBlockId, (av) => { // 修改选项标题 option.title.text = av.name; // 修改option x 轴文字 option.xAxis.data = av.keyValues[0].values.map(item => item.block.content); // 修改option series 数据 option.series = av.keyValues.slice(1).map(item => { const data = item.values.map(item => item[item.type].content); return { "data": data, "type": "bar", "barWidth": 30, "label": { "show": true, "position": "top" }, "name": item.key.name } }); }); ////////////////////////////////// 以下代码不涉及数据配置，一般不需要改动 //////////////////////////////////// // 监听av变化，当数据库块被修改时，重新获取数据 if(autoFreshDelay > 0 && !window['__chat_observe__' + avBlockId]) { window['__chat_observe__' + avBlockId] = observeDOMChanges(document.querySelector('.layout__center div[data-node-id="'+avBlockId+'"]'), ()=>{ freshChart(chartBlockId); }, autoFreshDelay, {attributes: false}); } // 输出运行状态，方便调试 console.log('render chart start'); // 获取数据库信息并格式化数据 async function getAVDataByBlockId(blockId, callback) { // 获取块信息 const block = await fetchSyncPost('/api/query/sql', {"stmt": `SELECT * FROM blocks WHERE id = '${blockId}'`}) const markdown = block.data[0]?.markdown; // 获取数据库信息 if(markdown){ // 获取数据库文件地址 const avId = getDataAvIdFromHtml(markdown); // 通过sy文件获取表格数据，按列排列，这里更合适 const av = await fetchSyncPost('/api/file/getFile', {"path":`/data/storage/av/${avId}.json`}); // 通过renderAttributeView获取表格数据，按行排列 //const av = await fetchSyncPost('/api/av/renderAttributeView', {"id":avId,"viewID":"","query":""}); // 格式化数据选项 if(av){ if(typeof callback === 'function') callback(av); } else { option = "未找到av-id=" + avId + "的数据库文件"; } } else { option = "未找到id=" + avBlockId + "的数据库块"; } } // 请求api async function fetchSyncPost (url, data) { const init = { method: "POST", }; if (data) { if (data instanceof FormData) { init.body = data; } else { init.body = JSON.stringify(data); } } const res = await fetch(url, init); const res2 = await res.json(); return res2; } // 获取avid function getDataAvIdFromHtml(htmlString) { // 使用正则表达式匹配data-av-id的值 const match = htmlString.match(/data-av-id="([^"]+)"/); if (match && match[1]) { return match[1]; // 返回匹配的值 } return ""; // 如果没有找到匹配项，则返回空 } // 刷新图表 async function freshChart(chartBlockId) { const ZWSP = "\u200b"; const looseJsonParse = (text) => { return Function(`"use strict";return (${text})`)(); }; const e = document.querySelector('.layout__center div[data-subtype="echarts"][data-node-id="'+chartBlockId+'"]') let width = undefined; if (e.firstElementChild.clientWidth === 0) { const tabElement = hasClosestByClassName(e, "layout-tab-container", true); if (tabElement) { Array.from(tabElement.children).find(item => { if (item.classList.contains("protyle") && !item.classList.contains("fn__none")) { width = item.querySelector(".protyle-wysiwyg").firstElementChild.clientWidth; return true; } }); } } const wysiswgElement = hasClosestByClassName(e, "protyle-wysiwyg", true); if (!e.firstElementChild.classList.contains("protyle-icons")) { e.insertAdjacentHTML("afterbegin", genIconHTML(wysiswgElement)); } const renderElement = e.firstElementChild.nextElementSibling; try { renderElement.style.height = e.style.height; const option = await looseJsonParse(Lute.UnEscapeHTMLStr(e.getAttribute("data-content"))); window.echarts.init(renderElement, window.siyuan.config.appearance.mode === 1 ? "dark" : undefined, {width}).setOption(option); e.setAttribute("data-render", "true"); renderElement.classList.remove("ft__error"); if (!renderElement.textContent.endsWith(ZWSP)) { renderElement.firstElementChild.insertAdjacentText("beforeend", ZWSP); } } catch (error) { window.echarts.dispose(renderElement); renderElement.classList.add("ft__error"); renderElement.innerHTML = `echarts render error:
${error}`; } } function hasClosestByClassName(element, className, top = false) { if (!element) { return false; } if (element.nodeType === 3) { element = element.parentElement; } let e = element; let isClosest = false; while (e && !isClosest && (top ? e.tagName !== "BODY" : !e.classList.contains("protyle-wysiwyg"))) { if (e.classList?.contains(className)) { isClosest = true; } else { e = e.parentElement; } } return isClosest && e; } function genIconHTML(element) { let enable = true; if (element) { const readonly = element.getAttribute("contenteditable"); if (typeof readonly === "string") { enable = element.getAttribute("contenteditable") === "true"; } else { return ''; } } return ``; } // 监听dom变化 let observeTimer = null; function observeDOMChanges(targetNode, callback, debounceTime = 1000, options = {}) { // 默认配置 const defaultOptions = { attributes: true, childList: true, subtree: true, }; // 合并默认配置与传入的配置 const config = Object.assign({}, defaultOptions, options); // 创建一个观察器实例 const observer = new MutationObserver((mutationsList) => { // 使用防抖函数确保单位时间内最多只调用一次回调 if(observeTimer) { clearTimeout(observeTimer); } observeTimer = setTimeout(() => { // 处理变化 callback(mutationsList); }, debounceTime); }); // 开始观察目标节点 observer.observe(targetNode, config); // 返回一个函数，以便在不需要时能够停止观察 return () => { observer.disconnect(); }; } return option; })()