You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
425 lines
15 KiB
JavaScript
425 lines
15 KiB
JavaScript
|
|
|
|
ej.base.registerLicense('ORg4AjUWIQA/Gnt2XFhhQlJHfV5AQmBIYVp/TGpJfl96cVxMZVVBJAtUQF1hTH5VdkxhWnpWcHZcTmhfWkZ/')
|
|
var studio = {_context:''}
|
|
studio.defaults = {
|
|
'duckdb':'SELECT * \nFROM INFORMATION_SCHEMA.TABLES',
|
|
'sqlite':'SELECT * \nFROM sqlite_master',
|
|
'sqlite3':'SELECT * \nFROM sqlite_master',
|
|
'bigquery':'SELECT * \nFROM <dataset>.INFORMATION_SCHEMA.COLUMNS \nLIMIT 10',
|
|
'postgresql':'SELECT *\nFROM INFORMATION_SCHEMA.COLUMNS LIMIT 10',
|
|
'iceberg':'SHOW TABLES',
|
|
'mysql':'SELECT * \nFROM INFORMATION_SCHEMA.COLUMNS LIMIT 10',
|
|
'mariadb':'SHOW TABLES'
|
|
}
|
|
studio.init = function (_context){
|
|
studio._context = _context
|
|
}
|
|
studio.dbe = {}
|
|
studio.dbe.get = function (){
|
|
var uri = [studio._context,'api/dbe/get']
|
|
uri = uri.join('/')
|
|
var http = HttpClient.instance()
|
|
http.get(uri,function(x){
|
|
|
|
})
|
|
|
|
|
|
}
|
|
|
|
studio.dbe.providers = function (_render){
|
|
var uri = [studio._context,'api/dbe/providers']
|
|
uri = uri.join('/')
|
|
var http = HttpClient.instance()
|
|
http.get(uri,function(x){
|
|
var _data = JSON.parse(x.responseText)
|
|
if (_render){
|
|
_render(_data)
|
|
}
|
|
|
|
})
|
|
}
|
|
|
|
|
|
studio.dbe.apply = function (label,_query){
|
|
var uri = [studio._context,'api/dbe/apply']
|
|
uri = uri.join('/')
|
|
|
|
var http = HttpClient.instance()
|
|
_data = {'label':label,'query':_query}
|
|
http.setHeader('Content-Type','application/json')
|
|
http.setData (JSON.stringify(_data))
|
|
http.get(uri,function(x){
|
|
if(x.status == 200 && x.readyState == 4){
|
|
_r = JSON.stringify(x.responseText)
|
|
}else{
|
|
alert(' error round '+x.responseText)
|
|
}
|
|
})
|
|
|
|
}
|
|
studio.grid = function (){
|
|
this.columns = function (_data){
|
|
var _columns = []
|
|
if(_data.columns){
|
|
_data.columns.forEach(_name=>{
|
|
_columns.push({title:_name})
|
|
})
|
|
}
|
|
return _columns
|
|
}
|
|
this.dataTable = function (_id,_data){
|
|
$(_id).html('<table class="display"></table>')
|
|
$(_id.replace(/output/,'status')).hide()
|
|
|
|
_id = _id + ' table'
|
|
var _columns = []
|
|
if (_data.columns ){
|
|
_data.columns.forEach(_name=>{
|
|
_columns.push({title:_name})
|
|
})
|
|
var _args = {'data':_data.data,'columns':_columns}
|
|
// _args.layout = {topStart:{}}
|
|
_args.buttons = ['excel','pdf']
|
|
|
|
}else{
|
|
_data = {'data':[],columns:[]}
|
|
}
|
|
_args.dom = 'rtip'
|
|
_args.scrollCollapse = true
|
|
_args.scrollY = '400px'
|
|
var _grid = new DataTable(_id,_args)
|
|
return _grid;
|
|
}
|
|
this.gridjs = function (_id, _data){
|
|
$(_id.replace(/output/,'status')).hide()
|
|
var grid = new gridjs.Grid({
|
|
// columns:_data.columns,
|
|
data:_data.data , //_data.data,
|
|
columns:_data.columns,
|
|
fixedHeader:true, resizable:true,
|
|
search:false,
|
|
|
|
style:{
|
|
th:{height:'20px',overflow:'hidden','text-overflow':'ellipsis'},
|
|
td :{height:'20px',padding:'4px',overflow:'hidden','text-overflow':'ellipsis'},
|
|
table:{overflow:'hidden','text-overflow':'ellipsis','white-space':'nowrap',border:'1px solid transparent'}
|
|
},
|
|
sort:true,
|
|
pagination:true,
|
|
height:'405px'})
|
|
if ($(_id).is(':empty')){
|
|
grid.render($(_id)[0])
|
|
}
|
|
grid.forceRender($(_id)[0])
|
|
}
|
|
this.spreadsheet = function (_id, _data){
|
|
var _div = $(_id)[0]
|
|
var _columns = this.columns(_data)
|
|
var rows = []
|
|
_data.data.forEach(row=>{
|
|
var _record = {} //-- formatted appropriately
|
|
row.forEach((value,index)=>{
|
|
_col = _data.columns[index].name
|
|
_record[_col] = value
|
|
})
|
|
rows.push(_record)
|
|
})
|
|
|
|
var _spreadsheet = jspreadsheet(_div, {
|
|
tabs:true,toolbar:false,
|
|
style:['text-overflow: ellipsis; white-space: nowrap;'],
|
|
worksheets: [{
|
|
media:[{type:'chart',options:{}}],
|
|
data: _data.data,
|
|
minDimensions:[20,15],
|
|
tableOverflow:true,
|
|
tableHeight: '400px',
|
|
tableWidth:$(_id).width(),//minSpareCols:'auto',
|
|
columns:_columns,
|
|
workbookname: _id
|
|
}]
|
|
});
|
|
_div.spreadsheet = _spreadsheet
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
this.syncfusion = function(_id,_data){
|
|
|
|
var rows = []
|
|
|
|
_data.data.forEach(row=>{
|
|
var _record = {} //-- formatted appropriately
|
|
row.forEach((value,index)=>{
|
|
_col = _data.columns[index]
|
|
_record[_col] = value
|
|
})
|
|
rows.push(_record)
|
|
})
|
|
|
|
var spreadsheet = new ej.spreadsheet.Spreadsheet();
|
|
|
|
// spreadsheet.openUrl='/api/io/open'
|
|
// spreadsheet.openSettings = {chunkSize:1024}
|
|
// spreadsheet.saveUrl = 'https://services.syncfusion.com/js/production/api/spreadsheet/save'
|
|
// spreadsheet.created = function (){}
|
|
spreadsheet.sheets = [
|
|
{name:_id.replace(/[#,.]/g,' '),
|
|
ranges:[{dataSource:rows}],
|
|
|
|
}
|
|
]
|
|
|
|
|
|
spreadsheet.appendTo(_id)
|
|
$(_id)[0].spreadsheet = spreadsheet
|
|
// console.log([' **** ',(_id+' .e-input-group')])
|
|
// $(_id+' .e-input-group').remove()
|
|
spreadsheet.refresh()
|
|
spreadsheet.hideFileMenuItems(["File"], true);
|
|
}
|
|
|
|
}
|
|
studio.frame = function (_args){
|
|
this._args = _args
|
|
//
|
|
// Initializing by having an input and ouput pane
|
|
// Load the template from disk
|
|
this.init = function (){
|
|
|
|
}
|
|
|
|
//
|
|
// Render the template
|
|
this.read = function(_label,_query,_compute){
|
|
var uri = [studio._context,'api/dbe/apply']
|
|
uri = uri.join('/')
|
|
// var _compute = this.compute
|
|
// $('.'+_label+' .output').html('<i class="fa-solid fa-cog fa-spin"></i> Please wait ...')
|
|
var http = HttpClient.instance()
|
|
|
|
_data = {'label':_label,'query':_query}
|
|
http.setHeader('Content-Type','application/json')
|
|
http.setData (JSON.stringify(_data))
|
|
|
|
http.post(uri,function(x){
|
|
if(x.status == 200 && x.readyState == 4){
|
|
_r = JSON.parse(x.responseText)
|
|
sessionStorage._data = x.responseText
|
|
// console.log(_r)
|
|
_compute(_label,_r)
|
|
|
|
}else{
|
|
alert(' error round '+x.responseText)
|
|
}
|
|
})
|
|
|
|
}
|
|
this.open = function (_id,file){
|
|
var http = HttpClient.instance()
|
|
_uri = 'api/io/open'
|
|
var form = new FormData()
|
|
form.append('file',file)
|
|
http.setData(form)
|
|
var _index = $(_id)[0].spreadsheet.sheets.length
|
|
// _name = file.name
|
|
|
|
// $(_id)[0].spreadsheet.insertSheet([{name:_name,_index:1}],0)
|
|
// console.log([_index, $(_id)[0].spreadsheet.sheets.length])
|
|
// $(_id)[0].spreadsheet.sheets[_index].name = `sheet {_index}`
|
|
http.post(_uri,function(x){
|
|
if(x.status == 200 && x.readyState == 4){
|
|
var _data = JSON.parse(x.responseText)
|
|
_data.Workbook.sheets.forEach(sheet=>{
|
|
sheet.name = file.name
|
|
sheet.index=_index
|
|
$(_id)[0].spreadsheet.insertSheet([sheet],0)
|
|
})
|
|
|
|
|
|
}else{
|
|
//@TODO:
|
|
// handle the error in a graceful way
|
|
}
|
|
})
|
|
}
|
|
this.export = function(_label,spreadsheet){
|
|
var uri = 'api/io/write'
|
|
var http = HttpClient.instance()
|
|
var _data = {}
|
|
_rows = {}
|
|
spreadsheet.sheets.forEach((sheet)=>{
|
|
_rows[sheet.name] = {columns:[],values:[]}
|
|
|
|
sheet.rows.forEach((_item,_index)=>{
|
|
// cells = _item.cells
|
|
if(_index == 0){
|
|
_item.cells.forEach(_x =>{
|
|
_rows[sheet.name].columns.push(_x.value)
|
|
})
|
|
}else{
|
|
rec = []
|
|
_item.cells.forEach(_x=>{
|
|
rec.push(_x.value)
|
|
})
|
|
_rows[sheet.name].values.push(rec)
|
|
}
|
|
})
|
|
console.log(_rows[sheet.name].values)
|
|
// if (sheet.ranges.length > 0){
|
|
// if(sheet.ranges[0].dataSource.length > 0){
|
|
|
|
// _data.push(sheet) //.ranges[0].dataSource)
|
|
// }
|
|
// }
|
|
})
|
|
if (_rows ){
|
|
http.setHeader('Content-Type','application/json')
|
|
http.setData (JSON.stringify({rows:_rows,'label':_label}))
|
|
http.post(uri,(x)=>{
|
|
if(x.status == 200 && x.readyState == 4){
|
|
//
|
|
//
|
|
// console.log(x)
|
|
// var blob = new Blob([x.response], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
|
|
const byteCharacters = atob(x.responseText);
|
|
const byteNumbers = new Array(byteCharacters.length);
|
|
for (let i = 0; i < byteCharacters.length; i++) {
|
|
byteNumbers[i] = byteCharacters.charCodeAt(i);
|
|
}
|
|
const byteArray = new Uint8Array(byteNumbers);
|
|
const blob = new Blob([byteArray], {
|
|
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
|
|
});
|
|
|
|
const link = document.createElement("a");
|
|
link.href = URL.createObjectURL(blob);
|
|
link.download = _label+'-export.xlsx';
|
|
document.body.appendChild(link);
|
|
link.click();
|
|
|
|
// Cleanup
|
|
URL.revokeObjectURL(link.href);
|
|
document.body.removeChild(link);
|
|
|
|
}else{
|
|
//
|
|
// something went wront
|
|
}
|
|
})
|
|
}
|
|
}
|
|
this.render = function (){
|
|
var _args = this._args
|
|
var _importSheet = this.open
|
|
var _exportSheet = this.export
|
|
var _icon = $('<img>').attr('src',this._args.icon)
|
|
var _label = $('<div class="bold"></div>').html(this._args.label+'<div style="font-weight:lighter; font-size:11px;">'+this._args.provider+'</div>')
|
|
read = this.read
|
|
_compute = this.compute
|
|
_xbutton = $('<div class="border-left"><div class="active apply" align="center"><i class="fa-solid fa-play"></i></div></div>').on('click',function (){
|
|
|
|
|
|
var _id = '.code.'+_args.label.trim()
|
|
_query = $(_id).val()
|
|
|
|
if (_query.length != ''){
|
|
var _data = read(_args.label,_query,_compute)
|
|
|
|
|
|
}
|
|
})
|
|
|
|
_openFile = $('<div class="border-left"><div class="active" title="Open File"><i class="fa-regular fa-folder-open"></i></div></div>')
|
|
$(_openFile).on('click',()=>{
|
|
var _id = '.'+_args.label+' .open-file'
|
|
$(_id).on('change',(event)=>{
|
|
var file = event.target.files[0]
|
|
_id = '.'+_args.label + ' .output'
|
|
_importSheet(_id,file)
|
|
})
|
|
$(_id).click()
|
|
})
|
|
_saveFile = $('<div class="border-left"><div class="active" title="Save File"><i class="fa-solid fa-floppy-disk"></i></div></div>')
|
|
$(_saveFile).on('click',()=>{
|
|
var _id = '.'+_args.label +' .output'
|
|
var spreadsheet = $(_id)[0].spreadsheet
|
|
_exportSheet(_args.label,spreadsheet)
|
|
})
|
|
_wizButton = $('<div class="border-left"><div class="active" title="New Connection"><i class="fa-solid fa-plug-circle-plus" style="color:#4682B4;"></i></div></div>')
|
|
_wizButton.on('click',function(){
|
|
dialog.show({uri:'www/html/wizard.html',title:'Create New Connection',context:studio._context})
|
|
|
|
})
|
|
_expandButton = $('<div class="border-left"><div class="active expand"><i class="fa-solid fa-angle-up" title="Expand spreadsheet"></div></div></div>')
|
|
$(_expandButton).attr('label',_args.label)
|
|
$(_expandButton).on('click',function(){
|
|
var _id = '.studio.'+$(this).attr('label') // + ' .expand'
|
|
if ($(_id).html().match(/fa-angle-up/)){
|
|
_icon = '<i class="fa-solid fa-angle-down"></i>'
|
|
_title = "Collapse Spreadsheet"
|
|
$(_id).addClass('studio-expand')
|
|
}else{
|
|
_icon = '<i class="fa-solid fa-angle-up"></i>'
|
|
$(_id).removeClass('studio-expand')
|
|
_title = "Expand Spreadsheet"
|
|
|
|
}
|
|
$(_id+' .expand').empty().html(_icon).attr('title',_title)
|
|
var spreadsheet = $(_id+' .output')[0].spreadsheet
|
|
spreadsheet.refresh()
|
|
})
|
|
|
|
var _buttons = $('<div style="display:grid; grid-template-columns: auto repeat(5,64px); gap:4px;" align="center"><div> </div></div>')
|
|
_buttons.append(_openFile,_saveFile,_wizButton,_xbutton,_expandButton)
|
|
|
|
// _frame = $('<div class="studio :label"></div>'.replace(/:label/,this._args.label))
|
|
var _frame = $('<div class="studio"></div>') .addClass(this._args.label)
|
|
//
|
|
// Input frame has 2 components (control, textarea)
|
|
_ctrl = $('<div class="border control" align="left"></div>')
|
|
_ctrl.append(_icon,_label,_buttons) //.html(this._args.label))
|
|
_textarea = $('<textarea class="code"></textarea>').addClass(this._args.label).attr('label',_args.label)
|
|
if (studio.defaults[_args.provider] != null){
|
|
_query = studio.defaults[_args.provider]
|
|
$(_textarea).val(_query)
|
|
}
|
|
|
|
$(_textarea).on('keydown',function (_e){
|
|
if (_e.shiftKey && (_e.keyCode == 13 || _e.key == 'Enter')){
|
|
var _id = $(this).attr('label')
|
|
_id = `.${_id} .apply`
|
|
$(_id).click()
|
|
}
|
|
})
|
|
_outputframe = $('<div class="output" ></div><div class="status"></div><input type="file" class="open-file" style="display:none" accept=".csv, .xls, .xlsx"/>')
|
|
|
|
|
|
// $(_frame).append(_inputframe, _outputframe)
|
|
$(_frame).append(_ctrl,_textarea,_outputframe)
|
|
$('.studio-pane').append(_frame)
|
|
|
|
this.compute()
|
|
|
|
}
|
|
|
|
this.compute = function (_label,_data){
|
|
_label = (_label != null)?_label: this._args.label
|
|
_id = '.'+_label + ' .output'
|
|
_data = (_data == null)? {'data':[],'columns':[]}: _data ;
|
|
if (_data.data == null){
|
|
_data = {data:[],columns:[]} //{'columns':[],'data':[[]]}
|
|
}
|
|
|
|
$(_id).empty()
|
|
_gHandler = new studio.grid()
|
|
_gHandler.syncfusion(_id,_data)
|
|
_rows = (_data.data)?_data.data.length : 0
|
|
$('.'+_label + ' .status').html(_rows + ' rows')
|
|
|
|
|
|
}
|
|
} |