Beispiel #1
0
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
Beispiel #2
0
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
Beispiel #3
0
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)
Beispiel #4
0
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
Beispiel #5
0
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
Beispiel #6
0
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)
Beispiel #7
0
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
Beispiel #8
0
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)
Beispiel #9
0
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)
Beispiel #10
0
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)
Beispiel #11
0
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)
Beispiel #12
0
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)
Beispiel #13
0
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)
Beispiel #14
0
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)
Beispiel #15
0
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)
Beispiel #16
0
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
Beispiel #17
0
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)
Beispiel #18
0
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)
Beispiel #19
0
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
Beispiel #20
0
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)
Beispiel #21
0
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
Beispiel #22
0
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)
Beispiel #23
0
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 })
Beispiel #24
0
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)
Beispiel #25
0
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 []
Beispiel #26
0
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)
Beispiel #27
0
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)
Beispiel #28
0
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)
Beispiel #29
0
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)
Beispiel #30
0
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