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)
