def upload_query_result_count(request, scn_id): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0] scn_name = this_scenario.scn_name scn_name_lower = scn_name.lower() if request.method == 'POST': query_result_count = request.form.get('query_result_count') ## decode JSON count_decoded = json.loads(query_result_count) ## insert count data into problem count table row_count = count_decoded['rowCount'] new_count_query = """INSERT INTO {namespace_name}.iqp_problem_count (Problem_Time, Scn_ID, Problem_Count) VALUES ((select extract(epoch from now())), {scn_id}, {row_count}) """.format(namespace_name=org_namespace_name, scn_id=scn_id, row_count=row_count) new_count_s = text(new_count_query) session.execute(new_count_s) ## commit all changes to db session.commit() return render_template('welcome.html')
def scenario_main_chart_options_data_source(request, s_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name s_name_lower = s_name.lower() for scenario in session.query(Scenario).filter(Scenario.scn_name==s_name).all(): this_scenario = scenario s_id = this_scenario.scn_id data = {} query = """select frequent_column_name from scenario_clicks_count where scn_id = {sid} order by frequency_number desc limit 5;""".format(sid = s_id) s = text(query) rs = session.execute(s).fetchall() data["most-five"] = [[str(item['frequent_column_name'])] for item in rs] query2 = """select column_name from INFORMATION_SCHEMA.COLUMNS where column_name not in (select frequent_column_name from scenario_clicks_count order by frequency_number desc limit 5) and table_name = '{scen_name_lower}' and table_schema = '{namespace_name}' order by column_name;""".format( namespace_name=org_namespace_name, scen_name_lower=s_name_lower.lower()) s2 = text(query2) rs2 = session.execute(s2).fetchall() data["others"] = [[str(item['column_name'])] for item in rs2] result = json.dumps(data) return Response(result, mimetype='application/json')
def downgrade(): ''' ''' try: # Remove OptionsData.fetch_id Column: session.execute('ALTER TABLE options_data DROP COLUMN fetch_id;') session.execute('DROP TABLE options_fetch;') session.commit() except: print traceback.format_exc() session.rollback()
def scenario_main_chart_data_source(request, s_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name groupby = request.args.get('groupby') data = {} if groupby: query = """SELECT {group_by}, COUNT(*) as groupsum FROM {namespace_name}.{scen_name} GROUP BY {group_by}""".format( namespace_name=org_namespace_name, scen_name=s_name, group_by=groupby) s = text(query) rs = session.execute(s).fetchall() data['groupby'] = groupby data['data'] = [[str(item[groupby]), int(item['groupsum'])] for item in rs] else: scenario = session.query(Scenario).filter(Scenario.scn_name==s_name).all()[0] scenario_id = scenario.scn_id s_name_lower = s_name.lower() query1 = """select frequent_column_name from scenario_clicks_count where scn_id = {s_id} order by frequency_number desc limit 1;""".format(s_id=scenario_id) s1 = text(query1) mostly_used = session.execute(s1).fetchall() if mostly_used: query = """SELECT {mostly_used}, COUNT(*) as groupsum FROM {namespace_name}.{scen_name} GROUP BY {mostly_used}""".format( namespace_name=org_namespace_name, scen_name=s_name, mostly_used=mostly_used[0][0]) else: query = """SELECT COUNT(*) as groupsum FROM {namespace_name}.{scen_name}""".format( namespace_name=org_namespace_name, scen_name=s_name) s = text(query) rs = session.execute(s).fetchall() if mostly_used: data['groupby'] = mostly_used[0][0] data['data'] = ([[str(item[mostly_used[0][0]]), int(item['groupsum'])] for item in rs]) else: data['groupby'] = 'All Rows' data['data'] = ([[str('All Rows'), int(item['groupsum'])] for item in rs]) result = json.dumps(data) return Response(result, mimetype='application/json')
def tradablesmapping(): ''' Get a mapping from tradable id to a set of all options.id's ''' mapping = {} tradables = list(session.execute('SELECT id FROM tradables;')) for tradableid, in tradables: query = session.execute( 'SELECT id FROM options where tradable_id = %s;' % tradableid) mapping[tradableid] = set(id for id, in query) return mapping
def upload_query_result_data(request, scn_id): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0] scn_name = this_scenario.scn_name scn_name_lower = scn_name.lower() if request.method == 'POST': query_result_data = request.form.get('query_result_data') ## decode JSON data_decoded = json.loads(query_result_data) ## get column names for table column_names_query = """SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{scn_name_lower}' AND table_schema = '{namespace_name}' """.format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower) column_names_s = text(column_names_query) column_names_result = session.execute(column_names_s).fetchall() column_names = [row[0] for row in column_names_result] if len(column_names) > 0: ## insert all data into the new table def columnValueIfExists(columnName): if columnName not in field_values: return "null" elif field_values[columnName] == '\x00': return "null" else: return "'" + field_values[columnName].replace("'", "''") + "'" rows = data_decoded['rows'] for row in rows: field_values = row['fieldValues'] column_names_string = ','.join([columnName for columnName in column_names]) column_values_string = ','.join([columnValueIfExists(columnName) for columnName in column_names]) ## make insert statement insert_query = """INSERT INTO {namespace_name}.{scn_name_lower}""".format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower) insert_query = insert_query + """({column_names_string}) VALUES""".format(column_names_string=column_names_string) insert_query = insert_query + "(" + column_values_string + ")" insert_s = text(insert_query) session.execute(insert_s) ## commit all changes to db session.commit() return render_template('welcome.html')
def scenario_trend_chart_data_source(request, s_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name s_name = str(s_name) s_name = escape(s_name) for scenario in session.query(Scenario).filter(Scenario.scn_name==s_name).all(): this_scenario = scenario s_id = this_scenario.scn_id query = """SELECT iqp_problem_count.problem_time, iqp_problem_count.problem_count FROM {namespace_name}.iqp_problem_count WHERE iqp_problem_count.scn_id = '{s_id}' ORDER BY iqp_problem_count.problem_time DESC LIMIT 100 OFFSET 0""".format( namespace_name=org_namespace_name, s_id=s_id) s = text(query) rs = session.execute(s).fetchall() data = [] for item in rs: data.append( [int(item['problem_time']*1000), int(item['problem_count'])]) result = json.dumps(data) return Response(result, mimetype='application/json')
def sub_category_proportion_chart_data_source(request, sc_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_sub_category = (session.query(SubCategory) .filter(SubCategory.subcategory_name==sc_name) .all()[0]) sc_id = this_sub_category.subcategory_id issue_filter = request.args.get('filter') if not issue_filter: issue_filter = '' query = """SELECT (CASE WHEN iqp_problem_count_recent.problem_count > 0 THEN 'Issue' ELSE 'No_Issue' END) as issue_or_not, COUNT(iqp_scenarios.scn_id) as issue_or_not_count FROM iqp_scenarios JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) WHERE (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '') AND iqp_scenarios.subcategory_id = '{sc_id}' GROUP BY issue_or_not """.format( namespace_name=org_namespace_name, sc_id=sc_id, filter_type=issue_filter) s = text(query) rs = session.execute(s).fetchall() data = ([[str(item['issue_or_not']), int(item['issue_or_not_count'])] for item in rs]) result = json.dumps(data) return Response(result, mimetype='application/json')
def generateHeaders(self,user_namespace_name): query = """SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{table}'""".format(table = self.tableName.lower()) resultset = session.execute(query).fetchall() headers = [] for header in resultset: headers.append(str(header['column_name'])) return json.dumps(headers)
def generateDataTables(self, userNameSpace): queries = self.generateQuery(userNameSpace) s = text(queries[0]) rs = session.execute(s).fetchall() rowData = [] for item in rs: tableData = {} tableData["cell"] = [str(item[eachColumn]) for eachColumn in self.columnNames] rowData.append(tableData) del tableData s = text(queries[1]) rs = session.execute(s).fetchall() self.data["total"] = rs[0][0] self.data["page"] = self.request.args["page"] self.data["rows"] = rowData # append the headers return json.dumps(self.data)
def upgrade(): ''' ''' # Add OptionsData.fetch_id Foreign Key, and Create Index on the Column: try: # This should create the options_fetch table: print 'Creating Table options_data...' Base.metadata.create_all(engine) session.execute(''' ALTER TABLE options_data ADD COLUMN fetch_id INTEGER REFERENCES options_fetch(id); ''') session.execute(''' CREATE INDEX idx_options_data_fetch_id ON options_data(fetch_id); ''') fetches = _getgroups(cutoff=3) for time, tradableid, _ in fetches: print 'Adding OptionsFetch(%s, %s)...' % (time, tradableid) session.execute( "INSERT INTO options_fetch (tradable_id, time) values (%s, '%s');" % (tradableid, time)) session.flush() # Now that each of the options fetches has an id, we want to update the # FK's on the options data table: tradables = tradablesmapping() fetches = session.execute( 'SELECT id, tradable_id, time FROM options_fetch;') for id, tradableid, time in fetches: print 'Adding options_data.fetch_id FK For Fetch %s..' % id session.execute( "UPDATE options_data SET fetch_id = %s WHERE time = '%s';" % (id, time)) session.flush() _check() session.commit() except: print traceback.format_exc() session.rollback()
def upload_query_result_structure(request, scn_id): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0] scn_name = this_scenario.scn_name scn_name_lower = scn_name.lower() if request.method == 'POST': def columnTypeIfExists(dataType): if dataType not in oracleToPostgresDataTypes: return "varchar" else: return oracleToPostgresDataTypes[dataType] ## get get argument query_result_structure = request.form.get('query_result_structure') ## decode JSON back into python list structure_decoded = json.loads(query_result_structure) ## drop old table in user namespace if it exists droptable_query = """DROP TABLE IF EXISTS {namespace_name}.{scn_name_lower} """.format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower) droptable_s = text(droptable_query) session.execute(droptable_s) ## create new table in user namespace columns = structure_decoded['columns'] column_definition_string = ','.join([column['name'] + ' ' + columnTypeIfExists(column['type']) for column in columns]) newtable_query = """CREATE TABLE {namespace_name}.{scn_name_lower} ( """.format(namespace_name=org_namespace_name, scn_name_lower=scn_name_lower) newtable_query = newtable_query + column_definition_string + """)""" newtable_s = text(newtable_query) session.execute(newtable_s) ## commit all changes to db session.commit() return render_template('welcome.html')
def _getgroups(cutoff=1): ''' Get the ''' query = session.execute(''' SELECT * FROM ( SELECT options_data.time, opts.tradable_id, count(*) AS cnt FROM options_data JOIN ( SELECT id, tradable_id FROM options ) AS opts ON options_data.option_id = opts.id GROUP BY options_data.time, opts.tradable_id ) AS tmp WHERE tmp.cnt > %s ORDER BY tmp.cnt DESC; ''' % cutoff) return list(query)
def _check(): print 'Checking Migration Integrity...' query = session.execute(''' SELECT * FROM ( SELECT options.id, data.tradable_id AS t1, options.tradable_id AS t2 FROM options JOIN ( SELECT options_data.option_id, fetches.tradable_id FROM options_data JOIN ( SELECT id, tradable_id FROM options_fetch ) AS fetches ON fetches.id = options_data.fetch_id ) AS data ON options.id = data.option_id ) AS comp WHERE comp.t1 != t2; ''') results = list(query) assert len(results) == 0 print 'Check passed.'
def category_main_chart_data_source(request, c_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_category = (session.query(Category) .filter(Category.category_name==c_name) .all()[0]) c_id = this_category.category_id issue_filter = request.args.get('filter') if not issue_filter: issue_filter = '' query = """SELECT iqp_subcategories.subcategory_name, COUNT(iqp_scenarios.scn_name) issue_count, SUM(problem_count) as problemsum FROM iqp_subcategories JOIN iqp_scenarios ON (iqp_subcategories.subcategory_id = iqp_scenarios.subcategory_id) JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) JOIN app_permissions ON (iqp_subcategories.subcategory_id = app_permissions.subcategory_id) WHERE iqp_problem_count_recent.problem_count > 0 AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '') AND iqp_subcategories.category_id = '{c_id}' AND app_permissions.user_id = '{user_id}' GROUP BY iqp_subcategories.subcategory_name """.format(namespace_name=org_namespace_name, c_id=c_id, filter_type=issue_filter, user_id=user_id) s = text(query) rs = session.execute(s).fetchall() data = ([[str(item['subcategory_name']), int(item['issue_count']), int(item['problemsum'])] for item in rs]) result = json.dumps(data) return Response(result, mimetype='application/json')
def generateDataTables(request,sc_id,issue_filter): data = { "sEcho": int(request.args["sEcho"]), # we should echo it with the same no, else datatable will not genrate a table "iTotalDisplayRecords": 10, } columns=['name', 'description' , 'current', 'stats_total', 'stats_percentage', 'trend', 'refreshtime'] query = """SELECT iqp_scenarios.scn_name as name, iqp_scenarios.scn_short_description as description, iqp_problem_count_recent.problem_count as current, COALESCE(iqp_problem_count_prev.problem_count, 0) as prev, (iqp_problem_count_recent.problem_count - COALESCE(iqp_problem_count_prev.problem_count, 0)) as trend, iqp_problem_count_recent.problem_time as refreshtime, COALESCE(problem_count_stats.problem_count,999999999) as stats_total, (iqp_problem_count_recent.problem_count / COALESCE(problem_count_stats.problem_count,999999999)) as stats_percentage FROM iqp_scenarios LEFT JOIN iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) LEFT JOIN iqp_problem_count_prev ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id) LEFT JOIN iqp_problem_count_recent problem_count_stats ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id) WHERE iqp_scenarios.subcategory_id = '{sc_id}' AND iqp_problem_count_recent.problem_count > 0 AND iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') AND (iqp_scenarios.scn_type = '{scen_type}' OR '{scen_type}' = '') """.format(sc_id=sc_id, scen_type=issue_filter) #s = text(query) rs = session.execute(query).fetchall() data = [[str(item['name']), str(item['description']), int(item['current']), int(item['stats_total']), float(item['stats_percentage']), int(item['trend']), int(item['refreshtime'])] for item in rs] return json.dumps(data)
def exportScenarioTable(request,s_name): import csv #Response.headers['Content-Type'] = "application/CSV" #Response.headers['Content-Disposition'] = 'attachment; filename= sample.csv' d = Headers() #write the headers #d.add("Pragma", "public") #d.add("Expires","0") #d.add("Cache-Control", must-revalidate, post-check=0, pre-check=0") #d.add('Content-Type', "application/force-download") #d.add("Content-Type","application/octet-stream") d.add("Content-Type","application/octet-stream") d.add('Content-Disposition', 'attachment;filename=iqpgenerated.csv') #get the name space user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name #get the get arguments headers = str(request.args["headers"]).split(",") tableName = s_name ofile = open(_CSV_FILE_PATH, "wb") #write column names first writer = csv.writer(ofile, delimiter=',',quotechar='"', quoting=csv.QUOTE_ALL) writer.writerow(headers) #write the data query = """SELECT * FROM {table}""".format(table = org_namespace_name +'.'+tableName) s = text(query) rs = session.execute(s).fetchall() for item in rs: lis = [str(item[eachColumn]) for eachColumn in headers] writer.writerow(lis) return Response(open(_CSV_FILE_PATH, 'r'),headers = d)
def register(request): c = request.client_session if request.method == 'POST': email = request.form.get('email') email = escape(email) password = request.form.get('password') password = escape(password) confirmpassword = request.form.get('confirmpassword') confirmpassword = escape(confirmpassword) firstname = request.form.get('firstname') firstname = escape(firstname) lastname = request.form.get('lastname') lastname = escape(lastname) companyname = request.form.get('companyname') companyname = escape(companyname) if password == confirmpassword: ## assign organizatio or create new one this_organization_result = session.query(Organization).filter(Organization.organization_name==companyname).all() if len(this_organization_result)>0: this_organization = this_organization_result[0] this_organization_id = this_organization.organization_id else: org_uuid = uuid.uuid1().hex namespace_name = companyname.strip().replace(' ', '') + org_uuid namespace_name = namespace_name.lower() new_organization = Organization(companyname, namespace_name) session.add(new_organization) query = """CREATE SCHEMA {u_namespace_name} """.format(u_namespace_name=namespace_name) s = text(query) session.execute(s) ## create count table in user namespace query = """CREATE TABLE {u_namespace_name}.iqp_problem_count ( Problem_Time integer, Scn_ID integer references IQP_Scenarios(Scn_ID), Problem_Count integer, PRIMARY KEY (Problem_Time, Scn_ID) ) """.format(u_namespace_name=namespace_name) s = text(query) session.execute(s) ## create recent problem count view query = """CREATE VIEW {u_namespace_name}.IQP_Problem_Count_Recent AS SELECT recent.problem_time, fulltable.scn_id, fulltable.problem_count FROM {u_namespace_name}.iqp_problem_count fulltable JOIN ( SELECT MAX(t1.problem_time) as problem_time, t1.scn_id FROM {u_namespace_name}.iqp_problem_count t1 GROUP BY scn_id) recent ON recent.problem_time = fulltable.problem_time AND recent.scn_id = fulltable.scn_id """.format(u_namespace_name=namespace_name) s = text(query) session.execute(s) ## create previous problem count view query = """CREATE VIEW {u_namespace_name}.IQP_Problem_Count_Prev AS SELECT prev.problem_time, fulltable.scn_id, fulltable.problem_count FROM {u_namespace_name}.iqp_problem_count fulltable JOIN ( SELECT MAX(fulltable.problem_time) as problem_time, fulltable.scn_id FROM {u_namespace_name}.iqp_problem_count fulltable JOIN ( SELECT MAX(t1.problem_time) as problem_time, t1.scn_id FROM {u_namespace_name}.iqp_problem_count t1 GROUP BY scn_id) prev ON prev.scn_id = fulltable.scn_id WHERE prev.problem_time > fulltable.problem_time GROUP BY fulltable.scn_id ) prev ON prev.problem_time = fulltable.problem_time AND prev.scn_id = fulltable.scn_id """.format(u_namespace_name=namespace_name) s = text(query) session.execute(s) this_organization = new_organization ## commit changes session.commit() this_organization_id = this_organization.organization_id ## create new user hashedpassword = hashlib.md5(password).hexdigest() new_user = User(email, hashedpassword, '0', firstname, lastname, this_organization_id, '0') session.add(new_user) ## commit changes session.commit() ## set user id in cookie c["user_id"] = new_user.user_id c.modified return redirect(url_for('overview')) return render_template('register.html')
def sub_category_table_data_source(request,sc_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_sub_category = (session.query(SubCategory) .filter(SubCategory.subcategory_name==sc_name) .all()[0]) sc_id = this_sub_category.subcategory_id issue_filter = request.args.get('filter') sortkey = request.args['sortname'] sortDir = request.args['sortorder'] limit = int(request.args['rp']) offset = int((int(request.args['page']) - 1) * limit) data = None if not issue_filter: issue_filter = '' if issue_filter == ('Stats' or 'Features'): query = """SELECT iqp_scenarios.scn_name as name, iqp_scenarios.scn_short_description as description, iqp_problem_count_recent.problem_count as current, iqp_problem_count_recent.problem_time as refreshtime, COALESCE(problem_count_stats.problem_count,999999999) as stats_total, (100*iqp_problem_count_recent.problem_count / COALESCE(problem_count_stats.problem_count,999999999)) as stats_percentage FROM iqp_scenarios LEFT JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_prev ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id) WHERE iqp_scenarios.subcategory_id = '{sc_id}' AND iqp_problem_count_recent.problem_count > 0 AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '') ORDER BY {sortby} {dir} limit {limit} offset {offset} """.format( namespace_name=org_namespace_name, sc_id=sc_id, filter_type=issue_filter, sortby=sortkey, dir=sortDir, limit=limit, offset = offset) s = text(query) rs = session.execute(s).fetchall() data = ([[str(item['name']), str(item['description']), int(item['current']), int(item['refreshtime'])] for item in rs]) else: query = """SELECT iqp_scenarios.scn_name as name, iqp_scenarios.scn_short_description as description, iqp_problem_count_recent.problem_count as current, COALESCE(iqp_problem_count_prev.problem_count, 0) as prev, (iqp_problem_count_recent.problem_count - COALESCE(iqp_problem_count_prev.problem_count, 0)) as trend, iqp_problem_count_recent.problem_time as refreshtime, COALESCE(problem_count_stats.problem_count,999999999) as stats_total, (100*iqp_problem_count_recent.problem_count / COALESCE(problem_count_stats.problem_count,999999999)) as stats_percentage FROM iqp_scenarios LEFT JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_prev ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id) WHERE iqp_scenarios.subcategory_id = '{sc_id}' AND iqp_problem_count_recent.problem_count > 0 AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '') ORDER BY {sortby} {dir} limit {limit} offset {offset}""".format(namespace_name=org_namespace_name, sc_id=sc_id, filter_type=issue_filter, sortby=sortkey, dir=sortDir, limit=limit, offset=offset) s = text(query) rs = session.execute(s).fetchall() data = ([[str(item['name']), str(item['description']), int(item['current']), int(item['stats_total']), float(item['stats_percentage']), int(item['trend']), int(item['refreshtime'])] for item in rs]) countQuery = """SELECT count(*) FROM iqp_scenarios LEFT JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_prev ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id) WHERE iqp_scenarios.subcategory_id = '{sc_id}' AND iqp_problem_count_recent.problem_count > 0 AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '') """.format(namespace_name=org_namespace_name, sc_id=sc_id, filter_type=issue_filter) cs = text(countQuery) rs2 = session.execute(cs).fetchall() jsond = {"total": rs2[0][0], "page": request.args['page'], "rows": []} for row in data: eachRow = {} eachRow["cell"] = row jsond["rows"].append(eachRow) del eachRow result = json.dumps(jsond) return Response(result, mimetype='application/json')
def exportSubcategoryTable(request,sc_name): import csv #Response.headers['Content-Type'] = "application/CSV" #Response.headers['Content-Disposition'] = 'attachment; filename= sample.csv' d = Headers() #write the headers #d.add("Pragma", "public") #d.add("Expires","0") #d.add("Cache-Control", must-revalidate, post-check=0, pre-check=0") #d.add('Content-Type', "application/force-download") #d.add("Content-Type","application/octet-stream") d.add("Content-Type","application/octet-stream") d.add('Content-Disposition', 'attachment;filename=iqpgenerated.csv') headers = ["Scenario","Current Count","Total Count","Percentage of Total","Trend","Last Refreshed"] ofile = open(_CSV_FILE_PATH, "wb") #write column names first writer = csv.writer(ofile, delimiter=',',quotechar='"', quoting=csv.QUOTE_ALL) writer.writerow(headers) user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name this_sub_category = session.query(SubCategory).filter(SubCategory.subcategory_name==sc_name).all()[0] sc_id = this_sub_category.subcategory_id issue_filter = request.args.get('filter') if not issue_filter: issue_filter = '' query = """SELECT iqp_scenarios.scn_name as name, iqp_scenarios.scn_short_description as description, iqp_problem_count_recent.problem_count as current, COALESCE(iqp_problem_count_prev.problem_count, 0) as prev, (iqp_problem_count_recent.problem_count - COALESCE(iqp_problem_count_prev.problem_count, 0)) as trend, iqp_problem_count_recent.problem_time as refreshtime, COALESCE(problem_count_stats.problem_count,999999999) as stats_total, (iqp_problem_count_recent.problem_count / COALESCE(problem_count_stats.problem_count,999999999)) as stats_percentage FROM iqp_scenarios LEFT JOIN {namespace_name}.iqp_problem_count_recent ON (iqp_scenarios.scn_id = iqp_problem_count_recent.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_prev ON (iqp_scenarios.scn_id = iqp_problem_count_prev.scn_id) LEFT JOIN {namespace_name}.iqp_problem_count_recent problem_count_stats ON (iqp_scenarios.scn_totals_scn_id = problem_count_stats.scn_id) WHERE iqp_scenarios.subcategory_id = '{sc_id}' AND iqp_problem_count_recent.problem_count > 0 AND (iqp_scenarios.scn_type NOT IN ('Stats', 'N','Feature') OR '{filter_type}' IN ('Stats', 'N','Feature')) AND (iqp_scenarios.scn_type = '{filter_type}' OR '{filter_type}' = '')""".format( namespace_name=org_namespace_name, sc_id=sc_id, filter_type=issue_filter) s = text(query) rs = session.execute(s).fetchall() data = ([[str(item['description']), int(item['current']), int(item['stats_total']), float(item['stats_percentage']), int(item['trend']), int(item['refreshtime'])] for item in rs]) #tableData = json.loads(request.args['tableData']) #write table data for eachRow in data: writer.writerow(eachRow) return Response( open(_CSV_FILE_PATH, 'r'), headers = d)
def scenario(request, c_name, sc_name, s_name): user = request.client_user_object user_id = user.user_id org = request.client_organization_object org_namespace_name = org.organization_namespace_name args = json.dumps(request.args) categories = (session.query(Category) .join( AppFamilyPermission, Category.category_id == AppFamilyPermission.category_id) .filter(AppFamilyPermission.user_id == user_id) .order_by(Category.category_display_order.asc())) sub_categories = (session.query(SubCategory) .join( AppPermission, SubCategory.subcategory_id == AppPermission.subcategory_id) .filter(AppPermission.user_id == user_id)) breadcrumbs = (session.query(Category, SubCategory, Scenario) .join(SubCategory, Category.category_id == SubCategory.category_id) .join(Scenario, SubCategory.subcategory_id == Scenario.subcategory_id) .filter(Category.category_name==c_name) .filter(SubCategory.subcategory_name==sc_name) .filter(Scenario.scn_name==s_name).all()[0]) scenario = (session.query(Scenario) .filter(Scenario.scn_name==s_name) .all()[0]) scn_short_des = scenario.scn_short_description scenario_id = scenario.scn_id s_name_lower = s_name.lower() groupby = request.args.get('groupby') if groupby: clicks_count_query = ( """select insert_clicks({s_id},'{name}');""" .format(s_id = scenario_id,name = groupby)) count_query = text(clicks_count_query) insert = session.execute(count_query).fetchall() session.commit() query1 = ( """select frequent_column_name from scenario_clicks_count where scn_id = {s_id} order by frequency_number desc limit 5;""".format(s_id = scenario_id)) s1 = text(query1) scenario_data_column_names_ordered1 = session.execute(s1).fetchall() query2 = (""" select column_name from INFORMATION_SCHEMA.COLUMNS where column_name not in ( select frequent_column_name from scenario_clicks_count where scn_id = {s_id} order by frequency_number desc limit 5) and table_name = '{scen_name_lower}' and table_schema = '{namespace_name}' order by column_name;""" .format( namespace_name=org_namespace_name, scen_name_lower=s_name_lower.lower(), s_id=scenario_id)) s2 = text(query2) scenario_data_column_names_ordered2 = session.execute(s2).fetchall() return render_template( 'scenario.html', categories=categories, sub_categories=sub_categories, breadcrumbs=breadcrumbs, scn_des=scn_short_des, scenario_name=s_name, scenario_data_column_names1=scenario_data_column_names_ordered1, scenario_data_column_names2=scenario_data_column_names_ordered2, user=user, args=args)