Raw Source Toggle Dropdown

• Minified Source

qinzhanqiang / Bi Enhance

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

(function() {
    'use strict';

    // select * 按钮
    const selectAllBtn = createSelectAllBtn();

    // 后续新增其他按钮可以在这里添加
    const btnList = [selectAllBtn];

    setTimeout( function(){
        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");

        // 遍历按钮
        for (const i in btnList){
            const curBtn = btnList[i];
            if(i === 0){
                curBtn.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;';
            }else{
                curBtn.style.cssText='color: #0a70f5;border-color: transparent!important;background-color: #0000;font-size: 14px;font-weight: 400;cursor: pointer;';
            }

            beforeEle.parentElement.insertBefore(curBtn,beforeEle);
            beforeEle=curBtn;
        }
    },3000);
})();

/**
 * 代码格式化按钮
 * */
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 getCodeMirror = function(tag=0){
    const cmList = document.getElementsByClassName("CodeMirror cm-s-ms-light CodeMirror-wrap");
    if(tag === 0){
        return cmList[0].CodeMirror;
    }else{
        return cmList[cmList.length-1].CodeMirror;
    }
};


/**
 * 获取表所有的行
 * */
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 selectAllBtn=document.createElement("button");
    selectAllBtn.innerText="select *";
    selectAllBtn.className="BiEnhance"
    selectAllBtn.onclick=function(){
        const cm = getCodeMirror();
        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();
    }
    return selectAllBtn;
}

/**
 * 获取表所有的行拼接成字符串，如果发现有"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 "";
}