1111.zip

<%@ include file="system/easyui_header_plugin.jsp"%>

rowheight=50;

MyTextBox('','text1','文本',100,rowheight*0,50,34,300);
MyNumberbox('','number11','文本',100,rowheight*1,50,34,300,2,44.44,100,0);
MyDatebox('','date1','文本',100,rowheight*2,50,34,300,'2019-2-3');

function MyTextBox(parent,id,label,labelwidth,top,left,height,width,style,value){
var s = '< div style="position:absolute;top:'+top+'px;left:'+left+'px;">< input class="easyui-textbox" id="'+id+'">< /div>';
if(parent=='')$("body").append(s);
else $("#"+parent).append(s);
$("#"+id).textbox({
width:width,
height:height,
label:label,
labelWidth:labelwidth,
prompt:'helloworld'
});
if(value!=undefined)$("#"+id).textbox({value:value});
}

function MyNumberbox(parent,id,label,labelwidth,top,left,height,width,precision,value,max,min){
var s = '< div style="position:absolute;top:'+top+'px;left:'+left+'px;">< input class="easyui-numberbox" id="'+id+'">< /div>';
if(parent=='')$("body").append(s);
else $("#"+parent).append(s);
$("#"+id).numberbox({
width:width,
height:height,
label:label,
labelWidth:labelwidth,
precision:precision,
max:max,
min:min
});
if(value!=undefined)$("#"+id).numberbox({value:value});
}

function MyDatebox(parent,id,label,labelwidth,top,left,height,width,value){
var s = '< div style="position:absolute;top:'+top+'px;left:'+left+'px;">< input class="easyui-datebox" id="'+id+'">< /div>';
if(parent=='')$("body").append(s);
else $("#"+parent).append(s);
$("#"+id).datebox({
width:width,
height:height,
label:label,
labelWidth:labelwidth
});
if(value!=undefined)$("#"+id).datebox({value:value});
}

var data = {};
data.columns=[
{title:'TEST1',field:'a', width:100, halign:'center', align:'center'},
{title:'AAA',field:'b', width:180, halign:'center'},
{title:'BBBB',field:'c', width:60, halign:'center', align:'center',
formatter: function(value,row,index){
if (value=='123') s='< div style="color:red">'+value+'< /div>';
else s = value;
return s;
} }
];
data.frozenColumns=[
{title:'选中',field:'_checkid', width:32, halign:'center', align:'center', checkbox:true},
{title:'ID',field:'id', width:70, halign:'center', align:'center',
formatter: function(value,row,index){
s='< div style="font-family:'times new roman';">'+value+'< /div>';
return s;
}
}
];
data.pagination = true;
data.fit=true;
data.fitColumns=true;
data.onSelect = function fun1(index,row){
console.log('test');
};
data.onSelectPage = 'sp2'; //为''时为空方法、为'xxx'时指定xxx存储过程,为函数体时指定函数体
data.filter=''; //指定过滤词条
//data.filterid=''; //指定过滤的Textbox id
MyPagerdatagrid('sad','mygrid1',20,30,300,400,data);

//$("#mygrid1").datagrid('loadData',[{id:'1',A:'2',B:'3',C:'C'},{id:'3',A:'4',B:'5',C:'D'}]);

function MyPagerdatagrid(parent,id,top,left,height,width,data){
data.id = id;
var s = '< table class="easyui-datagrid" id="'+id+'" style="position:absolute;top:'+top+'px;left:'+left+'px;"></table>';
if(parent=='')$("body").append(s);
else $("#"+parent).append(s);
$('#'+id).datagrid({
width:width,
height:height,
border: data.border!=undefined?data.border:true,
rownumbers: data.rownumbers!=undefined?data.rownumbers:true,
fitColumns: data.fitColumns!=undefined?data.fitColumns:false,
//rownumberWidth: 50,
pagination: data.pagination!=undefined?data.pagination:false,
pageNumber: data.pageNumber!=undefined?data.pageNumber:1,
pageSize: data.pageSize!=undefined?data.pageSize:20,
fit: data.fit!=undefined?data.fit:true,
singleSelect: data.singleSelect!=undefined?data.singleSelect:true,
selectOnCheck: data.selectOnCheck!=undefined?data.selectOnCheck:false,
checkOnSelect: data.checkOnSelect!=undefined?data.checkOnSelect:false,
striped: data.striped!=undefined?data.striped:true,
nowrap: data.nowrap!=undefined?data.nowrap:true,
idField: data.idField!=undefined?data.idField:'',
columns:data.columns!=undefined?[data.columns]:[[]],
frozenColumns:data.frozenColumns!=undefined?[data.frozenColumns]:[[]],
onSelect:data.onSelect!=undefined&&typeof(data.onSelect)=='function'?data.onSelect:function(index, row){}
});
if(data.pagination==true){
$("#"+id).datagrid("getPager").pagination({
pageList: data.pageList!=undefined?data.pageList:[10,20,50,100],
showPageList: data.pageList!=undefined||data.showPageList!=data.pageList?data.showPageList:true,
beforePageText: data.beforePageText!=undefined?data.beforePageText:'第',
afterPageText:data.afterPageText!=undefined?data.afterPageText:'页 共{pages}页',
displayMsg:data.displayMsg!=undefined?data.displayMsg:'当前显示{from}~{to}行,共{total}行',
onRefresh:function(pageno,pagesize){
},
onChangepageSize:function(pagesize){
},
//假如data.onSelectPage为函数体则赋值,为string则填充fnLoadGridData。
onSelectPage:data.onSelectPage!=undefined&&typeof(data.onSelectPage)=='function'?data.onSelectPage:

typeof(data.onSelectPage)=='string'&&data.onSelectPage!=''?function fnLoadGridData(pageno, pagesize){
var opts = $("#"+data.id).datagrid('options');
opts.pageNumber=pageno;
opts.pageSize=pagesize;
var id=opts.id;
var p={};
p.pageno=pageno;
p.pagesize=pagesize;
if(data.filter!=undefined||data.filterid!=undefined){ //过滤词条判断
 if(data.filter!=undefined)p.filtertext=data.filter;
 else p.filtertext = $('#'+data.filterid).textbox('getValue'); 
}
rs=myRunSqlProcedure(data.onSelectPage, p);
if (rs.rows.length>0) rs.total=rs.rows[0].total;
else rs.total=0;
$('#'+data.id).datagrid('clearChecked');
$('#'+data.id).datagrid('loadData', rs);
$('#'+data.id).datagrid('selectRow', 0);

}:function(pageno, pagesize){

//fnLoadGridData(pageno, pagesize);
console.log("空方法");

}
});
console.log("当data.onSelectPage为空时、将该函数体自动赋值给data.onSelectPage避免后续出错");
var opts = $("#"+id).datagrid('options');
$("#"+id).datagrid("getPager").pagination('options').onSelectPage(opts.pageNumber,opts.pageSize);
//if(data.onSelectPage==undefined||typeof(data.onSelectPage)=='function'||typeof(data.onSelectPage)=='string')
//data.onSelectPage=$("#"+id).datagrid("getPager").pagination('options').onSelectPage;
//data.onSelectPage(opts.pageNumber,opts.pageSize); //初始化第一页
}
}

 <div class="easyui-panel" data-options="region:'center'" style="border:0px; border-top:1px solid #95B8E7;">
        <div id="myTab" class="easyui-tabs" style="width:100%; height:100%">
              <div id="myTab1" title="基本信息" data-options="closable: false" style="position:relative; overflow:auto;padding:20px;display:none;"></div>
                <div id="myTab2" title="上传mp3" data-options="closable: false" style="position:relative; overflow:auto;padding:20px;display:none;">
                    <div id="filepath_div" style="position:absolute; top:16px; left:20px;"><input id="filepath" name="filepath" type="filebox" class="easyui-filebox" /></div>
                </div>
            </div>        
    </div>    

myTextField('title', 'myTab1', '资源名称:', 68, 10+0*rowheight, 20, 0, 580, '');

myButton('cmdupload', 'myTab1', '上传', 10+1*rowheight+30, 20+648-68, 28, 68, '', '');

myLabel('label1','myTab1', '已上传文件:', 120, 8);

$('#filebox1').filebox({
    label: '资源文件:',
    labelWidth: 68,
    buttonText: '选择文件',
    buttonAlign: 'right',
    multiple: true,  
    width: 648,
    height: 28
});

if (uploaderror!=''){
   myMessagebox(uploaderror, 'error', 0, 300);
    }else{
   $('#filebox1').attr('xfilename', JSON.stringify(data));
   fnShowFiles('filebox1');
    $("#filebox1").filebox('clear');    
   myMessagebox('文件已经上传成功!','',110, 280);
}

function fnRemove(index){

//删除已显示的一个文件
$('#myfile'+index).remove();    

}

function fnPreview(index){

var data=$('#filebox1').attr('xfilename');
data=JSON.parse(data);
var file=data[index];
var filesourcename=file.filesourcename;
var fileosname=file.fileosname; 
var fileext=file.fileext;
var xtitle=filesourcename;
var xtabs=$('#myTab').tabs('tabs');
var flag=0;
for (var i=1; i<xtabs.length; i++){
    if ($('#myTab'+parseInt(i+1)).attr('xfilename')==fileosname){
        flag=i;
        break;
    }
}
if (flag==0){
    var n=xtabs.length+1;
    var xid='myTab'+n;
    $('#myTab').tabs('add', {
        id: xid,
        closable:true,
        title: xtitle,
        selected: true
    });
    $('#'+xid).attr('xfilename', fileosname);
    posbegin=1;
    var newid=xid+'_'+fileext;
    if (fileext=='pdf'){
        var str='<iframe class="media" id="'+newid+'" href="'+fileosname+'#';
        str+='page=2&navpanes=1&scrollbar=1&toolbar=1&zoom=100" ';
        str+='style="position:absolute; overflow:auto;" ';
        str+='></iframe>';
        $('#'+xid).append(str);
        $('#'+xid).attr('xfileid', fileosname);  
        $('#'+xid).attr('xfiletype', fileext);
        $('#'+newid).media({width:'100%', height:'100%', autoplay: true});        
    }else if (fileext=='mp3' || fileext=='wav'){  
        //newid=myPreviewAudio(file, tab);
        str='<audio id="myMp3_'+n+'" controls="true" style="margin:20px;width:300px;"><audio>';
        $("#"+xid).append(str);
        var player= $("#"+'myMp3_'+n)[0];
        $("#"+'myMp3_'+n).attr('src',fileosname);
        player.muted=false;
        player.volume=0.5;
        player.loop=false;
        player.play();
    }else if ((';mp4;ogg;webm;').indexOf(';'+fileext+';')>=0){ 
    }else if ((';jpg;jpeg;gif;tif;tiff;png;').indexOf(';'+fileext+';')>=0){ //显示图片
        str='<img id="myImg_'+n+'" style=""></img>';
        $("#"+xid).append(str);
        var img = $("#myImg_"+n)[0];
        img.src=fileosname;
    }
}else $('#myTab').tabs('select', flag);

}

function fnShowFiles(id){

var data=$('#'+id).attr('xfilename');
var files=JSON.parse(data); 
str='';
for (var i=0; i<files.length; i++){
    str+='<span id="myfile'+i+'" style="background-color:#E0ECFF; font-size:13px; display:inline-block; height:42px; width:530px; padding:4px 6px 0px 6px; margin:4px 4px 0px 4px;">';
    str+='<div class="textdiv" style="width:420px; float:left;">'+files[i].filesourcename+'</div>';
    str+='<div style="float:right;"><a href="javascript:fnRemove('+i+')">[删除]</a></div>';
    str+='<div style="float:right; margin:0px 4px 0px 0px "><a href="javascript:fnPreview('+i+')">[预览]</a></div>';
    str+='<div style="clear:both;">'+files[i].filesizedesc+'</div>';
    str+='</span>';     
}
$('#myList1').html(str);

}

your code

$('#answer1').append(' rowheight=50;\
<div></div>\
<div>  MyTextBox("","text1","文本",100,rowheight*0,50,34,300);</div>\
<div>  MyNumberbox("","number11","文本",100,rowheight*1,50,34,300,2,44.44,100,0);</div>\
<div>  MyDatebox("","date1","文本",100,rowheight*2,50,34,300,"2019-2-3");</div>\
<div>  </div>\
<div>  function MyTextBox(parent,id,label,labelwidth,top,left,height,width,style,value){</div>\
<div>    var s = "< div style="position:absolute;top:"+top+"px;left:"+left+"px;">< input class="easyui-textbox" id=""+id+"">< /div>";</div>\
<div>    if(parent=="")$("body").append(s);</div>\
<div>    else $("#"+parent).append(s);</div>\
<div>    $("#"+id).textbox({</div>\
<div>      width:width,</div>\
<div>      height:height,</div>\
<div>      label:label,</div>\
<div>      labelWidth:labelwidth,</div>\
<div>      prompt:"helloworld"</div>\
<div>    });</div>\
<div>    if(value!=undefined)$("#"+id).textbox({value:value});</div>\
<div>  }</div>\
');

$('#answer1').append('function MyNumberbox(parent,id,label,labelwidth,top,left,height,width,precision,value,max,min){</div>\
<div>    var s = "< div style="position:absolute;top:"+top+"px;left:"+left+"px;">< input class="easyui-numberbox" id=""+id+"">< /div>";</div>\
<div>    if(parent=="")$("body").append(s);</div>\
<div>    else $("#"+parent).append(s);</div>\
<div>    $("#"+id).numberbox({</div>\
<div>      width:width,</div>\
<div>      height:height,</div>\
<div>      label:label,</div>\
<div>      labelWidth:labelwidth,</div>\
<div>      precision:precision,</div>\
<div>      max:max,</div>\
<div>      min:min</div>\
<div>    });</div>\
<div>    if(value!=undefined)$("#"+id).numberbox({value:value});</div>\
<div>  }</div>\
');

$('#answer1').append('function MyDatebox(parent,id,label,labelwidth,top,left,height,width,value){</div>\
<div>    var s = "< div style="position:absolute;top:"+top+"px;left:"+left+"px;">< input class="easyui-datebox" id=""+id+"">< /div>";</div>\
<div>    if(parent=="")$("body").append(s);</div>\
<div>    else $("#"+parent).append(s);</div>\
<div>    $("#"+id).datebox({</div>\
<div>      width:width,</div>\
<div>      height:height,</div>\
<div>      label:label,</div>\
<div>      labelWidth:labelwidth</div>\
<div>    });</div>\
<div>    if(value!=undefined)$("#"+id).datebox({value:value});</div>\
<div>  }</div>\
');

$('#answer1').append('drop procedure if exists d0703_1; \
<div>delimiter $$</div>\
<div>create procedure d0703_1(</div>\
<div>    $pageno int,</div>\
<div>    $pagesize int,</div>\
<div>    $filtertext varchar(500)</div>\
<div>)</div>\
<div>begin</div>\
<div>declare $start int;</div>\
<div>declare $s varchar(500);</div>\
<div>    set $s=concat("%", $filtertext, "%");</div>\
<div>    select count(*) into @n from x_students a left join x_departments b using(deptid)</div>\
<div>    where studentid like $s or name like $s or pycode like $s or gender like $s or address like $s or mobile like $s</div>\
<div>    or email like $s or weixin like $s or province like $s or city like $s;</div>\
<div>    if ($pagesize=0) then</div>\
<div>    set $pageno=1;</div>\
<div>    set $pagesize=@n;</div>\
<div>    end if;</div>\
<div>    set $start=($pageno-1)*$pagesize;</div>\
<div>    select a.*, b.deptname, @n as total from x_students a left join x_departments b using(deptid)</div>\
<div>    where studentid like $s or name like $s or pycode like $s or gender like $s or address like $s or mobile like $s</div>\
<div>    or email like $s or weixin like $s or province like $s or city like $s</div>\
<div>    order by studentid limit $start, $pagesize;</div>\
<div>end $$</div>\
<div>delimiter ;</div>\
<div>#call d0703_1(1,20, "国");</div>\
<div></div>\
<div>drop procedure if exists sp2;</div>\
<div>delimiter $$</div>\
<div>create procedure sp2(</div>\
<div>    $pageno int,</div>\
<div>    $pagesize int,</div>\
<div>    $filtertext varchar(255)</div>\
<div>)</div>\
<div>begin</div>\
<div>    set @sql="select studentid as id,name as A,gender as B,Deptid as C from x_students;";</div>\
<div>    call sys_gridPaging(@sql, $pageno, $pagesize, "A;B", $filtertext);</div>\
<div>end $$</div>\
<div>delimiter ;</div>\
<div>call sp2(1,10,"男");</div>\
');

$('#answer1').append('var data = {};</div>\
<div>  data.columns=[</div>\
<div>{title:"TEST1",field:"a", width:100, halign:"center", align:"center"},</div>\
<div>{title:"AAA",field:"b", width:180, halign:"center"},</div>\
<div>{title:"BBBB",field:"c", width:60, halign:"center", align:"center",</div>\
<div>formatter: function(value,row,index){</div>\
<div>if (value=="123") s="< div style="color:red">"+value+"< /div>";</div>\
<div>else s = value;</div>\
<div>return s;</div>\
<div>} }</div>\
<div>];</div>\
<div>  data.frozenColumns=[</div>\
<div>{title:"选中",field:"_checkid", width:32, halign:"center", align:"center", checkbox:true},</div>\
<div>{title:"ID",field:"id", width:70, halign:"center", align:"center",</div>\
<div>formatter: function(value,row,index){</div>\
<div>s="< div style="font-family:"times new roman";">"+value+"< /div>";</div>\
<div>return s;</div>\
<div>}</div>\
<div>}</div>\
<div>];</div>\
<div>  data.pagination = true;</div>\
<div>  data.fit=true;</div>\
<div>  data.fitColumns=true;</div>\
<div>  data.onSelect = function fun1(index,row){</div>\
<div>    console.log("test");</div>\
<div>  };</div>\
<div>  data.onSelectPage = "sp2"; //为""时为空方法、为"xxx"时指定xxx存储过程,为函数体时指定函数体</div>\
<div>  data.filter=""; //指定过滤词条</div>\
<div>  //data.filterid=""; //指定过滤的Textbox id</div>\
<div>  MyPagerdatagrid("sad","mygrid1",20,30,300,400,data);</div>\
<div>  </div>\
<div>  //$("#mygrid1").datagrid("loadData",[{id:"1",A:"2",B:"3",C:"C"},{id:"3",A:"4",B:"5",C:"D"}]);</div>\
<div>  </div>\
<div>  function MyPagerdatagrid(parent,id,top,left,height,width,data){</div>\
<div>    data.id = id;</div>\
<div>    var s = "< table class="easyui-datagrid" id=""+id+"" style="position:absolute;top:"+top+"px;left:"+left+"px;"></table>";</div>\
<div>    if(parent=="")$("body").append(s);</div>\
<div>    else $("#"+parent).append(s);</div>\
<div>    $("#"+id).datagrid({</div>\
<div>      width:width,</div>\
<div>      height:height,</div>\
<div>      border: data.border!=undefined?data.border:true,</div>\
<div>      rownumbers: data.rownumbers!=undefined?data.rownumbers:true,</div>\
<div>      fitColumns: data.fitColumns!=undefined?data.fitColumns:false,</div>\
<div>      //rownumberWidth: 50,</div>\
<div>      pagination: data.pagination!=undefined?data.pagination:false,</div>\
<div>      pageNumber: data.pageNumber!=undefined?data.pageNumber:1,</div>\
<div>      pageSize: data.pageSize!=undefined?data.pageSize:20,</div>\
<div>      fit: data.fit!=undefined?data.fit:true,</div>\
<div>      singleSelect: data.singleSelect!=undefined?data.singleSelect:true,</div>\
<div>      selectOnCheck: data.selectOnCheck!=undefined?data.selectOnCheck:false,</div>\
<div>      checkOnSelect: data.checkOnSelect!=undefined?data.checkOnSelect:false,</div>\
<div>      striped: data.striped!=undefined?data.striped:true,</div>\
<div>      nowrap: data.nowrap!=undefined?data.nowrap:true,</div>\
<div>      idField: data.idField!=undefined?data.idField:"",</div>\
<div>      columns:data.columns!=undefined?[data.columns]:[[]],</div>\
<div>      frozenColumns:data.frozenColumns!=undefined?[data.frozenColumns]:[[]],</div>\
<div>      onSelect:data.onSelect!=undefined&&typeof(data.onSelect)=="function"?data.onSelect:function(index, row){}</div>\
<div>    });</div>\
<div>    if(data.pagination==true){</div>\
<div>    $("#"+id).datagrid("getPager").pagination({</div>\
<div>      pageList: data.pageList!=undefined?data.pageList:[10,20,50,100],</div>\
<div>      showPageList: data.pageList!=undefined||data.showPageList!=data.pageList?data.showPageList:true,</div>\
<div>      beforePageText: data.beforePageText!=undefined?data.beforePageText:"第",</div>\
<div>      afterPageText:data.afterPageText!=undefined?data.afterPageText:"页 共{pages}页",</div>\
<div>      displayMsg:data.displayMsg!=undefined?data.displayMsg:"当前显示{from}~{to}行,共{total}行",</div>\
<div>      onRefresh:function(pageno,pagesize){</div>\
<div>      },</div>\
<div>      onChangepageSize:function(pagesize){</div>\
<div>      },</div>\
<div>      //假如data.onSelectPage为函数体则赋值,为string则填充fnLoadGridData。</div>\
<div>      onSelectPage:data.onSelectPage!=undefined&&typeof(data.onSelectPage)=="function"?data.onSelectPage:</div>\
<div>        typeof(data.onSelectPage)=="string"&&data.onSelectPage!=""?function fnLoadGridData(pageno, pagesize){</div>\
<div>        var opts = $("#"+data.id).datagrid("options");</div>\
<div>        opts.pageNumber=pageno;</div>\
<div>        opts.pageSize=pagesize;</div>\
<div>        var id=opts.id;</div>\
<div>        var p={};</div>\
<div>        p.pageno=pageno;</div>\
<div>        p.pagesize=pagesize;</div>\
<div>        if(data.filter!=undefined||data.filterid!=undefined){ //过滤词条判断</div>\
<div>          if(data.filter!=undefined)p.filtertext=data.filter;</div>\
<div>          else p.filtertext = $("#"+data.filterid).textbox("getValue");</div>\
<div>        }</div>\
<div>        rs=myRunSqlProcedure(data.onSelectPage, p);</div>\
<div>        if (rs.rows.length>0) rs.total=rs.rows[0].total;</div>\
<div>        else rs.total=0;</div>\
<div>        $("#"+data.id).datagrid("clearChecked");</div>\
<div>        $("#"+data.id).datagrid("loadData", rs);</div>\
<div>        $("#"+data.id).datagrid("selectRow", 0);</div>\
<div>      }:function(pageno, pagesize){</div>\
<div>        //fnLoadGridData(pageno, pagesize);</div>\
<div>        console.log("空方法");</div>\
<div>      }</div>\
<div>    });</div>\
<div>      console.log("当data.onSelectPage为空时、将该函数体自动赋值给data.onSelectPage避免后续出错");</div>\
<div>      var opts = $("#"+id).datagrid("options");</div>\
<div>      $("#"+id).datagrid("getPager").pagination("options").onSelectPage(opts.pageNumber,opts.pageSize);</div>\
<div>      //if(data.onSelectPage==undefined||typeof(data.onSelectPage)=="function"||typeof(data.onSelectPage)=="string")</div>\
<div>      //data.onSelectPage=$("#"+id).datagrid("getPager").pagination("options").onSelectPage;</div>\
<div>      //data.onSelectPage(opts.pageNumber,opts.pageSize); //初始化第一页</div>\
<div>    }</div>\
<div>  }</div>\
');

 <div class="easyui-panel" data-options="region:'center'" style="border:0px; border-top:1px solid #95B8E7;">
        <div id="myTab" class="easyui-tabs" style="width:100%; height:100%">
              <div id="myTab1" title="基本信息" data-options="closable: false" style="position:relative; overflow:auto;padding:20px;display:none;"></div>
                <div id="myTab2" title="上传mp3" data-options="closable: false" style="position:relative; overflow:auto;padding:20px;display:none;">
                    <div id="filepath_div" style="position:absolute; top:16px; left:20px;"><input id="filepath" name="filepath" type="filebox" class="easyui-filebox" /></div>
                </div>
            </div>        
    </div>    
    
    

$('#answer1').append('
<div>myTextField("title", "myTab1", "资源名称:", 68, 10+0*rowheight, 20, 0, 580, "");</div>\
<div></div>\
<div>myButton("cmdupload", "myTab1", "上传", 10+1*rowheight+30, 20+648-68, 28, 68, "", "");</div>\
<div></div>\
<div>myLabel("label1","myTab1", "已上传文件:", 120, 8);</div>\
<div></div>\
<div>$("#filebox1").filebox({</div>\
<div>label: "资源文件:",</div>\
<div>labelWidth: 68,</div>\
<div>        buttonText: "选择文件",</div>\
<div>        buttonAlign: "right",</div>\
<div>multiple: true,</div>\
<div>        width: 648,</div>\
<div>        height: 28</div>\
<div>});</div>\
<div></div>\
<div>if (uploaderror!=""){</div>\
<div>      myMessagebox(uploaderror, "error", 0, 300);</div>\
<div>        }else{</div>\
<div>      $("#filebox1").attr("xfilename", JSON.stringify(data));</div>\
<div>      fnShowFiles("filebox1");</div>\
<div>$("#filebox1").filebox("clear");</div>\
<div>      myMessagebox("文件已经上传成功!","",110, 280);</div>\
<div>}</div>\
<div></div>\
<div>function fnRemove(index){</div>\
<div>//删除已显示的一个文件</div>\
<div>$("#myfile"+index).remove();</div>\
<div>}</div>\
<div></div>\
<div>function fnPreview(index){</div>\
<div>var data=$("#filebox1").attr("xfilename");</div>\
<div>data=JSON.parse(data);</div>\
<div>var file=data[index];</div>\
<div>var filesourcename=file.filesourcename;</div>\
<div>var fileosname=file.fileosname;</div>\
<div>var fileext=file.fileext;</div>\
<div>var xtitle=filesourcename;</div>\
<div>var xtabs=$("#myTab").tabs("tabs");</div>\
<div>var flag=0;</div>\
<div>for (var i=1; i<xtabs.length; i++){</div>\
<div>if ($("#myTab"+parseInt(i+1)).attr("xfilename")==fileosname){</div>\
<div>flag=i;</div>\
<div>break;</div>\
<div>}</div>\
<div>}</div>\
<div>if (flag==0){</div>\
<div>var n=xtabs.length+1;</div>\
<div>var xid="myTab"+n;</div>\
<div>$("#myTab").tabs("add", {</div>\
<div>id: xid,</div>\
<div>closable:true,</div>\
<div>title: xtitle,</div>\
<div>selected: true</div>\
<div>});</div>\
<div>$("#"+xid).attr("xfilename", fileosname);</div>\
<div>posbegin=1;</div>\
<div>var newid=xid+"_"+fileext;</div>\
<div>if (fileext=="pdf"){</div>\
<div>var str="< iframe class="media" id=""+newid+"" href=""+fileosname+"#";</div>\
<div>str+="page=2&navpanes=1&scrollbar=1&toolbar=1&zoom=100" ";</div>\
<div>str+="style="position:absolute; overflow:auto;" ";</div>\
<div>str+=">< /iframe>";</div>\
<div>$("#"+xid).append(str);</div>\
<div>$("#"+xid).attr("xfileid", fileosname);</div>\
<div>$("#"+xid).attr("xfiletype", fileext);</div>\
<div>$("#"+newid).media({width:"100%", height:"100%", autoplay: true});</div>\
<div>}else if (fileext=="mp3" || fileext=="wav"){</div>\
<div>//newid=myPreviewAudio(file, tab);</div>\
<div>str="< audio id="myMp3_"+n+"" controls="true" style="margin:20px;width:300px;">< audio>";</div>\
<div>$("#"+xid).append(str);</div>\
<div>var player= $("#"+"myMp3_"+n)[0];</div>\
<div>$("#"+"myMp3_"+n).attr("src",fileosname);</div>\
<div>player.muted=false;</div>\
<div>player.volume=0.5;</div>\
<div>player.loop=false;</div>\
<div>player.play();</div>\
<div>}else if ((";mp4;ogg;webm;").indexOf(";"+fileext+";")>=0){</div>\
<div>}else if ((";jpg;jpeg;gif;tif;tiff;png;").indexOf(";"+fileext+";")>=0){ //显示图片</div>\
<div>str="< img id="myImg_"+n+"" style="">< /img>";</div>\
<div>$("#"+xid).append(str);</div>\
<div>var img = $("#myImg_"+n)[0];</div>\
<div>img.src=fileosname;</div>\
<div>}</div>\
<div>}else $("#myTab").tabs("select", flag);</div>\
<div>}</div>\
<div></div>\
<div>function fnShowFiles(id){</div>\
<div>var data=$("#"+id).attr("xfilename");</div>\
<div>var files=JSON.parse(data);</div>\
<div>str="";</div>\
<div>for (var i=0; i<files.length; i++){</div>\
<div>str+="< span id="myfile"+i+"" style="background-color:#E0ECFF; font-size:13px; display:inline-block; height:42px; width:530px; padding:4px 6px 0px 6px; margin:4px 4px 0px 4px;">";</div>\
<div>str+="< div class="textdiv" style="width:420px; float:left;">"+files[i].filesourcename+"< /div>";</div>\
<div>str+="< div style="float:right;">< a href="javascript:fnRemove("+i+")">[删除]< /a>< /div>";</div>\
<div>str+="< div style="float:right; margin:0px 4px 0px 0px ">< a href="javascript:fnPreview("+i+")">[预览]< /a>< /div>";</div>\
<div>str+="< div style="clear:both;">"+files[i].filesizedesc+"</div>";</div>\
<div>str+="< /span>";</div>\
<div>}</div>\
<div>$("#myList1").html(str);</div>\
<div>}</div>\
');

your code

use kms2020;

1)采用分层逐级展开树的方法构造一个树,树的第1层为省份(customers表中含有客户的省份),树的第2层为城市(customers表中含有客户的城市),树的第3层为客户编码与名称。没有客户的那些省份与城市不要列出显示。where +in

drop procedure if exists s1;
delimiter $$
create procedure s1()
begin
select distinct concat('1_',a.regionid) as id,b.areaname as text,1 as level,1 as isparentflag,'' as parentnodeid,'' as ancester from mysales.customers a join mysales.areas b on a.regionid = b.areaid;
end $$
delimiter ;

call s1();

drop procedure if exists s2;
delimiter $$
create procedure s2($parentnodeid varchar(20))
begin
select distinct concat('2_',a.cityid) as id,b.areaname as text,2 as level,1 as isparentflag,concat('1_',a.regionid) as parentnodeid,concat('1_',a.regionid,'#') as ancester from mysales.customers a join mysales.areas b on a.cityid = b.areaid
where concat('1_',a.regionid)=$parentnodeid;
end $$
delimiter ;

call s2();

drop procedure if exists s3;
delimiter $$
create procedure s3($parentnodeid varchar(20))
begin
select concat('3_',customerid) as id,concat(customerid,' ',companyname) as text,3 as level,0 as isparentflag,concat('2_',a.cityid) as parentnodeid,concat('1_',a.regionid,'#','2_',a.cityid,'#') as ancester from mysales.customers a join mysales.areas b on a.cityid = b.areaid
where concat('2_',a.cityid)=$parentnodeid;
end $$
delimiter ;

call s3();

2)采用分层逐级展开树的方法构造一个树,给定一个年份与月份,树的第一层节点为年份与月份信息(如“2018年6月份所有订单”),第二层为这个月份的含有订单的所有日期(如6月01日、6月30日),第三层为每一天的订单信息(包括订单编号、客户编码和客户名称)。没有订单的日期不需要出现。(期末试题用逐级展开的方法再做一下)

drop procedure if exists p1;
delimiter $$
create procedure p1($y int,$m int)
begin
select concat($y,'年',$m,'月份所有订单') as text,concat('1_',$m) as id,1 as level,1 as isparentflag,'' as parentnodeid,'' as ancester;
end $$
delimiter ;

call p1(2018,6);

drop procedure if exists p2;
delimiter $$
create procedure p2($y int,$m int,$parentnodeid varchar(20))
begin
select distinct concat($m,'月',day(orderdate),'日') as text,concat('2_',orderdate) as id,2 as level,1 as isparentflag,concat('1_',$m) as parentnodeid,concat('1_',$m,'#') as ancester from mysales.orders
where concat('1_',$m) = $parentnodeid and month(orderdate) = $m and year(orderdate) = $y;
end $$
delimiter ;

call p2(2018,6,'1_6');

drop procedure if exists p3;
delimiter $$
create procedure p3($y int,$m int,$parentnodeid varchar(20))
begin
select concat(orderid,' ',customerid,' ',companyname) as text,concat('3_',customerid) as id,3 as level,0 as isparentflag,concat('2_',orderdate) as parentnodeid,concat('1_',$m,'#2_',orderdate,'#') as ancester from mysales.orders a join mysales.customers b using(customerid)
where concat('2_',orderdate) = $parentnodeid and month(orderdate) = $m and year(orderdate) = $y;
end $$
delimiter ;

call p3(2018,6,'2_2018-06-02');

drop procedure if exists v1;
delimiter $$
create procedure v1($s varchar(255))
begin
declare $k varchar(255);
declare $len int;
set @str = 'select *, categoryid as id, categoryname as text from x_categories where ';
set $len = 1;
set $k = substring_index(trim(substring($s,$len)),' ',1);

while($k!='')do
set @str = concat(@str,if($len!=1,' or ',''),'categoryid like \'%',$k,'%' or ','categoryname like '%',$k,'%\' or ','englishname like \'%',$k,'%' or ','description like '%',$k,'%'');
set $len = char_length($k)+1;
set $s = trim(substring($s,$len));
set $k = substring_index($s,' ',1);
end while;
set @str = concat(@str,'order by concat(trim(ancester),id)');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end $$
delimiter ;

call v1('系统 电子');

select substring('你好 伟大的人儿',4);

select char_length('你好');

select char_length(substring_index(trim(substring('你好 伟大的人儿',1)),' ',1));

select substring_index(trim('你好 伟大的人儿'),' ',1);

drop procedure if exists v2;
delimiter $$
create procedure v2()
begin
select *,left(sys_getFirstPyCode(name),1) as py from x_students where name!='' and name not regexp '[0-9]' limit 100;
end $$
delimiter ;

call v2();

drop procedure if exists d2001_2;
delimiter $$
create procedure d2001_2( #分层获取部门节点

$parentnodeid varchar(100)

)
begin

select *, deptid as id, concat(deptid,' ',deptname) as text from x_departments where parentnodeid=$parentnodeid order by concat(trim(ancester),deptid);

end $$
delimiter ;

your code

use kms2020;
SET SQL_SAFE_UPDATES = 0; -- 非安全模式

三、构造实例存储过程

drop procedure if exists d0303_1;
delimiter $$
create procedure d0303_1()
begin

select * from mysales.customers;    

end $$
delimiter ;

drop procedure if exists d0303_2;
delimiter $$
create procedure d0303_2()
begin

select *,concat('<a href="javascript:fnShow(\'',customerid,'\')">', customerid, '&nbsp;', companyname,'</a>') as hlink 
from mysales.customers;    

end $$
delimiter ;

call d0303_2();

drop procedure if exists d0304_1;
delimiter $$
create procedure d0304_1(

$year int,  #select last_day('2019-8-11')
$month int

)
begin

with tmp as  (
    select productid,
    sum(if(year(orderdate)=$year and month(orderdate)=$month, quantity, 0)) as qty1,
    sum(if(orderdate<=last_day(concat($year,'-',$month,'-01')), quantity, 0)) as qty2
    from mysales.orderitems a
    join mysales.orders b using(orderid)
    group by productid
)
select a.productid,productname,quantityperunit,c.companyname as suppliername,c.supplierid,region,city,
b.unitprice,qty1,qty2 from tmp as a 
join mysales.products b  using(productid) 
join mysales.suppliers c using(supplierid);

end $$
delimiter ;

call d0304_1(2019,1);

drop procedure if exists d0304_2;
delimiter $$
create procedure d0304_2(

$supplierid varchar(20)

)
begin

select a.*,b.companyname as suppliername,b.region,b.city from mysales.products a
join mysales.suppliers b using(supplierid)
where a.supplierid=$supplierid;

end $$
delimiter ;

drop procedure if exists d0304_3; #取商品订单信息
delimiter $$
create procedure d0304_3(

$productid varchar(20),
$year int,
$month int

)
begin

select a.*,b.orderdate,b.customerid,c.companyname,b.employeeid,d.employeename from mysales.orderitems a
join mysales.orders b using(orderid)
join mysales.customers c using(customerid)
join mysales.employees d using(employeeid)
where a.productid=$productid and year(orderdate)=$year and month(orderdate)=$month;

end $$
delimiter ;

call d0304_3(1,2019,8);

drop procedure if exists d0305_1; #取地区
delimiter $$
create procedure d0305_1(

$parentnodeid varchar(20)

)
begin

select *,  areaid as id, concat(areaid, ' ', areaname) as text from city where parentnodeid=$parentnodeid;

end $$
delimiter ;

drop procedure if exists d0306_1;
delimiter $$
create procedure d0306_1(

$pageno int,
$pagesize int,
$filtertext varchar(255)

)
begin

set @sql="select a.*, b.orderdate, c.productname, c.quantityperunit, b.customerid, b.employeeid, c.unit, d.companyname,e.employeename from mysales.orderitems a 
join mysales.orders b using(orderid)
join mysales.products c using(productid)
join mysales.customers d using(customerid)
join mysales.employees e using(employeeid)";
call sys_gridPaging(@sql, $pageno, $pagesize, 'productname;companyname', $filtertext);

end $$
delimiter ;

call d0306_1(1,100,'');

drop procedure if exists d0306_11;
delimiter $$
create procedure d0306_11(

$pageno int,
$pagesize int

)
begin
declare $start int;
declare $lastno int;
select count(*) into @total from mysales.orderitems a

join mysales.orders b using(orderid)
join mysales.products c using(productid)
join mysales.customers d using(customerid)
join mysales.employees e using(employeeid);

if($pagesize<=0)then
set $pageno = 1;
set $pagesize = @total;
end if;

set $lastno = (@total-1)/$pagesize+1;
if($pageno>$lastno)then set $pageno = 1;
end if;

set $start = ($pageno-1)*$pagesize;
select a.*, b.orderdate, c.productname, c.quantityperunit, b.customerid, b.employeeid, c.unit, d.companyname,e.employeename,@total as total from mysales.orderitems a

join mysales.orders b using(orderid)
join mysales.products c using(productid)
join mysales.customers d using(customerid) 
join mysales.employees e using(employeeid) limit $start,$pagesize;

end $$
delimiter ;

select * from x_resources;

drop procedure if exists d0307_1;
delimiter $$
create procedure d0307_1()
begin

select a.*,b.categoryname,c.companyname,c.region,c.city from mysales.products a
join mysales.categories b using(categoryid)
join mysales.suppliers c using(supplierid)
order by a.productid;

end $$
delimiter ;

call d0307_1();

drop procedure if exists d0307_2;
delimiter $$
create procedure d0307_2()
begin

select * from mysales.suppliers;

end $$
delimiter ;

drop procedure if exists d0307_3;
delimiter $$
create procedure d0307_3()
begin

select * from mysales.categories;

end $$
delimiter ;

drop procedure if exists d0307_4;
delimiter $$
create procedure d0307_4(

$addoredit varchar(20),
$productid int,
$productname varchar(255),
$quantityperunit varchar(255),
$unit varchar(255),
$unitprice decimal(12,2),
$categoryid varchar(20),
$supplierid  varchar(25)

)
begin

update mysales.products set 
productname=$productname,
quantityperunit=$quantityperunit,
unit=$unit,
unitprice=$unitprice,
categoryid=$categoryid,
supplierid=$supplierid
where productid=$productid;

end $$
delimiter ;

drop procedure if exists d0307_5;
delimiter $$
create procedure d0307_5(

$productid int

)
begin

delete from mysales.products where productid=$productid;

end $$
delimiter ;

drop procedure if exists d0307_6;
delimiter $$
create procedure d0307_6(

$parentnodeid varchar(20)

)
begin

select *, areaid as regionid, areaid as cityid,areaname as province,areaname as city from x_areas where parentnodeid=$parentnodeid;

end $$
delimiter ;

drop procedure if exists d0307_7;
delimiter $$
create procedure d0307_7()
begin

select * from mysales.customers;

end $$
delimiter ;

drop procedure if exists d0404_1;
delimiter $$
create procedure d0404_1(

$str varchar(500)

)
begin

select sys_getPyCode($str) as pycode, sys_getFirstPyCode($str) as memocode;

end $$
delimiter ;

drop table if exists songs;
create table songs(

rowid int auto_increment primary key,
title varchar(100),
singer varchar(50),
author varchar(50),
pubdate date,
filepath json,
notes mediumtext

);
insert into songs (title,singer,author,filepath) values
('玛尼情歌','上官红燕','薛鹏','[{"fileosname":"mybase/mp3/玛尼情歌.mp3"}]'),
('漫漫漫长夜','王恩华','彭先学','[{"fileosname":"mybase/mp3/漫漫漫长夜.mp3"}]'),
('不过人间','海来阿木','海来阿木','[{"fileosname":"mybase/mp3/不过人间.mp3"}]'),
('白狐','小阿枫','孙红莺/李旭辉','[{"fileosname":"mybase/mp3/白狐.mp3"}]'),
('浪子闲话','花僮','周仁/花僮','[{"fileosname":"mybase/mp3/浪子闲话.mp3"}]'),
('爱江山更爱美人','李丽芬','小虫','[{"fileosname":"mybase/mp3/爱江山更爱美人.mp3"}]'),
('深情败给时间-男','张小磊','张守梅','[{"fileosname":"mybase/mp3/深情败给时间_男.mp3"}]'),
('深情败给时间-女','乔玲儿','张守梅','[{"fileosname":"mybase/mp3/深情败给时间_女.mp3"}]'),
('想你的时候只能问月亮','抖音版','党明毅','[{"fileosname":"mybase/mp3/想你的时候只能问月亮.mp3"}]'),
('你的样子','齐秦','罗大佑','[{"fileosname":"mybase/mp3/你的样子_齐秦.mp3"}]'),
('可可托海的牧羊人','亚男','王琪','[{"fileosname":"mybase/mp3/可可托海的牧羊人_亚男.mp3"}]'),
('白毛女','中国交响乐队','宋晓红','[{"fileosname":"mybase/mp3/白毛女.mp3"}]');
update songs set notes='太阳的雅鲁藏布啊
月亮的日喀则
思念的唐古拉山吼
爱情的狮泉河
神圣的布达拉宫
那多情的拉木措
永恒的珠穆郎玛峰上采雪莲一朵
天上的雄鹰高飞
地上的骏马驰过
亲热的酥油茶香飘过高原的部落
玛尼石的传说有着你给的承诺
让我在轮回中愿为了你漂泊
玛尼玛尼玛尼唱着前世的情歌
锅庄摇曳篝火
哦你是我的佛
玛尼玛尼玛尼守着格桑不寂寞
三生缘定因果
我等你来娶我
太阳的雅鲁藏布啊
月亮的日喀则
思念的唐古拉山吼
爱情的狮泉河
神圣的布达拉宫
那多情的拉木措
永恒的珠穆郎玛峰上采雪莲一朵
天上的雄鹰高飞
地上的骏马驰过
亲热的酥油茶香飘过高原的部落
玛尼石的传说有着你给的承诺
让我在轮回中愿为了你漂泊
玛尼玛尼玛尼唱着前世的情歌
锅庄摇曳篝火
哦你是我的佛
玛尼玛尼玛尼守着格桑不寂寞
三生缘定因果
我等你来娶我
玛尼玛尼玛尼唱着前世的情歌
锅庄摇曳篝火
哦你是我的佛
玛尼玛尼玛尼守着格桑不寂寞
三生缘定因果
我等你来娶我
三生缘定因果
我等你 来娶我' where rowid=1;

drop procedure if exists d0405_1;
delimiter $$
create procedure d0405_1()
begin

select *, rowid as rowno, concat(title,'(', singer,')') as titlex from songs;

end $$
delimiter ;

call d0405_1();

drop procedure if exists d0407_1;
delimiter $$
create procedure d0407_1(

$rowid int

)
begin

select count(*) as rowno from songs where rowID<$rowid;

end $$
delimiter ;

select * from x_resources;

drop procedure if exists d0408_1;
delimiter $$
create procedure d0408_1()
begin

select *, rowid as id, concat(title,'(', singer,')') as text, '' as parentnodeid, 1 as level, 
0 as isparentflag, '' as ancester from songs;

end $$
delimiter ;

drop procedure if exists d0406_1;
delimiter $$
create procedure d0406_1()
begin

select * from x_resources where rowid=1;

end $$
delimiter ;

drop procedure if exists d0406_2;
delimiter $$
create procedure d0406_2()
begin

select * from x_resources;

end $$
delimiter ;

select * from x_departments;

drop procedure if exists d0701_1;
delimiter $$
create procedure d0701_1(

$studentid varchar(20)

)
begin

if ($studentid='') then select a.*, b.deptname from x_students a left join x_departments b using(deptid) order by studentid; #提取全部学生
else #提取一个学生及其排名序号
    
    #with tmp as ( 
    #    select *, row_number() over(order by studentid ) as _rowno from x_students 
    #)
    #select _rowno-1 into @n from tmp where studentid=$studentid;
    select count(*) into @n from x_students where studentid<$studentid;
    #select count(*) into @n from x_students where studentid<$studentid and deptid<=$deptid;
    select a.*, b.deptname, @n as _rowindex from x_students a left join x_departments b using(deptid) 
    where a.studentid=$studentid;
end if;

end $$
delimiter ;

call d0701_1('2014333501021');

call d0701_1('1000000000');
drop procedure if exists d0701_2;
delimiter $$
create procedure d0701_2()
begin

select *, deptid as id, concat(deptid,' ', deptname) as text from x_departments order by concat(trim(ancester), id);

end $$
delimiter ;

drop procedure if exists d0702_1;
delimiter $$
create procedure d0702_1(

$pageno int,
$pagesize int

)
begin

declare $start int;
select count(*) into @n from x_students a left join x_departments b using(deptid) ;
if ($pagesize=0) then
    set $pageno=1;
    set $pagesize=@n;
end if;
set $start=($pageno-1)*$pagesize;
select a.*, b.deptname, @n as total from x_students a left join x_departments b using(deptid) order by studentid limit $start, $pagesize;

end $$
delimiter ;

call d0702_1(1,20);

drop procedure if exists d0703_1;
delimiter $$
create procedure d0703_1(

$pageno int,
$pagesize int,
$filtertext varchar(500)

)
begin

declare $start int;
declare $s varchar(500);
set $s=concat('%', $filtertext, '%');
select count(*) into @n from x_students a left join x_departments b using(deptid) 
where studentid like $s or name like $s or pycode like $s or gender like $s or address like $s or mobile like $s 
or email like $s or weixin like $s or province like $s or city like $s;
if ($pagesize=0) then
    set $pageno=1;
    set $pagesize=@n;
end if;
set $start=($pageno-1)*$pagesize;
select a.*, b.deptname, @n as total from x_students a left join x_departments b using(deptid) 
where studentid like $s or name like $s or pycode like $s or gender like $s or address like $s or mobile like $s 
or email like $s or weixin like $s or province like $s or city like $s
order by studentid limit $start, $pagesize;

end $$
delimiter ;

call d0703_1(1,20, '国');

drop procedure if exists d0801_1;
delimiter $$
create procedure d0801_1()
begin

select categoryid, categoryname, parentnodeid, level, isparentflag, ancester,
categoryid as id, concat(categoryid,' ',categoryname) as text from mysales.categorytree;

end $$
delimiter ;

select* from mysales.products;

drop procedure if exists d0801_2;
delimiter $$
create procedure d0801_2()
begin

select categoryid, categoryname, parentnodeid, level, 1 as isparentflag, ancester,
categoryid as id, concat(categoryid,' ',categoryname) as text from mysales.categorytree
union all 
select a.productid, a.productname, x.categoryid as parentnodeid, x.level+1, 0, concat(x.ancester, x.categoryid,'#'),
productid as id, productname as text
from mysales.products as a join mysales.categorytree as x on a.subcategoryid=x.categoryid
order by concat(trim(ancester), id);

end $$
delimiter ;

创建一个存储过程,输入一个日期值,按树型结构输出查询结果,具体要求如下:

树的第一层节点为年份与月份信息(如“2019年6月份所有订单”),

第二层为这个月份的所有订单及客户信息(包括订单编号、客户编码和客户名称),

第三层为每个订单的商品销售明细信息(商品编码、名称、规格型号、销售量和销售额)。在页面上放一个树控件,把这些节点一次性的加载到树节点中去。

drop procedure if exists d0801_3;
delimiter $$
create procedure d0801_3(

$date date

)
begin

select concat('1_', month($date)) as id, concat(year($date),'年', month($date),'月份') as text, 
'' as parentnodeid, 1 as level, 1 as isparentflag, '' as ancester     
union all 
select concat('2_', orderid) as id, concat(orderid,' ',customerid,' ', companyname) as text,
concat('1_', month($date)) as parentnodeid, 2 as level, 1 as isparentflag, concat('', '1_', month($date),'#') as ancester
from mysales.orders a join mysales.customers b using(customerid)
where year(orderdate)=year($date) and month(orderdate)=month($date)
union all 
select concat('3_', orderid, '#', productid) as id, 
concat(productid,' ',productname,' ',quantityperunit, ' ', quantity, ' ',amount)  as text,
concat('2_', orderid) as parentnodeid, 3 as level, 0 as isparentflag, 
concat('', '1_', month($date), '#','2_', orderid, '#') as ancester from mysales.orderitems as a
join mysales.orders using(orderid)
join mysales.products c using(productid) where year(orderdate)=year($date) and month(orderdate)=month($date)
order by concat(trim(ancester), id);

end $$
delimiter ;

call d0801_3('2019-6-10');

drop procedure if exists d0804_1;
delimiter $$
create procedure d0804_1(

$parentnodeid varchar(100)

)
begin

select *, areaid as id,areaname as text from x_areas where parentnodeid=$parentnodeid;
#select areaid as id,areaname as text,parentnodeid,level,ancester, isparentflag from x_areas where parentnodeid='';    

end $$
delimiter ;

drop procedure if exists d0805_1;
delimiter $$
create procedure d0805_1(

$date date

)
begin

select concat('1_', month($date)) as id, concat(year($date),'年', month($date),'月份') as text, 
'' as parentnodeid, 1 as level, 1 as isparentflag, '' as ancester; 

end $$
delimiter ;

drop procedure if exists d0805_2;
delimiter $$
create procedure d0805_2(

$date date,
$parentnodeid varchar(100)

)
begin

select concat('2_', orderid) as id, concat(orderid,' ',customerid,' ', companyname) as text,
concat('1_', month($date)) as parentnodeid, 2 as level, 1 as isparentflag, concat('', '1_', month($date),'#') as ancester
from mysales.orders a join mysales.customers b using(customerid)
where year(orderdate)=year($date) and month(orderdate)=month($date)
and concat('1_', month($date))=$parentnodeid;

end $$
delimiter ;

drop procedure if exists d0805_3;
delimiter $$
create procedure d0805_3(

$date date,
$parentnodeid varchar(100)

)
begin

select concat('3_', orderid, '#', productid) as id, 
concat(productid,' ',productname,' ',quantityperunit, ' ', quantity, ' ',amount)  as text,
concat('2_', orderid) as parentnodeid, 3 as level, 0 as isparentflag, 
concat('', '1_', month($date), '2_', orderid, '#') as ancester from mysales.orderitems as a
join mysales.orders using(orderid)
join mysales.products c using(productid) where year(orderdate)=year($date) and month(orderdate)=month($date)
and concat('2_', orderid)=$parentnodeid;

end $$
delimiter ;

四、知识管理系统存储过程

drop procedure if exists d1001_1;
delimiter $$
create procedure d1001_1(

$userid varchar(100),
$password varchar(100) 

)
begin

declare $s mediumtext;
set $s=sys_toycode($password);
select $s;
select teacherID as userid,name as username,account,usertype,mobile,email,weixin,photopath,signature,rememberflag, 1 as flag from x_teachers where (teacherid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid) and password=$s
union all
select teacherID as userid,name,account,usertype,mobile,email,weixin,photopath,signature,rememberflag, 2 as flag from x_teachers where (teacherid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid)
union all
select studentID as userid,name,account,usertype,mobile,email,weixin,photopath, '' as signature,rememberflag, 1 as flag from x_students where (studentid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid) and password=$s
union all
select studentID as userid,name,account,usertype,mobile,email,weixin,photopath, '' as signature,rememberflag, 2 as flag from x_students where (studentid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid);

end $$
delimiter ;

call d1001_1('20000554', '123456');

select* from x_students;

drop procedure if exists d1001_2;
delimiter $$
create procedure d1001_2( #登录框验证用户和密码是否正确

$userid varchar(100),
$password varchar(100) 

)
begin

declare $s mediumtext;
declare $ss1, $ss2, $ss3, $ss4, $ss5, $ss6, $ss7 varchar(500);
declare $flag int;
select teacherID,name,account,usertype,mobile,email,weixin, password into $ss1,$ss2,$ss3,$ss4,$ss5,$ss6,$ss7,$s
from x_teachers where teacherid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid;
if ($ss1 is null) then
    select studentid,name,account,usertype,mobile,email,weixin, password into $ss1,$ss2,$ss3,$ss4,$ss5,$ss6,$ss7,$s
    from x_students where studentid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid;
end if;
if ($ss1 is null) then set $flag=-1;
elseif ($s=sys_toycode($password)) then set $flag=1;
else set $flag=0;
end if;
select $flag as flag, $ss1 as userid, $ss2 as username, $ss3 as account, $ss4 as usertype, $ss5 as mobile, $ss6 as email, $ss7 as weixin;

end $$
delimiter ;
call d1001_2('20000555', '123456');

drop procedure if exists d1002_1;
delimiter $$
create procedure d1002_1(

$userid varchar(100),
$usertype varchar(10) 

)
begin

select *, menuid as id, menutitle as text from sys_menu;

end $$
delimiter ;

drop procedure if exists d1003_1;
delimiter $$
create procedure d1003_1(

$userid varchar(200),
$mobile varchar(200),
$email varchar(200),
$weixin varchar(200)    

)
begin

select 1 as flag from x_teachers where teacherid=$userid
union all
select 2 as flag from x_teachers where account=$userid
union all
select 3 as flag from x_teachers where mobile=$mobile and $mobile<>''
union all
select 4 as flag from x_teachers where email=$email and $email<>''
union all
select 5 as flag from x_teachers where weixin=$weixin and $weixin<>''
union all
select 1 as flag from x_students where studentid=$userid
union all
select 2 as flag from x_students where account=$userid
union all
select 3 as flag from x_students where mobile=$mobile and $mobile<>''
union all
select 4 as flag from x_students where email=$email and $email<>''
union all
select 5 as flag from x_students where weixin=$weixin and $weixin<>'';    

end $$
delimiter ;

drop procedure if exists d1003_2;
delimiter $$
create procedure d1003_2( ##保存注册用户

$data mediumtext,
$usertype varchar(100),
$userid  varchar(100)

)
begin

SET SQL_SAFE_UPDATES = 0;  -- 非安全模式
if ($usertype='1') then
    call sys_runEditRows('', 'x_students', 'studentid', '', $data);
    update x_students set password=sys_toycode(password) where studentid=$userid;
else
    call sys_runEditRows('kms2020', 'x_teachers', 'teacherid', '', $data);
    update x_teachers set password=sys_toycode(password) where teacherid=$userid;
end if;

end $$
delimiter ;

drop procedure if exists d1004_1;
delimiter $$
create procedure d1004_1( ##验证密码是否正确

$userid  varchar(100),
$usertype varchar(100),
$password varchar(100)

)
begin

declare $s mediumtext;
if ($password<>'') then
    set $s=sys_toycode($password);  #密码加密
    if ($usertype='1') then
        select * from x_students where (studentid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid) and password=$s;
    else
        select * from x_teachers where (teacherid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid) and password=$s;
    end if;
else
    if ($usertype='1') then
        select * from x_students where (studentid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid);
    else
        select * from x_teachers where (teacherid=$userid or account=$userid or mobile=$userid or email=$userid or weixin=$userid);
    end if;
end if;    

end $$
delimiter ;

call d1004_1('20000555','1','');

drop procedure if exists d1005_1;
delimiter $$
create procedure d1005_1( ##判断注册账号是否重复

$userid  varchar(100),
$usertype varchar(100)

)
begin

select studentid as userid,account from x_students where studentid=$userid
union all
select teacherid as userid,account from x_teachers where teacherid=$userid;

end $$
delimiter ;

drop procedure if exists d2001_1;
delimiter $$
create procedure d2001_1() #一次性获取部门节点
begin

select *, deptid as id, concat(deptid,' ',deptname) as text from x_departments order by concat(trim(ancester),deptid);

end $$
delimiter ;

drop procedure if exists d2001_2;
delimiter $$
create procedure d2001_2( #分层获取部门节点

$parentnodeid varchar(100)

)
begin

select *, deptid as id, concat(deptid,' ',deptname) as text from x_departments where parentnodeid=$parentnodeid order by concat(trim(ancester),deptid);

end $$
delimiter ;

删除树形结构中的一个节点及其子孙结点,返回兄弟节点与父节点

drop procedure if exists d2001_3;
delimiter $$
create procedure d2001_3(

$deptid varchar(20)

)
begin

declare $s1, $s2 varchar(20);
declare $bnode1, $bnode2 varchar(20);
SET SQL_SAFE_UPDATES = 0;
/* 提取这个节点的父节点和祖先节点,存放到两个变量中。*/
select trim(parentnodeid), trim(ancester) into $s1, $s2 from x_departments where deptid=$deptid;
/* 根据ancester和like子句,删除这个节点的所有子孙节点。语法上需要使用cte。*/
with cte as ( #
    select deptid from x_departments where ancester like concat($s2, $deptid, '#%')
)
delete from x_departments where deptid in (select deptid from cte);
/* 判断父节点中有没有值为$s1的地区节点,即判断有没有节点其父节点与当前节点的父节点时相同的,或者说查找有没有兄弟节点。*/
if not exists(select 1 from x_departments where parentnodeid=$s1 and deptid<>$deptid) then 
    #找不到兄弟节点,将原来的父节点改为叶子结点
    update x_departments set isparentflag=0 where deptid=$s1;
else 
    #存在兄弟节点。先提取当前节点的下一个兄弟节点
    select deptid into $bnode1 from x_departments where parentnodeid=$s1 and deptid>$deptid order by deptid limit 1;
    #再提取当前节点的上一个兄弟节点
    select deptid into $bnode2 from x_departments where parentnodeid=$s1 and deptid<$deptid order by deptid desc limit 1;
end if;
/* 删除这个节点的本身。*/
delete from x_departments where deptid=$deptid;
#输出当前节点的两个兄弟节点和父节点
select $bnode1 as nextnode, $bnode2 as priornode, $s1 as parentnode;

end $$
delimiter ;

call d2001_3('A3');

drop procedure if exists d2004_1;
delimiter $$
create procedure d2004_1() #分层获取资源分类节点
begin

select *, categoryid as id, categoryname as text from x_categories order by concat(trim(ancester),id);

end $$
delimiter ;

drop procedure if exists d2004_2;
delimiter $$
create procedure d2004_2( #保存数据

$data mediumtext

)
begin

truncate table x_categories;
call sys_runEditRows('kms2020','x_categories', 'categoryid', '', $data);

end $$
delimiter ;

drop procedure if exists d2005_1;
delimiter $$
create procedure d2005_1( #分层获取资源分类节点

$parentnodeid varchar(100)

)
begin

select *, categoryid as id, categoryname as text from x_categories where parentnodeid=$parentnodeid order by concat(trim(ancester),id);

end $$
delimiter ;

select 'ok, end!' as end;

标签: none

已有 5 条评论

  1. 加油

  2. :3

  3. 祝

    求劝学源音频谢谢!!!!(歇斯底里)

添加新评论