{% extends "base.html" %}

{% block content %}
    <div class="row clearfix">
        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading">
                    分析选项
                </div>
                <div class="panel-body form-inline">
                    <div class="form-group">
                        <input id="sql-upload" name="sql-upload" accept=".sql,.xml" type="file" class="file-loading">
                    </div>
                    <div class="form-group">
                        <select id="instance_name" name="instance_name"
                                class="selectpicker show-tick form-control bs-select-hidden" data-live-search="true"
                                title="请选择实例:"
                                data-placeholder="请选择实例:" required>
                        </select>
                    </div>
                    <div class="form-group">
                        <select id="db_name" name="db_name" class="form-control selectpicker show-tick bs-select-hidden"
                                title="请选择数据库:"
                                data-live-search="true" data-placeholder="请选择数据库:" required>
                            <option value="all">全部</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <input id="btn-analyze" type="button" class="btn btn-warning" value="开始分析"
                               onclick="analyze()"/>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-md-12 column">
            <div class="panel panel-default">
                <div class="panel-heading">
                    SQL列表
                </div>
                <div class="panel-body">
                    <div class="table-responsive">
                        <table id="analyze-sql" data-toggle="table" class="table table-striped table-hover"
                               style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;"></table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- 分析报告-->
    <div class="modal fade" id="reports">
        <div class="modal-dialog modal-lg">
            <div class="modal-content message_align">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
                            aria-hidden="true">×</span></button>
                    <h4 class="modal-title text-danger">分析报告</h4>
                </div>
                <div id="reports-body" class="modal-body">
                </div>
                <div class="modal-footer">
                </div>
            </div>
        </div>
    </div>
{% endblock content %}
{% block js %}
    {% load static %}
    <link href="{% static 'bootstrap-fileinput/css/fileinput.min.css' %}" rel="stylesheet">
    <script src="{% static 'bootstrap-fileinput/js/fileinput.min.js' %}"></script>
    <script src="{% static 'bootstrap-fileinput/js/locales/zh.js' %}"></script>
    <script src="{% static 'bootstrap-table/js/bootstrap-table-export.min.js' %}"></script>
    <script src="{% static 'bootstrap-table/js/tableExport.min.js' %}"></script>
    <script src="{% static 'dist/js/marked.min.js' %}"></script>
    <!-- 解析文件 -->
    <script>
        //初始化上传控件
        $(function init_upload() {
            $("#sql-upload").fileinput({
                language: 'zh',
                allowedFileExtensions: ['sql', 'xml'],//接收的文件后缀
                showCaption: true,//是否显示标题
                initialCaption: 'SQL/MyBatis3 Mapper XML file',
                captionClass: "",
                showUpload: false,     //不显示上传按钮
                showPreview: false,    // 不显示预览,
            }).on('fileloaded', function (event, data, previewId, index) {
                if (data.size <= 10240 * 512) {
                    loadsql();
                } else {
                    $('#sql-upload').fileinput('reset');
                    alert('文件大小超过5M，请重新选择');
                }
            }).on('fileclear', function (event) {
                generate();
            });
        });


        //读取文件并展示在输入域
        function loadsql() {
            $('#sql-upload').fileinput('disable');
            //获取文件
            var file = $("#sql-upload")[0].files[0];

            //创建读取文件的对象
            var reader = new FileReader();

            //为文件读取成功设置事件
            reader.onload = function (e) {
                var text = e.target.result;
                generate(text);
            };

            //正式读取文件
            reader.readAsText(file);
        }

        //获取解析后的SQL列表
        function generate(text) {
            //初始化table
            $('#analyze-sql').bootstrapTable('destroy').bootstrapTable({
                escape: true,
                method: 'post',
                contentType: "application/x-www-form-urlencoded",
                url: "/sql_analyze/generate/",
                striped: true,                      //是否显示行间隔色
                cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                pagination: true,                   //是否显示分页（*）
                sortable: false,                     //是否启用排序
                sortOrder: "asc",                   //排序方式
                sidePagination: "client",           //分页方式：client客户端分页，server服务端分页（*）
                pageNumber: 1,                      //初始化加载第一页，默认第一页,并记录
                pageSize: 20,                     //每页的记录行数（*）
                pageList: [20, 30, 50, 100],       //可供选择的每页的行数（*）
                showExport: true,                   //是否显示导出按钮
                exportOptions: {},
                search: true,                      //是否显示表格搜索
                strictSearch: false,                //是否全匹配搜索
                showColumns: true,                  //是否显示所有的列（选择显示的列）
                showRefresh: false,                  //是否显示刷新按钮
                minimumCountColumns: 2,             //最少允许的列数
                clickToSelect: false,                //是否启用点击选中行
                showToggle: true,                   //是否显示详细视图和列表视图的切换按钮
                cardView: false,                    //是否显示详细视图
                detailView: true,                  //是否显示父子表
                //格式化详情
                detailFormatter: function (index, row) {
                    var html = [];
                    $.each(row, function (key, value) {
                        if (key === 'sql') {
                            var sql = window.sqlFormatter.format(value);
                            //替换标签
                            sql = sql.replace(/&/g, "&amp;");
                            sql = sql.replace(/</g, "&lt;");
                            sql = sql.replace(/>/g, "&gt;");
                            sql = sql.replace(/"/g, "&quot;");
                            //替换所有的换行符
                            sql = sql.replace(/\r\n/g, "<br>");
                            sql = sql.replace(/\n/g, "<br>");
                            //替换所有的空格
                            sql = sql.replace(/\s/g, "&nbsp;");
                            html.push('<span>' + sql + '</span>');
                        }
                    });
                    return html.join('');
                },
                locale: 'zh-CN',                    //本地化
                toolbar: "#toolbar",               //指明自定义的toolbar
                queryParamsType: 'limit',
                //请求服务数据时所传参数
                queryParams:
                    function (params) {
                        return {
                            text: text,
                            offset: params.offset,
                        }
                    },
                columns: [{
                    title: 'SQLID',
                    field: 'sql_id'
                }, {
                    title: 'SQL内容',
                    field: 'sql',
                    formatter: function (value, row, index) {
                        if (value.length > 100) {
                            var sql = value.substr(0, 100) + '...';
                            return sql;
                        } else {
                            return value
                        }
                    }
                }, {
                    title: '完整SQL内容',
                    field: 'sql',
                    visible: false // 默认不显示
                }],
                onLoadSuccess: function () {
                    $('#sql-upload').fileinput('enable');
                    sessionStorage.setItem('analyze_text', text);
                },
                onLoadError: onLoadErrorCallback,
                responseHandler: function (res) {
                    //在ajax获取到数据，渲染表格之前，修改数据源
                    return res;
                }
            });

        }
    </script>
    <!-- 获取数据库名称 -->
    <script>
        $("#instance_name").change(function () {
            //将数据通过ajax提交给获取db_name
            $.ajax({
                type: "get",
                url: "/instance/instance_resource/",
                dataType: "json",
                data: {
                    instance_name: $("#instance_name option:selected").text(),
                    resource_type: "database"
                },
                complete: function () {
                },
                success: function (data) {
                    if (data.status === 0) {
                        var result = data.data;
                        $("#db_name").empty();
                        for (var i = 0; i < result.length; i++) {
                            var name = "<option>" + result[i] + "</option>";
                            $("#db_name").append(name);
                        }
                        $('#db_name').selectpicker('render');
                        $('#db_name').selectpicker('refresh');
                    } else {
                        alert(data.msg);
                    }
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        });
    </script>
    <!-- 执行分析获取结果 -->
    <script>
        function analyze() {
            var analyze_text = sessionStorage.getItem('analyze_text');
            if (analyze_text !== "undefined") {
                $('#btn-analyze').addClass('disabled');
                $('#btn-analyze').prop('disabled', true);
                //初始化table
                $('#analyze-sql').bootstrapTable('destroy').bootstrapTable({
                    escape: true,
                    method: 'post',
                    contentType: "application/x-www-form-urlencoded",
                    url: "/sql_analyze/analyze/",
                    striped: true,                      //是否显示行间隔色
                    cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                    pagination: true,                   //是否显示分页（*）
                    sortable: false,                     //是否启用排序
                    sortOrder: "asc",                   //排序方式
                    sidePagination: "client",           //分页方式：client客户端分页，server服务端分页（*）
                    pageNumber: 1,                      //初始化加载第一页，默认第一页,并记录
                    pageSize: 20,                     //每页的记录行数（*）
                    pageList: [20, 30, 50, 100],       //可供选择的每页的行数（*）
                    showExport: true,                   //是否显示导出按钮
                    exportOptions: {},
                    search: true,                      //是否显示表格搜索
                    strictSearch: false,                //是否全匹配搜索
                    showColumns: true,                  //是否显示所有的列（选择显示的列）
                    showRefresh: false,                  //是否显示刷新按钮
                    minimumCountColumns: 2,             //最少允许的列数
                    clickToSelect: false,                //是否启用点击选中行
                    uniqueId: "id",                     //每一行的唯一标识，一般为主键列
                    showToggle: true,                   //是否显示详细视图和列表视图的切换按钮
                    cardView: false,                    //是否显示详细视图
                    detailView: true,                  //是否显示父子表
                    //格式化详情
                    detailFormatter: function (index, row) {
                        var html = [];
                        $.each(row, function (key, value) {
                            if (key === 'report') {
                                var report = value;
                                var rendererMD = new marked.Renderer();
                                marked.setOptions({
                                    renderer: rendererMD,
                                    gfm: true,
                                    tables: true,
                                    breaks: true,
                                    pedantic: false,
                                    sanitize: false,
                                    smartLists: false,
                                    smartypants: false
                                });
                                //增加的代码，用于个性化输出table
                                rendererMD.table = function (header, body) {
                                    return '<div class="table-responsive"><table class="table table-condensed">' + header + body + '</table></div>'
                                };
                                html.push(marked(report))
                            }
                        });
                        return html.join('');
                    },
                    locale: 'zh-CN',                    //本地化
                    toolbar: "#toolbar",               //指明自定义的toolbar
                    queryParamsType: 'limit',
                    //请求服务数据时所传参数
                    queryParams:
                        function (params) {
                            return {
                                text: sessionStorage.getItem('analyze_text'),
                                instance_name: $("#instance_name").val(),
                                db_name: $("#db_name").val(),
                                offset: params.offset,
                            }
                        },
                    columns: [{
                        title: 'SQLID',
                        field: 'sql_id'
                    }, {
                        title: 'SQL内容',
                        field: 'sql',
                        formatter: function (value, row, index) {
                            if (value.length > 100) {
                                var sql = value.substr(0, 100) + '...';
                                return sql;
                            } else {
                                return value
                            }
                        }
                    }, {
                        title: '完整SQL内容',
                        field: 'sql',
                        visible: false // 默认不显示
                    }, {
                        title: '分析报告',
                        field: 'operation',
                        formatter: function (value, row, index) {
                            return "<button class=\"btn btn-info btn-xs\" report=\"" + row.report.replace(/"<>\.*/gm,'<\>才是标准SQL中的不等于运算符') + "\"\n" + "onclick=\"getReport(this)\" >查看\n" + "</button>"
                        }
                    }],
                    onLoadSuccess: function () {
                        $('#btn-analyze').removeClass('disabled');
                        $('#btn-analyze').prop('disabled', false);
                    },
                    onLoadError: function (status, jqXHR) {
                        $('#btn-analyze').removeClass('disabled');
                        $('#btn-analyze').prop('disabled', false);
                        onLoadErrorCallback(status, jqXHR);
                    },
                    responseHandler: function (res) {
                        //在ajax获取到数据，渲染表格之前，修改数据源
                        return res;
                    }
                });
                // 提示信息
                if ($('#instance_name').val() && $('#db_name').val()) {
                    alert('已提交SOAR分析，请耐心等待分析结果!');
                } else {
                    alert('已提交SOAR分析，未选择实例和数据库将不会输出执行计划和索引建议，请耐心等待分析结果!');
                }
            } else {
                alert('请先解析SQL/MyBatis3 Mapper XML文件');
            }

        }
    </script>
    <!-- 查看分析报告 -->
    <script>
        function getReport(obj) {
            var report = $(obj).attr("report");
            var rendererMD = new marked.Renderer();
            marked.setOptions({
                renderer: rendererMD,
                gfm: true,
                tables: true,
                breaks: true,
                pedantic: false,
                sanitize: false,
                smartLists: false,
                smartypants: false
            });
            //增加的代码，用于个性化输出table
            rendererMD.table = function (header, body) {
                return '<div class="table-responsive"><table class="table table-condensed">' + header + body + '</table></div>'
            };
            $("#reports-body").html(marked(report));
            $('#reports').modal('show');
        }
    </script>
    <script>
        $(document).ready(function () {
            //获取用户实例列表
            $(function () {
                $.ajax({
                    type: "get",
                    url: "/group/user_all_instances/",
                    dataType: "json",
                    data: {
                        db_type: ['mysql']
                    },
                    complete: function () {
                    },
                    success: function (data) {
                        if (data.status === 0) {
                            let result = data['data'];
                            $("#instance_name").empty();
                            for (let i = 0; i < result.length; i++) {
                                let instance = "<option value=\"" + result[i]['instance_name'] + "\">" + result[i]['instance_name'] + "</option>";
                                $("#instance_name").append(instance);
                            }
                            $('#instance_name').selectpicker('render');
                            $('#instance_name').selectpicker('refresh');
                        } else {
                            alert(data.msg);
                        }
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(errorThrown);
                    }
                });
            });
            sessionStorage.setItem('analyze_text', '');
            generate();
        })
    </script>
{% endblock %}

