def _get_table_infomation(userid):
data = db.session.execute(
db.session.query()
.from_statement(text(""" select orgprefix from admin where id = :userid """))
.params(userid=userid)
)
ooo = []
for d in data:
print d
orgs = d.orgprefix.split(",")
for o in orgs:
ooo.append("t.table_name like '" + o + "\_%'")
sqlstr = """
SELECT t.table_name , concat(ROUND(DATA_LENGTH/1024/1024,2),'MB') AS data_length
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_SCHEMA='test'
and
""" + " or ".join(
ooo
)
print sqlstr
query = db.session.query().from_statement(text(sqlstr)).params(userid=userid)
data = query_to_list_json(query)
return data
def _get_table_and_columns_infomation(userid):
query = (
db.session.query()
.from_statement(
text(
"""
SELECT
t.table_name, c.column_name, c.data_type
FROM information_schema.tables t,information_schema.columns c , admin a
WHERE 1=1
AND t.table_schema = 'test' AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND a.id = :userid
AND t.table_name LIKE concat(a.orgprefix, '\_%')
ORDER BY 1
"""
)
)
.params(userid=userid)
)
data = query_to_list_json(query)
new_dict = [
{"table_name": k, "columns": [d for d in data if d.get("table_name") == k]}
for k in set(d.get("table_name") for d in data)
]
return new_dict
def listTmpl(sqlId, selId, userId):
# select concat('[',group_concat(tmpls), ']') as tmpls from (
# SELECT
# concat('{"tmpltype":"', tmpltype, '", "subs":[',
# group_concat(concat('{"tmplid":"', v.tmplid, '","tmplnm":"', v.tmplnm, '","filepath":"', v.filepath, '"}')
# SEPARATOR ','), ']}') AS tmpls
# FROM viewtmpl v, dsviewtmpl m
# WHERE 1
# AND v.tmplid = m.tmplid
# AND m.sqlid = :sqlid
# AND m.selid = :selid
# GROUP BY tmpltype
query = db.session.query().from_statement(text(
"""
select concat('[',group_concat(tmpls),']') as tmpls from (
select concat('{"tmplid":"', v.tmplid, '","tmplnm":"', v.tmplnm, '","filepath":"',v.filepath, '","tmpltype":"', v.tmpltype ,'"}' ) as tmpls
FROM viewtmpl v, dsviewtmpl m
WHERE 1
AND v.tmplid = m.tmplid
AND m.sqlid = :sqlid
AND m.selid = :selid
) a
"""
)).params(sqlid=sqlId).params(selid=selId)
return query_to_list_json(query)
def map_query_by_str(query_str, bounds, roundlevel, template):
# print '------- map_query_by_str ------'
bounds = bounds.split(',')
# print query_str
# print bounds
# print roundlevel
# print template
import re
query_str = re.sub(r'AND lat >= [\-]*(\d)+.(\d)+',r'AND lat >= '+str(bounds[0]),query_str)
query_str = re.sub(r'AND latitude >= [\-]*(\d)+.(\d)+',r'AND latitude >= '+str(bounds[0]),query_str)
query_str = re.sub(r'AND lat <= [\-]*(\d)+.(\d)+',r'AND lat <= '+str(bounds[2]),query_str)
query_str = re.sub(r'AND latitude <= [\-]*(\d)+.(\d)+',r'AND latitude <= '+str(bounds[2]),query_str)
query_str = re.sub(r'AND lng >= [\-]*(\d)+.(\d)+',r'AND lng >= '+str(bounds[1]),query_str)
query_str = re.sub(r'AND longitude >= [\-]*(\d)+.(\d)+',r'AND longitude >= '+str(bounds[1]),query_str)
query_str = re.sub(r'AND lng <= [\-]*(\d)+.(\d)+',r'AND lng <= '+str(bounds[3]),query_str)
query_str = re.sub(r'AND longitude <= [\-]*(\d)+.(\d)+',r'AND longitude <= '+str(bounds[3]),query_str)
query_str = re.sub(r'TRUNCATE\((\s)*lat,(\s)*(\d)+(\.)*(\d)*(\s)*\)',r'TRUNCATE(lat,'+str(roundlevel)+')',query_str)
query_str = re.sub(r'TRUNCATE\((\s)*latitude,(\s)*(\d)+(\.)*(\d)*(\s)*\)',r'TRUNCATE(latitude,'+str(roundlevel)+')',query_str)
query_str = re.sub(r'TRUNCATE\((\s)*lng,(\s)*(\d)+(\.)*(\d)*(\s)*\)',r'TRUNCATE(lng,'+str(roundlevel)+')',query_str)
query_str = re.sub(r'TRUNCATE\((\s)*longitude,(\s)*(\d)+(\.)*(\d)*(\s)*\)',r'TRUNCATE(longitude,'+str(roundlevel)+')',query_str)
# print 'query_str is ----------> '
# print query_str
new_dict = {}
new_dict['resformat'] = 'json'
new_dict['resvalue'] = query_to_list_json(db.session.query().from_statement(text(query_str)))
# print '***** resvalue length is *****'
# print len(new_dict['resvalue'])
new_dict['query'] = query_str
new_dict['template'] = template
return new_dict
def _get_tablename_from_sqlid(sqlid):
query = DsQuery.query.filter(DsQuery.sqlid == sqlid)
datas = []
data = query_to_list_json(query)
for row in data:
sql = row['sqlstmt']
print sql.lower()
m = re.search('from\s+(^\s)*[^\(]*\s+where', sql.lower())
print m.group(0)
aa = m.group(0).replace('from','').replace('where','').strip()
if ',' in aa:
bb = aa.split(',')
else:
bb = [aa]
bb = [[ v for v in b.strip().split(' ') if ' ' in b][0] if ' ' in b else b.strip() for b in bb]
print bb
for b in bb:
query = db.session.query().from_statement(text("describe " + b))
result = db.session.execute(query)
for row in result:
# datas.append(b + "->" + row['Field'])
# print row['Field'] + '-' + row['Key']
if row['Key'] == 'PRI':
datas.append({'value':row['Field'] ,'dspnm':b + '.' +row['Field'] + '[primary key]'})
else:
datas.append({'value':row['Field'] ,'dspnm':b + '.' +row['Field']})
# print '_get_tablename_from_sqlid result ---->'
print datas
return datas
def _list_api_info():
db.session.execute('SET group_concat_max_len = 1000000')
query = db.session.query().from_statement(
text(
"""
select concat('{"sqlid":',sqlid,',"sqlnm":"', sqlnm,'","sqldesc":"', sqldesc,'","metrics":', metrics,', "filters":', ifnull(filters,'[]'), '}') as ss from (
select
q.sqlid, q.sqlnm, q.sqldesc,
concat('[', group_concat( concat('{"selid":', selid,',"selnm":"' ,selnm, '","seldesc":"' , seldesc,'"}')), ']') as metrics
, c.filters
from dsquery q left join dsselect s on q.sqlid = s.sqlid
left join (
select sqlid, concat('[',group_concat(vals),']') as filters from (
select
c.sqlid, concat('{"whid":',c.whid,',"colnm":"',colnm,'","values":[', group_concat(concat('{"valid":',valid, ',"valnm":"',valnm, '"}') separator ',' ), ']}') as vals
from dswhcolumn c, dswhvalue v
where c.sqlid = v.sqlid
and c.whid = v.whid
group by c.sqlid, c.whid
) a group by a.sqlid
) c on q.sqlid = c.sqlid
where useyn='y'
group by q.sqlid
) x
"""
))
return query_to_list_json(query)
def __get_values_from_sqlid(sqlid, whid, valstr):
# print '===> begin __get_values_from_sqlid... ' + sqlid + ' / ' + whid + ' / ' + valstr
query = DsQuery.query.filter(DsQuery.sqlid == sqlid)
datas = []
data = query_to_list_json(query)
if whid is None:
wwhid = -1
else:
wwhid = whid
# print data
for row in data:
sql = row['sqlstmt']
print sql.lower()
m = re.search('from\s+(^\s)*[^\(]*\s+where', sql.lower())
# print m.group(0)
aa = m.group(0).replace('from','').replace('where','').strip()
print aa
if ',' in aa:
bb = aa.split(',')
cc = []
for b in bb:
if 'left join' in b:
cc.append(b.split('left join')[0].split(' ')[0])
cc.append(b.split('left join')[1].split(' ')[0])
else:
if ' ' in b:
cc.append(b.strip().split(' ')[0])
else:
print cc
print b
cc.append(b)
bb = cc
elif 'left join' in aa:
bb = [aa.split('left join')[0].strip().split(' ')[0], aa.split('left join')[1].strip().split(' ')[0]]
else:
bb = [aa]
bb = [[ v for v in b.strip().split(' ') if ' ' in b][0] if ' ' in b else b.strip() for b in bb]
print '====> now what contains in bb '
print bb
for b in bb:
query = db.session.query().from_statement(text("select a.valstr, b.valnm, if(b.valstr is NULL, 0, 1) as chk from ( select distinct "+valstr+" as valstr from "+b+" ) a left join (select * from dswhvalue where sqlid = "+str(sqlid)+" and whid= "+str(wwhid)+") b on a.valstr= b.valstr "))
try:
datas += query_to_list_json(query)
except:
print 'error'
print datas
return datas
def __view_dswhcolumn(sqlid):
query = db.session.query().from_statement(
text(
"""
select whid, sqlid, colstr, colnm, operand, coltype, filtertype from dswhcolumn where sqlid = :sqlid and whid = :whid
"""
)).params(sqlid=sqlid).params(whid=whid)
return query_to_list_json(query)
def list_templates_for_map():
query = db.session.query().from_statement(
text(
"""
SELECT * FROM viewtmpl v WHERE UPPER(v.filepath) LIKE UPPER('%google%map%')
"""
))
return query_to_list_json(query)
def checkWhQuery(sqlid, colids):
if colids and len(colids)>0:
valqstr = "and v.valid in (" + colids + ")"
else:
valqstr = ""
query = db.session.query().from_statement(
text("""select c.whid, c.sqlid, c.colstr,c.colnm,c.operand,c.coltype,c.filtertype,v.valid,v.valstr,v.valnm from dswhcolumn c, dswhvalue v where c.whid=v.whid and c.sqlid=v.sqlid and c.sqlid=:sqlid """+valqstr+""" """)).params(sqlid=sqlid)
return query_to_list_json(query)
def q_list_query_for_map(userid):
query = db.session.query().from_statement(
text(
"""
-- SELECT DISTINCT q.* FROM dsquery q, dsselect s WHERE q.sqlid = s.sqlid AND UPPER(s.selcols) LIKE UPPER('%lat%lng%')
SELECT DISTINCT q.* FROM dsquery q, dsquery_admin a, dsselect s WHERE q.sqlid = s.sqlid AND UPPER(s.selcols) LIKE UPPER('%lat%lng%') AND q.sqlid = a.sqlid AND a.adminid = :userid
"""
)).params(userid=userid)
return query_to_list_json(query)
def __list_menu_users(menuid):
query = db.session.query().from_statement(
text(
"""
select a.id, a.opnm, a.email, m.menuid
from chartmaker a left join wavemenu_admin m on m.adminid = a.id and m.menuid = :menuid where a.svcname = 'wave'
"""
)).params(menuid=menuid)
return query_to_list_json(query)
def list_where_menus(sqlid):
query = db.session.query().from_statement(
text("""
select concat('[', group_concat(json_str separator ',') ,']') as json_str from (
select
concat( '{"id":', c.whid, ',"nm":"', c.colnm, '","sub":[', group_concat(concat('{"id":',v.valid, ', "nm":"', v.valnm, '","desc":"', v.valstr ,'"}') separator ',') ,']}') as json_str
from dswhcolumn c, dswhvalue v where c.whid = v.whid and c.sqlid = :sqlid
group by c.whid, c.colnm ) a
""")).params(sqlid=sqlid)
return query_to_list_json(query)
def list_mapboard_items_from_model(mapboard_id):
query = db.session.query().from_statement(
text(
"""
select
i.*
from mapboard_item i
where i.mapboard_id = :mapboard_id
"""
)).params(mapboard_id=mapboard_id)
return query_to_list_json(query)
def __list_dsquery_users(sqlid):
# print str(sqlid) + ' in __list_dsquery_users'
query = db.session.query().from_statement(
text(
"""
SELECT a.id, a.opnm, a.email, m.sqlid, a.orgprefix
FROM admin a LEFT JOIN dsquery_admin m ON m.adminid = a.id AND m.sqlid = :sqlid
where svcname = 'wave'
"""
)).params(sqlid=sqlid)
return query_to_list_json(query)
def _get_columns_infomation(table_name):
query = db.session.query().from_statement(
text(
"""
SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='"""
+ table_name
+ """';
"""
)
)
data = query_to_list_json(query)
return data
def __list_viewtmpl(sqlid, selid):
selstmt = ""
if selid > 0:
selstmt = """ and selid = """ + str(selid)
query = db.session.query().from_statement(
text(
"""
select v.tmplid, v.tmplnm, v.tmpldesc, v.filepath, d.sqlid, d.selid
from viewtmpl v left join dsviewtmpl d on v.tmplid = d.tmplid and d.sqlid = :sqlid and selid = :selid
"""
)).params(sqlid=sqlid).params(selid=selid)
return query_to_list_json(query)
def list_mapboard_from_model(userid):
query = db.session.query().from_statement(
text(
"""
select
m.id, m.title, m.desc, m.adminid, ifnull(group_concat(i.title),'') as items
from mapboard m left join mapboard_item i on m.id = i.mapboard_id
where 1=1
and m.adminid = :userid
group by m.id, m.title, m.desc, m.adminid
"""
)).params(userid=userid)
return query_to_list_json(query)
def executeQuery(exeinfo):
query_str = compileQuery(exeinfo)
query = db.session.query().from_statement(query_str)
new_dict = {}
new_dict['restitle'] = exeinfo.selname + ' of ' + exeinfo.stmtname
new_dict['resformat'] = 'json'
new_dict['resvalue'] = query_to_list_json(query)
new_dict['query'] = query_str
new_dict['template'] = exeinfo.template
# print new_dict['resvalue']
return new_dict
def listDataSet(userid):
query = db.session.query().from_statement(text("""
select q.sqlid, sqlnm, sqldesc
from dsquery q, dsquery_admin qa , admin a
where 1=1
and q.sqlid = qa.sqlid
and qa.adminid = a.id
and a.opid = :userid
and q.useyn = 'y'
and lower(sqlnm) not like '%map%'
""" )).params(userid=userid)
return query_to_list_json(query)
def _execute_query_from_fiddle(query_str):
query = db.session.query().from_statement(text(query_str + " limit 100"))
result = {}
try:
data = query_to_list_json(query)
result["data"] = data
result["status"] = "success"
except SQLAlchemyError as e:
result["status"] = "error"
print e
result["data"] = str(e)
return result
def descriptions(sqlId,userId):
query = db.session.query().from_statement(
text(
"""
select 'query' as types, concat('<strong>', sqlnm,'</strong><br/>',sqldesc, '') as descs from dsquery where sqlid = :sqlid
union all
select 'select' as types, group_concat(seldesc,',') as descs from dsselect a where a.sqlid = :sqlid and exists (select 'e' from wavemenu b, wavemenu_admin c, admin d where a.sqlid=b.sqlid and a.selid=b.dstblid and b.dstbltype='dsselect' and b.menuid=c.menuid and c.adminid=d.id and d.opid = :userid)
union all
select 'where' as types, group_concat(colstr,',') as descs from dswhcolumn a where a.sqlid = :sqlid and exists (select 'e' from wavemenu b, wavemenu_admin c, admin d, dswhvalue e where a.sqlid=b.sqlid and e.whid=a.whid and e.valid=b.dstblid and b.dstbltype='dswhvalue' and b.menuid=c.menuid and c.adminid=d.id and d.opid = :userid)
union all
select 'template' as types, group_concat(distinct a1.tmpldesc,',') as descs from viewtmpl a1, dsviewtmpl a where a.tmplid = a1.tmplid and a.sqlid = :sqlid and exists (select 'e' from wavemenu b, wavemenu_admin c, admin d, dsselect e where a.selid = e.selid and a.sqlid=b.sqlid and e.selid=b.dstblid and b.dstbltype='dsselect' and b.menuid=c.menuid and c.adminid=d.id and d.opid = :userid)
"""
)).params(sqlid=sqlId).params(userid=userId).params(sqlid=sqlId)
return query_to_list_json(query)
def _test_dsselect_conntection(sqlid, selcols, grpbystmt, havingstmt, orderbystmt):
query_full_str = selcols+grpbystmt+havingstmt+orderbystmt + ' limit 1'
query = db.session.query().from_statement(text(query_full_str))
syntax_err = False
print query_full_str
try:
result = query_to_list_json(query_full_str)
except Exception as e:
e_str = str(e.message)
syntax_err = True
pass
if syntax_err:
return json.dumps({'result':'error', 'message':e_str, 'query':query_full_str})
else:
return json.dumps({'result':'success', 'resvalue':result, 'message':'', 'query':query_full_str })
def list_query_for_map(userid):
query = db.session.query().from_statement(
text(
"""
select q.sqlid, sqlnm, sqldesc
from dsquery q, dsquery_admin qa , admin a
where 1=1
and q.sqlid = qa.sqlid
and qa.adminid = a.id
and a.opid = :userid
and q.sqlid in (select sqlid from dsselect s where s.selcols LIKE UPPER('%lat%lng%') )
and q.useyn = 'y'
and lower(sqlnm) like '%map%'
"""
)).params(userid=userid)
return query_to_list_json(query)
def _available_columns(sqlid):
print '__available_columns(' + str(sqlid) + ')'
query = DsQuery.query.filter(DsQuery.sqlid == sqlid)
data = query_to_list_json(query)
ret = []
for row in data:
query = db.session.query().from_statement(text(row['sqlstmt'] + " limit 1 "))
try:
result = db.session.execute(query)
row = result.fetchone()
# print result.keys()
return result.keys()
except Exception as e:
print e
return []
return []
def __list_dsselect(sqlid,selid):
selidstmt = ""
if selid > 0:
selidstmt = """ and d.selid = """ + str(selid)
query = db.session.query().from_statement(
text(
"""
select
d.sqlid, d.selid, d.selnm, d.seldesc, d.selcols, d.grpbystmt, d.havingstmt, d.orderbystmt, if(a.cnt is NULL, 0, a.cnt) as templates
from dsselect d left join (select sqlid, selid, count(*) cnt from viewtmpl v, dsviewtmpl m where v.tmplid = m.tmplid group by sqlid, selid) a on a.sqlid = d.sqlid and a.selid = d.selid
where 1=1
and d.sqlid = :sqlid
""" + selidstmt
)).params(sqlid=sqlid)
return query_to_list_json(query)
def __list_dsquery(userid):
query = db.session.query().from_statement(
text(
"""
select
q.*,
ifnull((select count(*) as cnt from dsselect t where t.sqlid = q.sqlid ),0) as selcnt,
ifnull((select count(*) as cnt from dswhcolumn t where t.sqlid = q.sqlid ),0) as colcnt,
ifnull((select count(*) as cnt from dswhvalue t where t.sqlid = q.sqlid ),0) as valcnt
from dsquery q , dsquery_admin qa
where 1
and q.sqlid = qa.sqlid
and qa.adminid = :userid
"""
)).params(userid=userid)
return query_to_list_json(query)
def listMenus(sqlId, userId):
query = db.session.query().from_statement(
text(
"""
select m1 as parent , group_concat(m2,'->',m3) as children from (
select m1, m2, group_concat(m3 SEPARATOR '#') as m3 from (
select
concat(m1.menuid,':',m1.dspnm,':',m1.dstbltype,':',m1.dstblid,':',m1.multiselectyn) as m1,
concat(m2.menuid,':',m2.dspnm,':',m2.dstbltype,':',m2.dstblid,':',m2.multiselectyn) as m2,
concat(m3.menuid,':',m3.dspnm,':',m3.dstbltype,':',m3.dstblid,':',m3.multiselectyn) as m3
from wavemenu as m1
left join wavemenu as m2 on m2.parentid = m1.menuid
left join wavemenu as m3 on m3.parentid = m2.menuid
and exists (select 'x' from wavemenu_admin u, admin a where u.menuid = m3.menuid and u.adminid=a.id and a.opid = :userid )
where m1.parentid = 0 and m1.sqlid = :sqlid
) a group by m1, m2
) a
group by m1
"""
)).params(sqlid=sqlId).params(userid=userId)
return query_to_list_json(query)
def __list_menus_for_admin(sqlid,userid,m_type):
chkquery = db.session.query().from_statement(
text(
"""
select * from wavemenu m1 where m1.parentid = 0 and m1.sqlid = :sqlid and m1.dspnm = :m_type
"""
)).params(sqlid=sqlid).params(m_type=m_type)
results = db.session.execute(chkquery)
if len(list(results)) == 0:
if m_type == 'metric':
dstbltype = 'dsselect'
elif m_type == 'condition':
dstbltype = 'dswhcolumn'
initData = {'parentid':0, 'dspnm':m_type, 'dstbltype':dstbltype, 'dstblid':0, 'sqlid':sqlid, 'leafyn':'N', 'multiselectyn':'N'}
wavemenu = WaveMenu.create(**initData)
print wavemenu
query = db.session.query().from_statement(
text(
"""
select concat( '{',m1,', "nodes":[', group_concat(m2), ']', '}' ) as data from (
select m1, if(m2 is NULL, NULL, if(m2!='', concat('{',m2,',"nodes":[', group_concat(m3) ,']','}' ), '')) as m2 from (
select
if(m1.menuid is not NULL, concat('"id":"',m1.menuid,'", "title":"',m1.dspnm,'","tbltype":"',m1.dstbltype,'","tblid":"',m1.dstblid,'", "multiselectyn":"',m1.multiselectyn,'"'),'') as m1,
if(m2.menuid is not NULL, concat('"id":"',m2.menuid,'", "title":"',m2.dspnm,'","tbltype":"',m2.dstbltype,'","tblid":"',m2.dstblid,'", "multiselectyn":"',m2.multiselectyn,'"'),'') as m2,
if(m3.menuid is not NULL, concat('{"id":"',m3.menuid,'", "title":"',m3.dspnm,'","tbltype":"',m3.dstbltype,'","tblid":"',m3.dstblid,'", "multiselectyn":"',m3.multiselectyn,'"}'), '') as m3
from wavemenu as m1
left join wavemenu as m2 on m2.parentid = m1.menuid
left join wavemenu as m3 on m3.parentid = m2.menuid
where m1.parentid = 0 and m1.sqlid = :sqlid and m1.dspnm = :m_type
) a group by m2
) a
"""
)).params(sqlid=sqlid).params(m_type=m_type)
return query_to_list_json(query)
def getExeQuery(sqlId, selId, whvalId, tmplId, userId, sdt, edt, limit, bounds, roundlevel):
print 'getExeQuery result : ' + str(whvalId)
colids = sum([[ a for a in k['colvals']] for k in whvalId], [])
print 'colids is ' + str(colids)
# print 'colids.join() is ' + str(colids).strip('[]')
data = checkWhQuery(sqlId, str(colids).strip('[]') )
print 'checkWhQuery result is ' + str(data)
for idx, k in enumerate(whvalId):
for i in data :
print data
if str(k['whid']) == str(i['whid']):
# print 'is not true????'
k['whid'] = i['whid']
k['coltype'] = i['coltype']
k['operand'] = i['operand'] # operand is =, in, > , <, >=, <=
k['colstr'] = i['colstr']
k['filtertype'] = i['filtertype']
whvalId[idx] = k
new_whquery = []
print '---- new setted whvalId is -----'
print whvalId
for k in whvalId:
if k['coltype'] != 'number':
squato = "\\'"
else:
squato = ""
if len(k['colvals']) > 0:
_str_colvals = " and v.valid in ( " + ",".join(map(str,k['colvals'])) + ")"
else:
_str_colvals = ""
if len(k['colvals']) > 0 and k['filtertype'] and k['filtertype'].lower() == 'customezble':
new_whquery.append("""
select 'where' as cmd, cast(concat('and ', colstr,
' """+k['operand']+""" ', cast(concat('"""+squato+"""',group_concat(concat(cast(v.valstr as char)) SEPARATOR '|'),'"""+squato+"""') as char(4000) CHARACTER SET utf8)) as char(1000)) as val
,'' as name from dswhcolumn c, dswhvalue v where c.sqlid = v.sqlid and c.whid = v.whid and c.sqlid = """+str(sqlId)+"""
and v.whid = """+str(k['whid'])+""" """+ _str_colvals +""" group by c.sqlid, c.whid""")
elif len(k['colvals']) > 0:
if k['operand'] in ['in']:
new_whquery.append("""select 'where' as cmd, cast(concat('and ', colstr,
' """+k['operand']+""" (', cast(group_concat(concat('"""+squato+"""',cast(v.valstr as char),'"""+squato+"""') SEPARATOR ',') as char(4000) CHARACTER SET utf8), ')') as char(1000)) as val
,'' as name from dswhcolumn c, dswhvalue v where c.sqlid = v.sqlid and c.whid = v.whid and c.sqlid = """+str(sqlId)+"""
and v.whid = """+str(k['whid'])+""" """+ _str_colvals +""" group by c.sqlid, c.whid""")
elif k['operand'] in ['<=', '<', '>', '>=']:
new_whquery.append("""
select 'where' as cmd, cast(concat('and ', colstr,
' """+k['operand']+""" ', cast(group_concat(concat(cast(v.valstr as char)) SEPARATOR ',') as char(4000) CHARACTER SET utf8)) as char(1000)) as val
,'' as name from dswhcolumn c, dswhvalue v where c.sqlid = v.sqlid and c.whid = v.whid and c.sqlid = """+str(sqlId)+"""
and v.whid = """+str(k['whid'])+""" """+ _str_colvals +""" group by c.sqlid, c.whid""")
elif k['operand'] in ['=']:
new_whquery.append("""select 'where' as cmd, cast(concat('and ', colstr,
' """+k['operand']+""" ', cast(group_concat(concat('"""+squato+"""',cast(v.valstr as char),'"""+squato+"""') SEPARATOR ',') as char(4000) CHARACTER SET utf8)) as char(1000)) as val
,'' as name from dswhcolumn c, dswhvalue v where c.sqlid = v.sqlid and c.whid = v.whid and c.sqlid = """+str(sqlId)+"""
and v.whid = """+str(k['whid'])+""" """+ _str_colvals +""" group by c.sqlid, c.whid""")
sqlstr = """
select 'stmt' as cmd, sqlstmt as val, sqlnm as name from dsquery where sqlid = :sqlId
union all
select 'select' as cmd, selcols as val,selnm as name from dsselect where sqlid = :sqlId and selid = :selId
union all
select 'groupby' as cmd, grpbystmt as val ,'' as name from dsselect where sqlid = :sqlId and selid = :selId
union all
select 'having' as cmd, havingstmt as val,'' as name from dsselect where sqlid = :sqlId and selid = :selId
union all
select 'orderby' as cmd, orderbystmt as val,'' as name from dsselect where sqlid = :sqlId and selid = :selId
union all
select 'resformat' as cmd, resfmt as val,'' as name from dsselect where sqlid = :sqlId and selid = :selId
union all
select 'template' as cmd, filepath as val,'' as name from viewtmpl t, dsviewtmpl d where t.tmplid = d.tmplid and sqlid = :sqlId and selid = :selId and d.tmplid = :tmplId
"""
if len(new_whquery) > 0:
sqlstr += """ union all """ + "\n union all ".join(new_whquery)
print sqlstr
query = db.session.query().from_statement(
sqlstr
).params(sqlId=sqlId, selId=selId, tmplId=tmplId)
data = query_to_list_json(query)
exeinfo = ExeQuery()
exeinfo.sdt = sdt
exeinfo.edt = edt
exeinfo.limit = limit
# print 'roundlevel----------------'
# print roundlevel
# print bounds
if roundlevel:
exeinfo.roundlevel = roundlevel
else:
exeinfo.roundlevel = None
for k in data:
# print '#######>' + json.dumps(k)
if k['cmd'] == 'stmt':
exeinfo.stmt = k['val']
exeinfo.stmtname = k['name']
elif k['cmd'] == 'select':
exeinfo.select = k['val']
exeinfo.selname = k['name']
elif k['cmd'] == 'where':
exeinfo.where = exeinfo.where + ' ' + k['val']
elif k['cmd'] == 'groupby':
exeinfo.groupby = k['val']
elif k['cmd'] == 'having':
exeinfo.having = k['val']
elif k['cmd'] == 'orderby':
exeinfo.orderby = k['val']
elif k['cmd'] == 'resformat':
exeinfo.resformat = k['val']
elif k['cmd'] == 'template':
exeinfo.template = k['val']
bounds_str = ''
# bounds = [16.539216496008894,42.75338379488741,111.8718348967285,142.1501552092285];
if bounds:
if 'latitude'.upper() in exeinfo.select.upper() and 'longitude'.upper() in exeinfo.select.upper():
bounds_str = bounds_str + ' AND latitude >= ' + str(bounds[0]) + ' AND latitude <= ' + str(bounds[2])
bounds_str = bounds_str + ' AND longitude >= ' + str(bounds[1]) + ' AND longitude <= ' + str(bounds[3])
elif 'lat'.upper() in exeinfo.select.upper() and 'lng'.upper() in exeinfo.select.upper():
bounds_str = bounds_str + ' AND lat >= ' + str(bounds[0]) + ' AND lat <= ' + str(bounds[2])
bounds_str = bounds_str + ' AND lng >= ' + str(bounds[1]) + ' AND lng <= ' + str(bounds[3])
exeinfo.where = exeinfo.where + ' ' + bounds_str
exeinfo.outerlatlng = None
exeinfo.groupbylatlng = None
groupbyLatLng(exeinfo)
return exeinfo