def on_message(self, message):
log('ws_global', 'message:' + str(message))
subprocess.run(['ss --tcp state CLOSE-WAIT --kill'], shell=True)
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth')
#print('ws sesid:', sesid)
squery = '''
SELECT *
FROM framework.fn_notifications_bysess(_sess:=%s)
'''
result = None
oldresult = []
while self.opened:
yield gen.sleep(5)
try:
result = yield self.db.execute(squery, (sesid, ))
except Exception as err:
err = str(err)
self.opened = False
self.write_message('{"error":"' +
(err[err.find('HINT:') +
5:err.find('+++___')]).split('\n')[0] +
'"}')
self.close()
return
result = result.fetchone()[0]
if len(oldresult) != len(result):
oldresult = result
self.write_message(dumps(result))
self.close()
return
def on_message(self, message):
try:
message = loads(message)
except Exception as e:
self.write_message('{"error":"wrong data"}')
return
log('ws_messages', 'message:' + str(message))
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth')
squery = "select * from framework.fn_fapi(injson:=%s,apititle:='chats_messages',apitype:='1',sessid:=%s,primaryauthorization:=%s)"
result = None
oldresult = None
while self.opened:
yield gen.sleep(2)
try:
result = yield self.db.execute(squery, (
extras.Json(message),
sesid,
str(primaryAuthorization),
))
except Exception as err:
err = str(err)
self.write_message('{"error":"chats_messages' +
(err[err.find('HINT:') +
5:err.find('+++___')]).split('\n')[0] +
'"}')
self.close()
return
result = result.fetchone()[0].get('outjson')
if str(oldresult) != str(result):
oldresult = result
self.write_message(dumps(result))
#self.finish()
return
def on_close(self):
#print('Connection closed')
log('ws_closed', 'SUCCESS 4')
self.opened = False
#print('self.sending', self.opened)
self.close()
#self.finish()
return
def get(self, url):
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
log('/admin/getsettings_Error',str(e))
return
userdetail = userdetail.fetchone()[0]
if userdetail is None:
self.set_status(401,None)
self.write('{"message":"no session or session was killed"}')
return
roles = userdetail.get('roles')
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
filename = args.get('filename')
if filename is None:
self.set_status(500,None)
self.write('{"message":"enter filename"}')
return
filepath = './logs/' + filename
f = open(filepath,encoding='utf-8')
file_text = f.read()
f.close()
file_arr = file_text.split('\n')
res_json = []
for x in file_arr:
if x.find('||') != -1:
res_json.append({'log_line': x})
else:
index = len(res_json) - 1
if index >= 0:
res_json[index] = {'log_line':res_json[index].get('log_line') + ' || ' + x}
self.write(dumps(res_json))
return
def get(self, url):
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
path = args.get('path')
if path is None:
showError('HINT:path not specified +++___', self)
return
method = url[7:].replace('/','').lower()
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
return
userdetail = userdetail.fetchone()[0]
userdetail['sessid'] = sesid
squery = 'SELECT framework."fn_view_getByPath_showSQL"(%s)'
result = []
roles = (userdetail.get('roles') or [])
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
try:
result = yield self.db.execute(squery,(path,))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()
if not result:
self.set_status(500,None)
self.write('{"message":"view is not found"}')
return
result = result[0]
#self.write(dumps(result))
query = getList(result, {}, userdetail=userdetail)
squery = query[0]
self.write(squery)
def post(self, url):
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
return
userdetail = userdetail.fetchone()[0]
if userdetail is None:
self.set_status(401,None)
self.write('{"message":"no session or session was killed"}')
return
roles = userdetail.get('roles')
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
body = loads(self.request.body.decode('utf-8'))
#settingsFile = body.get('settings')
settingsFile = body
squery = 'select * from framework.fn_mainsettings_save(%s)'
try:
userdetail = yield self.db.execute(squery,(extras.Json(settingsFile),))
except Exception as e:
showError(str(e), self)
return
if settingsFile:
try:
df = open('./settings.json','wt')
df.write(dumps(settingsFile))
df.close()
df = open('./settings.py','at')
df.write(' ')
df.close
except Exception as e:
showError(str(e), self)
return
log('/admin/savesettings',' settingsFile:' + str(settingsFile) + ' userdetail: ' + str(userdetail))
self.write('{"message":"OK"}')
def on_message(self, message):
try:
message = loads(message)
except Exception as e:
self.write_message('{"error":"wrong data"}')
return
log('ws', 'message:' + str(message))
#print('self.sending', self.opened)
viewpath = message.get('viewpath')
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth')
if viewpath is None:
self.write_message('{"error":"view path is None"}')
return
squery = '''
SELECT *
FROM framework.fn_fapi(injson:=%s,apititle:='notifs',apitype:='1',sessid:=%s,primaryauthorization:=%s)
'''
result = None
oldresult = []
while self.opened:
yield gen.sleep(5)
#print('self.ws_connection', self.ws_connection)
try:
result = yield self.db.execute(squery, (
extras.Json(message),
sesid,
str(primaryAuthorization),
))
except Exception as err:
err = str(err)
self.opened = False
self.write_message('{"error":"' +
(err[err.find('HINT:') +
5:err.find('+++___')]).split('\n')[0] +
'"}')
self.close()
return
result = result.fetchone()[0].get('outjson')
if len(oldresult) != len(result):
oldresult = result
self.write_message(dumps(result))
self.close()
return
def get(self, url):
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
log('/admin/getsettings_Error',str(e))
return
userdetail = userdetail.fetchone()[0]
if userdetail is None:
self.set_status(401,None)
self.write('{"message":"no session or session was killed"}')
return
roles = userdetail.get('roles')
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
substr = args.get('substr') or ''
pagenum = int(args.get('pagenum') or 1)
pagesize = int(args.get('pagesize') or 20)
off = (pagenum * pagesize) - pagesize
logs = listdir(path = './logs')
lgs = []
for x in logs:
if (x.find(substr) != -1):
lgs.append({'filename':x})
lgs = lgs[off:]
lgs = lgs[:pagesize]
self.write(dumps(lgs))
def onFileUpload(self, url, type):
'''
Function for post request on universal api for upload file (for class Uplo)
'''
args = {} #variable for arguments or body
method = url[4:] #cut 4 symbols from url start, work only if it will be api/
files = [] #variable for files
sesid = self.get_cookie("sesid") or self.request.headers.get('Auth') #get session id cookie
if type != 1 and self.request.headers.get('Content-Type').find('multipart/form-data') == -1:
log(url, 'args: ' + str(self.request.arguments) + '; body: ' + str(self.request.body.decode('utf-8')) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
else:
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
files = self.request.files
value = args.get('value')
if not value:
value = '[]'
if files:
value = loads(value)
if args.get('config') and loads(args.get('config')).get('type') in ('file','image') and len(value) > 0:
showError('for type file/image can not be more then 1 file',self)
return
value = value + savefile(self)
#args['value'] = dumps(value)
self.set_header('Content-Type','application/json charset="utf-8"')
self.write(dumps(value, indent=4, default=lambda x:str(x),ensure_ascii=False))
self.set_status(200,None)
self.finish()
def getList(result, body, userdetail=None):
squery = ' '
gropby = ' '
sgroupby = None
config = result.get('config')
order_by = []
filters = result.get('filters')
useroles = []
if userdetail:
useroles = userdetail.get('roles') or []
#check filters roles
if filters:
filteredFilters = []
for filter in filters:
if 'roles' in filter and len(filter.get('roles')) > 0:
fRoles = []
for obj in filter.get('roles'):
fRoles.append(obj.get('value'))
fRoles.append(developerRole)
if len(list(set(fRoles) & set(useroles)))>0:
filteredFilters.append(filter)
else:
filteredFilters.append(filter)
filters = filteredFilters
result['filters'] = filteredFilters
#check acts roles
if result.get('acts'):
filteredActs = []
for act in result.get('acts'):
if 'roles' in act and len(act.get('roles')) > 0:
fAct = []
for obj in act.get('roles'):
fAct.append(obj.get('value'))
fAct.append(developerRole)
if len(list(set(fAct) & set(useroles)))>0:
filteredActs.append(act)
else:
filteredActs.append(act)
result['acts'] = filteredActs
if config:
filteredConfig = []
for conf in config:
if 'roles' in conf and conf.get('roles') is not None and len(conf.get('roles')) > 0:
cRoles = conf.get('roles')
#cRoles.append(developerRole)
if len(list(set(cRoles) & set(useroles))) > 0:
filteredConfig.append(conf)
else:
filteredConfig.append(conf)
config = filteredConfig
result['config'] = config
joins = ''
orderby = ''
where = ''
defaultOrderBy = ''
for col in config:
roles = []
if col.get('roles'):
roles = col.get('roles')
if isinstance(col.get('roles'), str):
roles = loads(roles)
newroles = []
if roles:
for obj in roles:
newroles.append(obj)
newroles.append(developerRole)
if (newroles is None or len(newroles) == 0 or (len(newroles)>0
and len(list(set(newroles) & set(useroles)))>0)):
colT = str(col.get('t'))
if ('relation' not in col or col.get('relation') is None) and col.get('related')==False:
colT = '1'
sColT = str(col.get('t'))
if 'table' not in col or col.get('table') is None:
sColT = '1'
if col.get('fn') is None:
if not col.get('depency'):
if col.get('type') != 'password':
squery += 't' + sColT + '."' + col.get('col') + '" as "' + (col.get('key') or col.get('title')) + '", '
gropby += 't' + sColT + '."' + col.get('col') + '",'
else:
squery += "'' as \"" + (col.get('key') or col.get('title')) + "\", "
gropby += "'' ,"
else:
relcols = ''
if str(col.get('relationcolums')) != '[]':
if col.get('depfunc'):
for k in col.get('relationcolums'):
relcols += 't' + colT + '."' + k.get('value') + '",'
relcols = relcols[:len(relcols) - 1]
squery += ('(SELECT ' + col.get('depfunc') +
'(' + relcols + ') FROM ' + col.get('relation') +
' as t' + colT + ' WHERE t' +
colT + '.' + col.get('depencycol') +
' = t1.id ) as "' + (col.get('key') or col.get('title')) + '",')
gropby += ' t1.id ,'
else:
for k in col.get('relationcolums'):
relcols += 't' + colT + '."' + k.get('value') + '",'
relcols = relcols[:len(relcols) - 1]
squery += ('coalesce((SELECT array_to_json(array_agg(row_to_json(d))) FROM ((SELECT ' +
relcols +
' FROM ' + col.get('relation') +
' as t' + colT +
' WHERE t' + colT +
'."' + col.get('depencycol') +
'" = t1.id )) as d),\'[]\') as "' + (col.get('key') or col.get('title')) +
'", ')
gropby += ' t1.id ,'
else:
squery += col.get('fn').get('label') + '( '
if col.get('fn').get('functype') == 'groupby':
sgroupby = gropby
squery += ' distinct '
else:
for cl in col.get('fncolumns'):
if str(cl.get('label')) not in ('_userid_', '_orgid_', '_orgs_', '_sesid_', '_viewid_'):
gropby += 't' + str(cl.get('t')) + '."' + str(cl.get('label')) + '",'
else:
if str(cl.get('label')) == '_userid_':
gropby += str(userdetail.get('id') or 'null') + ','
if str(cl.get('label')) == '_orgid_':
gropby += str(userdetail.get('orgid') or 'null') + ','
if str(cl.get('label')) == '_orgs_':
gropby += "'" + str(userdetail.get('orgs') or 'null') + "',"
if str(cl.get('label')) == '_sesid_':
gropby += "'" + str(userdetail.get('sessid') or 'null') + "',"
if str(cl.get('label')) == '_viewid_':
gropby += "'" + str(result.get('id') or 'null') + "',"
for cl in col.get('fncolumns'):
if str(cl.get('label')) not in ('_userid_', '_orgid_', '_orgs_', '_sesid_', '_viewid_'):
squery += 't' + str(cl.get('t')) + '."' + str(cl.get('label')) + '",'
else:
if str(cl.get('label')) == '_userid_':
squery += str(userdetail.get('id') or 'null') + ','
if str(cl.get('label')) == '_orgid_':
squery += str(userdetail.get('orgid') or 'null') + ','
if str(cl.get('label')) == '_orgs_':
squery += "'" + str(userdetail.get('orgs') or 'null') + "',"
if str(cl.get('label')) == '_sesid_':
squery += "'" + str(userdetail.get('sessid') or 'null') + "',"
if str(cl.get('label')) == '_viewid_':
squery += "'" + str(result.get('id') or 'null') + "',"
if col.get('fn').get('label') == 'concat':
squery += "' ',"
squery = squery[:len(squery)-1]
squery += ') as "' + (col.get('key') or col.get('title')) + '", '
if col.get('relation') and not col.get('depency') and not col.get('related'):
if 'join' in col:
if col.get('join'):
joins += ' JOIN ' + col.get('relation') + ' as t' + colT + ' on t1."' + col.get('col') + '" = t' + colT + '."' + (col.get('relcol') or 'id') + '"'
else:
joins += ' LEFT JOIN ' + col.get('relation') + ' as t' + colT + ' on t1."' + col.get('col') + '" = t' + colT + '."' + (col.get('relcol') or 'id') + '"'
else:
joins += ' LEFT JOIN ' + col.get('relation') + ' as t' + colT + ' on t1."' + col.get('col') + '" = t' + colT + '."' + (col.get('relcol') or 'id') + '"'
if col.get('tpath'):
i = 1
tpath = col.get('tpath')
while i < len(tpath):
if col.get('join'):
if joins.find(' as ' + tpath[i].get('t')) == -1:
joins += ' JOIN ' + tpath[i].get('table') + ' as ' + tpath[i].get('t') + ' on ' + tpath[i-1].get('t') + '."' + tpath[i].get('col') + '" = ' + tpath[i].get('t') + '."' + (col.get('relcol') or 'id') + '"'
else:
if joins.find(tpath[i].get('t')) == -1:
joins += ' LEFT JOIN ' + tpath[i].get('table') + ' as ' + tpath[i].get('t') + ' on ' + tpath[i-1].get('t') + '."' + tpath[i].get('col') + '" = ' + tpath[i].get('t') + '."' + (col.get('relcol') or 'id') + '"'
i += 1
if joins.find(" as t" + str(col.get('t'))) == -1:
joins += " LEFT JOIN " + col.get("table") + " as t" + str(col.get('t')) + " on t" + str(col.get('t')) + '."' + (col.get("relcol") or "id") + '" = ' + tpath[i-1].get('t') + '."' + col.get('relatecolumn') + '"'
colname = ''
if col.get('fn') is None:
colname = 't' + sColT + '."' + col.get("col") + '"'
else:
colname = col.get('fn').get('label') + '( '
for cl in col.get('fncolumns'):
if str(cl.get('label')) not in ('_userid_', '_orgid_', '_orgs_','_sesid_'):
colname += 't' + str(cl.get('t')) + '."' + str(cl.get('label')) + '",'
else:
if str(cl.get('label')) == '_userid_':
colname += str(userdetail.get('id') or 'null') + ','
if str(cl.get('label')) == '_orgid_':
colname += str(userdetail.get('orgid') or 'null') + ','
if str(cl.get('label')) == '_orgs_':
colname += "'" + str(userdetail.get('orgs') or 'null') + "',"
if str(cl.get('label')) == '_sesid_':
colname += "'" + str(userdetail.get('sessid') or 'null') + "',"
colname = colname[:len(colname)-1]
colname += ')'
if col.get('defaultval'):
defv = ''
if len(col.get('defaultval'))>0:
defv = '('
for d in col.get('defaultval'):
def_v = d.get('value')
act_v = d.get('act').get('value')
bool_v = d.get('bool').get('value')
if act_v == 'like' or act_v == 'not like':
if def_v == '_userid_':
userid = str(userdetail.get('id'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '%" + userid + "%' "
elif def_v == '_orgid_':
orgid = str(userdetail.get('orgid'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '%" + orgid + "%' "
elif def_v == '_sesid_':
sId = str(userdetail.get('_sesid_'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '%" + sId + "%' "
else :
defv += bool_v + ' ' + colname + ' ' + act_v + " '%" + def_v + "%' "
elif act_v == 'is null' or act_v == 'is not null':
defv += bool_v + ' ' + colname + ' ' + act_v
elif act_v == 'in' or act_v == 'not in':
if def_v == '_orgs_':
userorgs = str(userdetail.get('orgs'))
if col.get('type') == 'array':
defv += bool_v + " (select count(*) from json_array_elements_text('" + userorgs + "') as j1" + ' join json_array_elements_text("' + colname + '") as j2 on j1.value::varchar=j2.value::varchar)>0 '
else:
defv += bool_v + ' ' + colname + '::varchar ' + act_v + "(select value::varchar from json_array_elements_text('" + userorgs + "')) "
elif def_v == '_userid_':
userid = str(userdetail.get('id'))
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " ('" + userid + "') "
elif def_v == '_sesid_':
sId = str(userdetail.get('sessid'))
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " ('" + sId + "') "
elif def_v == '_orgid_':
orgid = str(userdetail.get('orgid'))
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " ('" + orgid + "') "
elif def_v.find(',') != -1:
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " (select value::varchar from json_array_elements_text('[" + def_v + "]')) "
defv = defv.replace('[','["').replace(',','","').replace(']','"]')
else :
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " ('" + def_v + "') "
else:
if def_v == '_orgs_':
userorgs = str(userdetail.get('orgs'))
defv += bool_v + ' ' + colname + '::varchar ' + act_v + " (select value::varchar from json_array_elements_text('" + userorgs + "')) "
elif def_v == '_userid_':
userid = str(userdetail.get('id'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '" + userid + "' "
elif def_v == '_orgid_':
orgid = str(userdetail.get('orgid'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '" + orgid + "' "
elif def_v == '_sesid_':
sId = str(userdetail.get('sessid'))
defv += bool_v + ' ' + colname + ' ' + act_v + " '" + sId + "' "
else :
defv += bool_v + ' ' + colname + ' ' + act_v + " '" + def_v + "' "
if len(defv) > 0:
defv = defv.replace('(or','( ')
defv = defv.replace('(and','( ')
defv += ')'
where += 'and ' + defv + ' '
if col.get('orderby'):
t = '1'
if col.get('related'):
t = str(col.get('t'))
defaultOrderBy += ' t' + t + '."' + col.get('col') + '"'
if col.get('orderbydesc'):
defaultOrderBy += ' desc'
defaultOrderBy += ','
if 'inputs' in body:
order_by = body.get('inputs').get('orderby') or []
if body.get('inputs').get(col.get('title')):
if body.get('inputs').get(col.get('title')) == '_orgs_':
body['inputs'][col.get('title')] = userdetail.get('orgs')
elif body.get('inputs').get(col.get('title')) == '_userid_':
body['inputs'][col.get('title')] = userdetail.get('id')
elif body.get('inputs').get(col.get('title')) == '_orgid_':
body['inputs'][col.get('title')] = userdetail.get('orgid')
#where += 'and t' + sColT + '."' + col.get('col') + '" = \'' + formatInj(body.get('inputs').get(col.get('title'))) + "' "
where += 'and ' + colname + ' = \'' + formatInj(body.get('inputs').get(col.get('title'))) + "' "
body['inputs'][col.get('title')] = None
#if col.get('required') and body.get('inputs'):
# where += 'and t' + sColT + '."' + col.get('col') + '" = \'' + (body.get('inputs').get(col.get('title')) ) + '\' '
if col.get('required') and not body.get('inputs'):
where += 'and t' + sColT + '."' + col.get('col') + '" = null '
if len(filters) > 0:
for col in filters:
#print('col:',col)
if 'filters' in body:
if (col.get('type') == 'typehead' and col.get('title') in body.get('filters')) or str(col.get('column')) in body.get('filters'):
if col.get('type') == 'select':
if body.get('filters').get(col.get('column')):
where += 'and t' + str(col.get('t') or '1') + '.' + col.get('column') + " = '" + formatInj(body.get('filters').get(col.get('column'))) + "' "
elif col.get('type') == 'substr':
where += (
'and (upper(coalesce(t' + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::varchar,'')) like upper('%" + formatInj(body.get('filters').get(col.get('column'))) +
"%') OR upper(coalesce(t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::varchar,'')) like substring(upper('" + formatInj(body.get('filters').get(col.get('column'))) +
"'),3,length('" + formatInj(body.get('filters').get(col.get('column'))) +
"')) OR upper(coalesce(t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::varchar,'')) like upper(concat(substring('" + formatInj(body.get('filters').get(col.get('column'))) +
"',3,14),substring('" + formatInj(body.get('filters').get(col.get('column'))) + "',19,13)))) "
)
elif col.get('type') == 'period':
if ('date1' in body.get('filters').get(col.get('column'))) and ('date2' in body.get('filters').get(col.get('column'))):
where += ("and t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' + "::date >= '" +
formatInj(body.get('filters').get(col.get('column')).get('date1')) +
"' and t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::date <= '" + formatInj(body.get('filters').get(col.get('column')).get('date2')) + "' ")
elif col.get('type') == 'date_between':
if formatInj(body.get('filters').get(col.get('column'))) is not None:
where += (
"and t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' + "::date >= '" +
formatInj(body.get('filters').get(col.get('column'))) +
"' and t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::date <= '" + formatInj(body.get('filters').get(col.get('column'))) + "' "
)
elif col.get('type') == 'multiselect':
if len(body.get('filters').get(col.get('column'))) > 0 :
where += ("and (t" + str(col.get('t') or '1') + '."' + col.get('column') + '"' +
"::varchar in ( select (value::varchar::json)->>'value'::varchar from json_array_elements_text('" +
dumps(body.get('filters').get(col.get('column'))) + "')) or ( select count(*) from json_array_elements_text('" +
dumps(body.get('filters').get(col.get('column'))) + "') where (value::varchar::json)->>'value' is null )>0)")
elif col.get('type') == 'multijson':
if len(body.get('filters').get(col.get('column'))) > 0:
where += ("and ( select count(*) from json_array_elements_text('" +
dumps(body.get('filters').get(col.get('column'))) + "') as a JOIN json_array_elements_text(t" + str(col.get('t') or '1') + '."' +
col.get('column') + '"' + ") as b on (a.value::varchar::json)->>'value'::varchar = b.value::varchar or (a.value::varchar::json->>'value') is null )>0 ")
elif col.get('type') == 'check' and body.get('filters').get(col.get('column')) is not None:
ch = 'false'
if body.get('filters').get(col.get('column')) == True:
ch = 'true'
where += 'and coalesce(t' + str(col.get('t') or '1') + '."' + col.get('column') + '",false) = ' + str(ch) + ' '
elif col.get('type') == 'typehead':
v = formatInj(body.get('filters').get(col.get('title')))
if v:
where += 'and ('
if len(v.split(' ')) > 2:
i = 0
v = v.split(' ')
cols = col.get('columns')
while i < len(cols):
if len(v) >= i + 1:
where += ' and '
if cols[i].get('t'):
where += ' lower(t' + str(cols[i].get('t') or '1') + '."' + cols[i].get('label') + '"' + "::varchar) like lower('" + str(v[i]) + "%') "
else:
where += ' lower(' + cols[i].get('label') + "::varchar) like lower('" + str(v[i]) + "') "
i += 1
where = where.replace('( and','(') + ' ) '
else:
for x in col.get('columns'):
where += ' or '
if x.get('t'):
where += ' lower(t' + str(x.get('t') or '1') + '."' + x.get('label') + '"' + "::varchar) like lower('%" + str(v).strip() + "%') "
else:
where += ' lower(' + x.get('label') + "::varchar) like lower('%" + str(v).strip() + "%') "
where = where.replace('( or','(') + ' ) '
pagenum = 1
pagesize = 30
rownum = ''
pagewhere = ''
pageselect = ''
if sgroupby:
sgroupby = ' GROUP BY ' + sgroupby[:len(sgroupby)-1]
else:
sgroupby = ''
if result.get('pagination'):
if body.get('pagination') and 'pagenum' in body.get('pagination'):
pagenum = int(body.get('pagination').get('pagenum'))
if body.get('pagination') and 'pagesize' in body.get('pagination'):
pagesize = int(body.get('pagination').get('pagesize'))
page1 = (pagenum * pagesize) - pagesize
pageselect = 'SELECT * FROM ('
pagewhere += ' LIMIT ' + str(pagesize) + ' OFFSET ' + str(page1) + ') as pz '
rownum += 'ROW_NUMBER() over ( order by '
if len(order_by) > 0:
for col in order_by:
t = '1'
if col.get('related'):
t = str(col.get('t'))
if col.get('fn') is None:
rownum += 't' + t + '."' + col.get('col') + '"::varchar ' + col.get('desc') + ','
else:
rownum += col.get('fn').get('value') + '( '
if col.get('fncols'):
for x in col.get('fncols'):
rownum += 't' + str(x.get('t')) + '."' + x.get('label') + '",'
rownum = rownum[:len(rownum) - 1]
rownum += ') ' + col.get('desc') + ','
rownum = rownum[:len(rownum) - 1]
elif len(defaultOrderBy) > 0:
rownum += defaultOrderBy[:len(defaultOrderBy) - 1]
else:
rownum += 't1.id desc'
rownum += ') as rownum '
if result.get('viewtype').find('form') != -1:
pagewhere = ' LIMIT 2 '
elif not result.get('pagination'):
pagewhere = ' LIMIT 300 '
if len(where) > 0:
where = ' WHERE ' + where[3:]
squery = (pageselect + 'SELECT ' + rownum + ', ' + squery[:len(squery)-2] +
' FROM ' + result.get('tablename') + ' as t1 ' + joins + where + sgroupby +
pagewhere)#orderby
count = 'SELECT count(*) as count FROM ' + result.get('tablename') + ' as t1 ' + joins + where #orderby
log('sql_migration', squery + ' userdetail: ' + str(userdetail))
return squery, count
def post(self, url):
method = url[
5:] #cut 4 symbols from url start, work only if it will be auth/
log(url, str(self.request.body))
self.clear_cookie('sesid')
if method == 'logout':
sesid = self.get_cookie('sesid')
if sesid:
squery = 'select * from framework.fn_logout(%s)'
result = None
try:
result = yield self.db.execute(squery, (sesid, ))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
self.write('{"message":"OK"}')
elif method == 'auth_f':
body = loads(self.request.body.decode('utf-8'))
login = body.get('login')
passw = body.get('pass')
sesid = self.request.headers.get('Auth')
passw = sha224(passw.encode('utf-8')).hexdigest()
if login is None or passw is None:
self.write('{"message":"login or password is null"}')
self.set_status(500, None)
return
squery = 'select * from framework.fn_sess(%s,%s,%s);'
try:
result = yield self.db.execute(squery, (login, passw, sesid))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()[0]
self.set_cookie('sesid', result)
self.write('{"message":"OK"}')
elif method == 'auth_crypto':
body = loads(self.request.body.decode('utf-8'))
sesid = self.request.headers.get('Auth')
squery = 'select * from framework.fn_cryptosess(%s,%s);'
try:
result = yield self.db.execute(squery, (
extras.Json(body),
sesid,
))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()[0]
self.set_cookie('sesid', result)
self.write('{"message":"OK"}')
else:
self.set_status(404, None)
self.write('{"message":"method not found"}')
def onRequest(self, url, type):
'''
Function for get,post,put and delete requests on universal api (for class FApi)
'''
args = {} #variable for arguments or body
method = url[4:] #cut 4 symbols from url start, work only if it will be api/
files = [] #variable for files
sesid = self.get_cookie("sesid") or self.request.headers.get('Auth') #get session id cookie
if type != 1 and self.request.headers.get('Content-Type').find('multipart/form-data') == -1:
log(url, 'args: ' + str(self.request.arguments) + '; body: ' + str(self.request.body.decode('utf-8')) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
else:
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
if type in (2,4):
files = self.request.files
body = {}
if files:
value = args.get('value')
if not value:
value = '[]'
value = loads(value)
if args.get('config') and loads(args.get('config')).get('type') in ('file','image') and len(value) > 0:
showError('for type file/image can not be more then 1 file',self)
return
value = value + savefile(self)
args['value'] = dumps(value)
else:
body = loads(self.request.body.decode('utf-8')) #request body, expecting application/json type
for k in args:
body[k] = args.get(k)
args = body
for k in args:
if args[k] == '':
args[k] = None
squery = 'select * from framework.fn_fapi(injson:=%s,apititle:=%s,apitype:=%s,sessid:=%s,primaryauthorization:=%s)'
result = None
try:
result = yield self.db.execute(squery,(extras.Json(args),method,str(type),sesid,str(primaryAuthorization),))
except Exception as e:
log(url + '_Error',' args: ' +
str(extras.Json(args)) + '; sess: ' +
str(sesid) + '; type: ' + str(type) + '; Error:' + str(e))
showError(str(e), self)
return
result = result.fetchone()[0]
self.set_header("Content-Type",'application/json charset="utf-8"')
self.write(dumps(result, indent=4, default=lambda x:str(x),ensure_ascii=False))
self.set_status(200,None)
self.finish()
def Report(self, url):
"""
Function for call node js report method and get xls or xlsx file
"""
args = {} #variable for arguments or body
report_path = url[4:] #cut 4 symbols from url start, work only if it will be rep/
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + sesid + '; type: 1')
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
if args.get('filename') is None:
showError('{"message":"filename is empty"}', self)
return
injson = {'injson':args, 'sess':sesid, 'report_path':report_path}
squery = 'select * from reports.fn_call_report(injson:=%s)'
result = None
try:
result = yield self.db.execute(squery,(extras.Json(injson),))
except Exception as e:
log(url + '_Error',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
res = result.fetchone()[0]
data = res.get('outjson')
reqBody = {'template':'..' + res.get('template_path'),'data':dumps(data), 'filename':args.get('filename')}
http_client = AsyncHTTPClient();
req = HTTPRequest(
url=reports_url,
method='POST',
headers={'Content-Type':'application/json'},
body=dumps(reqBody),
connect_timeout=200.0,
request_timeout=200.0
);
try:
req = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
log(url + '_Error_NodeJs',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
except Exception as err:
system('cd reports && node index.js') # try start reports server
try:
req = yield http_client.fetch(req)
except Exception as err:
showError('No connection to the report server',self)
return
if res.get('ishtml'):
html_report = StringIO()
reportFilePath = './files/' + str(uuid4()) + '.xlsx'
reportFile = open(reportFilePath, 'wb')
reportFile.write(req.buffer.read())
reportFile.close()
html = xlsx2html(reportFilePath, html_report)
html_report.seek(0)
html_report = html_report.read()
self.set_header('Content-Type', 'text/html')
html_report += (
'<script>window.print()</script>' +
'<style type="text/css" media="print">' +
'@page { size: auto; margin: 0mm; } </style>'
)
self.write(html_report)
else:
self.set_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.set_header('Cache-Control', 'public')
self.set_header('Content-Disposition', 'attachment; filename=' + args.get('filename') + '.xlsx')
self.set_header('Content-Description', 'File Transfer')
self.write(req.body)
self.set_status(200)
self.finish()
def post(self, url):
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
path = args.get('path')
if path is None:
showError('HINT:path not specified +++___', self)
return
body = loads(self.request.body.decode('utf-8'))
method = url[7:].replace('/','').lower()
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
log(url, 'path: '+ path + '; body: ' + str(body) + ' sessid:' + str(sesid) )
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
return
userdetail = userdetail.fetchone()[0]
userdetail['sessid'] = sesid
#userdetail = userdetail.get('outjson')
if method == 'list':
squery = 'SELECT framework."fn_view_getByPath"(%s,%s)'
result = []
try:
result = yield self.db.execute(squery,(path,'list',))
except Exception as e:
showError(str(e), self)
return
result = result.fetchone()[0]
if not result:
self.set_status(500,None)
self.write('{"message":"view is not found"}')
return
#result = result[0]
if len(result.get('roles')) > 0:
x = False
else:
x = True
for col in result.get('roles'):
if col.get('value') in (userdetail.get('roles') or []) and not x:
x = True
if not x:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
user = {}
# if exist initial action onLoad
actions = result.get('acts')
onLoad = None
for act in actions:
if act.get('type') == 'onLoad':
onLoad = act
if onLoad:
req_url = onLoad.get('act')
if 'inputs' in body and onLoad.get('parametrs') is not None:
req_url += '?'
for param in onLoad.get('parametrs'):
req_url += param.get('paramtitle') + '=' + (str(body.get('inputs').get(param.get('paraminput')) or '') ) + '&'
if req_url[:4] != 'http':
req_url = maindomain + req_url
if onLoad.get('actapitype').lower() == 'get':
req = HTTPRequest(
url = req_url,
method = onLoad.get('actapitype'),
headers = {'Cookie':'sesid=' + sesid}
)
else:
req_body = {}
if onLoad.get('parametrs') is not None:
for param in onLoad.get('parametrs'):
req_body[param.get('paramtitle')] = body.get('inputs').get(param.get('paraminput'))
req = HTTPRequest(
url = req_url,
body = dumps(req_body),
method = onLoad.get('actapitype'),
headers = {'Cookie':'sesid=' + sesid}
)
try:
response = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
showError(str(e), self)
log(req_url + '_Error_onLoad', str(e))
log(req_url + '_Error_act', str(onLoad))
return
# if exist initial action onLoad
data = []
count = 0
config = result.get('config')
filters = result.get('filters')
acts = result.get('acts')
title = result.get('title')
classname = result.get('classname')
pagination = result.get('pagination')
pagecount = result.get('pagecount')
ispagesize = result.get('ispagesize')
isfoundcount = result.get('isfoundcount')
subscrible = result.get('subscrible')
orderby = result.get('orderby')
checker = result.get('checker')
if result.get('viewtype').find('api_') == -1:
query = getList(result, body, userdetail=userdetail)
acts = result.get('acts')
config = result.get('config')
squery = query[0]
scounquery = query[1]
try:
data = yield self.db.execute(squery)
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
data = curtojson([x for x in data],[x[0] for x in data.description])
try:
count = yield self.db.execute(scounquery)
except Exception as e:
showError(str(e), self)
log(url + '_Error_count', str(e))
return
count = count.fetchone()[0]
else:
req_url = result.get('tablename')
if req_url[:4] != 'http':
req_url = maindomain + req_url
req = HTTPRequest(
url = req_url,
body = dumps(body),
method = 'POST',
headers = {'Cookie':'sesid=' + sesid}
)
try:
response = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
showError(str(e), self)
log(req_url + ' api error', str(e))
log(url + '_error_act', str(onLoad))
return
data = loads(response.body.decode('utf-8'))
if data is not None:
if 'foundcount' in data:
count = data.get('foundcount')
else:
count = None
if 'config' in data and data.get('config') is not None:
config = data.get('config')
if 'acts' in data and data.get('acts') is not None:
acts = data.get('acts')
if 'filters' in data and data.get('filters') is not None:
filters = data.get('filters')
if 'classname' in data:
classname = data.get('classname')
if 'title' in data:
title = data.get('title')
if 'pagination' in data:
pagination = data.get('pagination')
if 'pagecount' in data:
pagecount = data.get('pagecount')
if 'ispagesize' in data:
ispagesize = data.get('ispagesize')
if 'isfoundcount' in data:
isfoundcount = data.get('isfoundcount')
if 'subscrible' in data:
subscrible = data.get('subscrible')
if 'orderby' in data:
orderby = data.get('orderby')
if 'checker' in data:
checker = data.get('checker')
if 'outjson' in data:
data = data.get('outjson')
else:
data = []
useroles = userdetail.get('roles') or []
if acts:
filteredActs = []
for act in acts:
if 'roles' in act and len(act.get('roles')) > 0:
fAct = []
for obj in act.get('roles'):
fAct.append(obj.get('value'))
fAct.append(developerRole)
if len(list(set(fAct) & set(useroles))) > 0:
filteredActs.append(act)
else:
filteredActs.append(act)
acts = filteredActs
if count is None:
count = len(data)
self.write(dumps({
'foundcount': count, 'data': data, 'config': config, 'filters': filters, 'acts': acts,
'classname': classname, 'title': title, 'viewtype': result.get('viewtype'), 'pagination': pagination,
'ispagecount': pagecount, 'ispagesize': ispagesize, 'isfoundcount': isfoundcount, 'subscrible': subscrible,
'isorderby': orderby, 'viewid': result.get('id'), 'checker': checker, 'user':user
}))
elif method == 'getone':
squery = 'SELECT framework."fn_view_getByPath"(%s,%s)'
result = []
try:
result = yield self.db.execute(squery,(path,'getone',))
except Exception as e:
showError(str(e), self)
return
result = result.fetchone()[0]
if not result:
self.set_status(500,None)
self.write('{"message":"view is not found"}')
return
#result = result[0]
if len(result.get('roles')) > 0:
x = False
else:
x = True
for col in result.get('roles'):
if col.get('value') in (userdetail.get('roles') or []) and not x:
x = True
if not x:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
# if exist initial action onLoad
actions = result.get('acts')
onLoad = None
for act in actions:
if act.get('type') == 'onLoad':
onLoad = act
if onLoad:
req_url = onLoad.get('act')
if 'inputs' in body and onLoad.get('parametrs') is not None:
req_url += '?'
for param in onLoad.get('parametrs'):
req_url += param.get('paramtitle') + '=' + (str(body.get('inputs').get(param.get('paraminput')) or '') ) + '&'
if req_url[:4] != 'http':
req_url = maindomain + req_url
if onLoad.get('actapitype').lower() == 'get':
req = HTTPRequest(
url = req_url,
method = onLoad.get('actapitype'),
headers = {'Cookie':'sesid=' + sesid}
)
else:
req_body = {}
if onLoad.get('parametrs') is not None:
for param in onLoad.get('parametrs'):
req_body[param.get('paramtitle')] = body.get('inputs').get(param.get('paraminput'))
req = HTTPRequest(
url = req_url,
body = dumps(req_body),
method = onLoad.get('actapitype'),
headers = {'Cookie':'sesid=' + sesid}
)
try:
response = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
showError(str(e), self)
log(req_url + '_Error_onLoad', str(e))
log(url + '_Error_act', str(onLoad))
return
# if exist initial action onLoad
data = []
config = result.get('config')
filters = result.get('filters')
acts = result.get('acts')
title = result.get('title')
classname = result.get('classname')
subscrible = result.get('subscrible')
if result.get('viewtype').find('api_') == -1:
query = getList(result, body, userdetail=userdetail)
acts = result.get('acts')
config = result.get('config')
squery = query[0]
try:
data = yield self.db.execute(squery)
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
data = curtojson([x for x in data],[x[0] for x in data.description])
else:
req_url = result.get('tablename')
if req_url[:4] != 'http':
req_url = maindomain + req_url
req = HTTPRequest(
url = req_url,
body = dumps(body),
method = 'POST',
headers = {'Cookie':'sesid=' + sesid}
)
try:
response = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
showError(str(e), self)
log(req_url + ' api error', str(e))
log(url + '_error_act', str(onLoad))
return
data = loads(response.body.decode('utf-8'))
if data is not None:
if 'config' in data and data.get('config') is not None:
config = data.get('config')
if 'acts' in data and data.get('acts') is not None:
acts = data.get('acts')
if 'filters' in data and data.get('filters') is not None:
filters = data.get('filters')
if 'classname' in data:
classname = data.get('classname')
if 'title' in data:
title = data.get('title')
if 'subscrible' in data:
subscrible = data.get('subscrible')
if 'outjson' in data:
data = data.get('outjson')
else:
data = []
useroles = userdetail.get('roles') or []
if acts:
filteredActs = []
for act in acts:
if 'roles' in act and len(act.get('roles')) > 0:
fAct = []
for obj in act.get('roles'):
fAct.append(obj.get('value'))
fAct.append(developerRole)
if len(list(set(fAct) & set(useroles))) > 0:
filteredActs.append(act)
else:
filteredActs.append(act)
acts = filteredActs
if len(data) > 1:
self.set_status(500,None)
self.write('{"message":"getone can\'t return more then 1 row"}')
return
#count = count.fetchone()[0]
self.set_status(200,None)
self.write(dumps({
'data': data, 'config': config, 'acts': acts, 'classname': classname,
'table': result.get('tablename'), 'subscrible': subscrible,
'title': title, 'viewtype': result.get('viewtype'), 'id': result.get('id')
}))
elif method == 'squery':
squery = '''
SELECT row_to_json (d)
FROM (
SELECT *
FROM framework.views where path = %s
) as d
'''
result = []
roles = userdetail.get('roles')
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
try:
result = yield self.db.execute(squery,(path,))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()
if not result:
self.set_status(500,None)
self.write('{"message":"view is not found"}')
return
result = result[0]
#self.write(dumps(result))
query = getList(result, body, userdetail=userdetail)
squery = query[0]
self.write(dumps({'squery':squery + '; '}))
else:
self.set_status(404,None)
self.write('{"message":"method not found"}')
return