Raw Source Toggle Dropdown

• Minified Source

qinzhanqiang / Bi Enhance V2

// ==UserScript==
// @name         Bi Enhance V2
// @namespace    http://tampermonkey.net/
// @version      2024-05-17
// @description  自动生成select*语句，自动进行时间戳转换，自动再where语句中增加一个时间范围查询组件判断条件。定时添加按钮，避免多个tab没有按钮的情况
// @author       qinzhanqiang
// @match        https://bi.sankuai.com/sql/edit*
// @icon         data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
// @grant        none
// @license      MIT
// ==/UserScript==

function addButton() {
    return function () {
        // select * 按钮
        const selectAllBtnArray = createSelectAllBtn();
        let beforeEle = document.querySelector("#app > div > div.sidebar-container > div > div.ms-sidebar-main > div > div.router-view > div > div > div.mtd-tabs-content > div > div > div.control-buttons > div > span > button");

        // 遍历按钮数组
        selectAllBtnArray.forEach(function (btn) {
            // 设置按钮样式，这里可以根据需要设置
            btn.style.cssText = 'display: inline-block;color:#fff;background:#0a70f5;white-space:nowrap;cursor:pointer;outline:0;text-align:center;font-weight:400;user-select:none;position:relative;transition:all.3s;border-radius:4px;min-width:32px;height:32px;font-size:14px;border-style:none;margin-right:10px;';

            // 将按钮插入到页面中的指定位置
            beforeEle.parentElement.insertBefore(btn, beforeEle);
        });
    };
}

(function() {
    'use strict';
    setTimeout(addButton(),1000);

    setTimeout(function (){
        const mtdTabAdd = document.querySelector('.mtd-tabs-action.mtd-tabs-add');
        mtdTabAdd.addEventListener('click', () => {
            console.log('Button was clicked.');
            setTimeout(addButton(),150);
        });
    }, 2000)
})();


/**
 * 代码格式化按钮
 * */
const beautify = function(){
    const matchingElement = document.querySelector("#app > div > div.sidebar-container > div > div.ms-sidebar-main > div > div.router-view > div > div > div.mtd-tabs-content > div > div > div.sql-item-main > div.editor-controls > div.editor-control-button-groups > div.format-control.control-item > button");
    matchingElement.click()
};


/**
 * 获取选中的表名称
 * */
const getTableName = function(){
    return document.getElementsByClassName("table-line show-column")[0].childNodes[1].childNodes[0].title;
};





/**
 * 获取表所有的行
 * */
const getColsName = function(){
    const tableCols = [];
    const table = document.getElementsByClassName("column-list")[0];
    const rows = table.rows;
    for(let i=1; i < rows.length; i++){
        const row = rows[i];
        const colName = row.cells[0].title;//获取具体单元格
        tableCols.push(colName);
    }
    return tableCols;
};


/**
 * 创建select*按钮
 * */
function createSelectAllBtn() {
    let selectAllBtnList = [];
    const cmList = document.getElementsByClassName("CodeMirror cm-s-ms-light CodeMirror-wrap");

    for (let i = 0; i < cmList.length; i++) {

        let elementsByClassName = document.getElementsByClassName("BiEnhance" + [i]);
        if (elementsByClassName.length > 0){
            continue;
        }

        let selectAllBtn = document.createElement("button");
        selectAllBtn.innerText = "select *";
        selectAllBtn.className = "BiEnhance" + [i]
        selectAllBtn.onclick = (function (index) {
            return function () {
                const cm = cmList[index].CodeMirror;
                const tableName = getTableName();
                let colsName = getColsName();
                let tableColsStr = getTableColsStr(colsName);
                let createTimeWhere = getCreateTimeWhereStr(colsName);

                // 拼写最终的sql
                const finalSql = "select " + tableColsStr + " from " + tableName + " where (1=1) " + createTimeWhere + " order by id desc limit 100";
                cm.setValue(finalSql);
                beautify();
            }
        })(i);
        selectAllBtnList.push(selectAllBtn);
    }

    return selectAllBtnList;
}

/**
 * 获取表所有的行拼接成字符串，如果发现有"ctime", "utime", "optime", "create_time", "modify_time" 自动加一行转换成正常时间格式
 * */
const getTableColsStr = function (tableCols) {
    let result = "";
    for (let i=0;i<tableCols.length;i++){
        if (result.length > 0){
            result += ", ";
        }
        result += "`" + tableCols[i] + "`";
        if (tableCols[i].includes("time")){
            result += ", FROM_UNIXTIME(`" + tableCols[i] + "`/1000)";
        }
    }
    return result;
}

/**
 * 获取创建时间where条件，如果字符中包含["ctime",  "optime", "create_time"]字段自动加上根据这个字段组件查询条件
 * */
const getCreateTimeWhereStr= function (tableCols) {
    let timeList = ["ctime", "optime", "create_time"];
    for (let i=0;i<tableCols.length;i++){
        if (timeList.includes(tableCols[i])){
            return "and ("+ tableCols[i] +" BETWEEN UNIX_TIMESTAMP(\"$$begindate 00:00:00\") * 1000 and UNIX_TIMESTAMP(\"$$enddate 23:59:59\") * 1000)";
        }
    }
    return "";
}