def category(request, c_name):
if c_name == 'favicon.ico':
return Response()
user = request.client_user_object
user_id = user.user_id
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)
.filter(Category.category_name==c_name).all()[0])
return render_template(
'category.html',
categories=categories,
sub_categories=sub_categories,
breadcrumbs=breadcrumbs,
category_name=c_name,
user=user,
args=args)
def setpermissions3(request):
user = request.client_user_object
user_id = user.user_id
if request.method == 'POST':
selectedsubcategory_ids = request.form.getlist('subcategory')
session.query(AppPermission).filter(AppPermission.user_id==user_id).delete()
for selectedsubcategory_id in selectedsubcategory_ids:
selectedsubcategory = session.query(SubCategory).filter(SubCategory.subcategory_id==selectedsubcategory_id)
newapppermission = AppPermission(user_id, selectedsubcategory_id)
session.add(newapppermission)
session.commit()
else:
return redirect(url_for('setpermissions'))
return render_template('setpermissions3.html')
def authorized(oauth_token):
"""
authorized: update user table and login session
Args:
oauth_token (str): access token from oauth server
Returns:
return homepage
"""
state = request.args.get('state')
if state != login_session['state']:
response = make_response(json.dumps('Invalid state parameter.'), 401)
response.headers['Content-Type'] = 'application/json'
return response
login_session['access_token'] = oauth_token
data = github.get('user')
email = data['email']
name = data['name']
user = session.query(User).filter_by(email=email).first()
if not user:
user = User(name=name, email=email)
session.add(user)
user.name = name
session.commit()
login_session['user_id'] = user.id
flash("Logged in as %s!" % name)
return redirect(url_for('show_home'))
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_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 update(cls, id, form):
m = session.query(cls).filter_by(id=id).first()
for name, value in form.items():
setattr(m, name, value)
session.add(m)
session.commit()
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 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 setpermissions2(request):
user = request.client_user_object
user_id = user.user_id
if request.method == 'POST':
selectedcategory_ids = request.form.getlist('category')
session.query(AppFamilyPermission).filter(AppFamilyPermission.user_id==user_id).delete()
for selectedcategory_id in selectedcategory_ids:
selectedcategory = session.query(Category).filter(Category.category_id==selectedcategory_id)
newappfamilypermission = AppFamilyPermission(user_id, selectedcategory_id)
session.add(newappfamilypermission)
session.commit()
categories = session.query(Category).filter(Category.category_id.in_(selectedcategory_ids)).order_by(Category.category_display_order.asc()).all()
subcategories = session.query(SubCategory, Category).join(Category, Category.category_id == SubCategory.category_id).all()
else:
return redirect(url_for('setpermissions'))
return render_template('setpermissions2.html', categories=categories, subcategories=subcategories)
def client_organization_object(self):
if not self.client_user_object:
return None
else:
org_id = self.client_user_object.user_organization_id
org_result = session.query(Organization).filter(Organization.organization_id==org_id).all()
org_object = None
for org in org_result:
org_object = org
return org_object
def wrapper(*args, **kwargs):
request = args[0]
user = request.client_user_object
if not user:
return redirect(url_for('login'))
else:
user_id = user.user_id
subcategory_name = kwargs['sc_name']
subcategoryquery = session.query(SubCategory).filter(SubCategory.subcategory_name==subcategory_name).all()
if len(subcategoryquery) == 0:
return redirect(url_for('overview'))
else:
subcategory_id = subcategoryquery[0].subcategory_id
## check user permission here
apppermisionquery = session.query(AppPermission).filter(AppPermission.user_id==user_id).filter(AppPermission.subcategory_id==subcategory_id).all()
if len(apppermisionquery) == 0:
return redirect(url_for('setpermissions'))
else:
result = fn(*args, **kwargs)
return result
def wrapper(*args, **kwargs):
request = args[0]
user = request.client_user_object
if not user:
return redirect(url_for('login'))
else:
user_id = user.user_id
category_name = kwargs['c_name']
categoryquery = session.query(Category).filter(Category.category_name==category_name).all()
if len(categoryquery) == 0:
return redirect(url_for('overview'))
else:
category_id = categoryquery[0].category_id
## check user permission here
appfamilypermisionquery = session.query(AppFamilyPermission).filter(AppFamilyPermission.user_id==user_id).filter(AppFamilyPermission.category_id==category_id).all()
if len(appfamilypermisionquery) == 0:
return redirect(url_for('setpermissions'))
else:
result = fn(*args, **kwargs)
return result
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 overview(request):
user = request.client_user_object
user_id = user.user_id
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))
return render_template(
'overview.html',
categories=categories,
sub_categories=sub_categories,
user=user,
args=args)
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 get_all(date_object_lower=None, date_object_upper=None):
record_query = session.query(Record)
if date_object_lower:
record_query = record_query.filter(
Record.date_time >= date_object_lower
)
if date_object_upper:
record_query = record_query.filter(
Record.date_time < date_object_upper
)
return record_query.order_by(Record.date_time).all()
def client_user_object(self):
if 'user_id' not in self.client_session:
return None
else:
user_id = self.client_session['user_id']
user_result = session.query(User).filter(User.user_id==user_id).all()
user_object = None
for user in user_result:
user_object = user
if not user_object:
del self.client_session["user_id"]
self.client_session.modified
return None
else:
return user_object
def login(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)
hashedpassword = hashlib.md5(password).hexdigest()
userlogin = session.query(User).filter(User.user_email==email).filter(User.user_password==hashedpassword).all()
loggedinuser = None
for user in userlogin:
loggedinuser = user
if loggedinuser:
c["user_id"] = loggedinuser.user_id
c.modified
return redirect(url_for('overview'))
return render_template('login.html')
def export_table():
data = session.query(
OrderWebsite.id, OrderWebsite.date, OrderWebsite.amount,
OrderAgent.date, OrderAgent.amount,
OrderAgent.source).filter(OrderWebsite.id == OrderAgent.id).all()
df = pd.DataFrame.from_records(data)
df = df.rename(
columns={
0: 'id',
1: 'Website.date',
2: 'Website.amount',
3: 'Agent.date',
4: 'Agent.amount',
5: 'Agent.source',
})
df.to_excel(export_dir)
print(df)
def get_ties(self):
self.driver.get('http://tieba.baidu.com/i/i/my_tie')
tables = self.driver.find_elements_by_tag_name('table')
for table in tables:
bar_tag = table.find_element_by_class_name(
'nowrap').find_element_by_tag_name('a')
bar_name = bar_tag.text[:-1]
bars = session.query(Bar).filter(Bar.name == bar_name).all()
if len(bars) == 0:
bar = Bar(name=bar_name)
else:
bar = bars[0]
tie_tag = table.find_element_by_class_name(
'wrap').find_element_by_tag_name('a')
tie_url = tie_tag.get_attribute('href')
tie = Tie(url=tie_url, bar_id=bar.id, bar=bar)
session.add(tie)
session.commit()
def save(self):
if not self.id:
record_query = session.query(Record)
record_query = record_query.filter(
Record.date_time == self.date_time
)
kk = record_query.one_or_none()
if kk:
return kk
session.add(self)
session.commit()
return self
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 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 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']
rowdata = []
rowCounter = 0
fromRow = 0
toRow = 5
sortByColumn = 0
sortDir = "asc"
if (request.args['iDisplayStart'] and request.args['iDisplayLength'] != '-1' ): # set the from and to
fromRow = int(request.args['iDisplayStart'])
toRow = int(fromRow + int(request.args['iDisplayLength']))
# ordering
if(request.args['iSortCol_0']):
sortByColumn = int(request.args['iSortCol_0'])
if(request.args['sSortDir_0']):
sortDir = str(request.args['sSortDir_0'])
if(request.args['sSearch']):
jsonData = generateTablesWithSearch(request.args['sSearch'],data,sortByColumn,sortDir,columns)
else:
for row in session.query(Scenario.scn_name.label("name"),Scenario.scn_short_description.label("desc"),Scenario.scn_type.label("type")).order_by(columns[sortByColumn])[fromRow:toRow]:
rowdata.append([])
rowdata[rowCounter].append(row.name)
rowdata[rowCounter].append(row.desc)
rowdata[rowCounter].append(row.type)
rowCounter += 1
jsonData = (rowdata,rowCounter)
data["aaData"] = jsonData[0]
data["iTotalRecords"] = jsonData[1]
return json.dumps(data)
def generateTablesWithSearch(search,data,sortByColumn,sortDir,columns):
rowdata = []
rowCounter = 0
fromRow = 0
toRow = 5
aFilter1 = "lower(iqp_scenarios.scn_name) LIKE '%"+search+"%'"
aFilter2 = "lower(iqp_scenarios.scn_short_description) LIKE '%"+search+"%'"
aFilter3 = "lower(iqp_scenarios.scn_type) LIKE '%"+search+"%'"
for row in session.query(Scenario.scn_name.label("name"),Scenario.scn_short_description.label("desc"),Scenario.scn_type.label("type")).filter(or_(aFilter1,aFilter2,aFilter3)).order_by(columns[sortByColumn])[fromRow:toRow]:
rowdata.append([])
rowdata[rowCounter].append(row.name)
rowdata[rowCounter].append(row.desc)
rowdata[rowCounter].append(row.type)
rowCounter += 1
return (rowdata,rowCounter) # here rowCounter will be the no of rows
def reply(self):
ties = session.query(Tie).all()
for tie in ties:
if '高' in tie.bar.name or '中' in tie.bar.name:
self.driver.get(tie.url)
# 将页面滚动条拖到底部
js = "var q=document.documentElement.scrollTop=10000"
self.driver.execute_script(js)
time.sleep(3)
reply_content = get_reply()
# 输入内容
ueditor_replace = self.driver.find_element_by_id(
'ueditor_replace')
self.driver.execute_script(
"arguments[0].innerHTML='<p>{}</p>'".format(reply_content),
ueditor_replace)
time.sleep(3)
# 点击提交
button = self.driver.find_element_by_class_name(
'poster_submit')
button.click()
sleep_time = random.randint(5, 15)
time.sleep(sleep_time)
def delete(cls, id):
m = session.query(cls).filter_by(id=id).first()
session.delete(m)
session.commit()
time.sleep(sleep_time)
if __name__ == '__main__':
# 账号
accounts = [
{
'name': "wanwufusu037",
'pwd': "123456abc"
},
]
# 登陆
baidu = BaiduSpider('wanwufusu037', '123456abc') # 你的百度账号，密码
# 发帖
for bar_obj in session.query(Bar).filter(Bar.hassend == False).all():
barName = bar_obj.name
print(barName)
# post_title = '{}{}'.format(barName, get_title())
# post_content = '{}的同学们，{}'.format(barName, get_content())
# baidu.post(barName, post_title, post_content)
baidu.focus(barName)
# baidu.post(barName,'{}{}'.format(barName,get_title()), content)
# bar_obj.hassend=True
# session.commit()
# 查看已发的帖子
# baidu.get_ties()
# 回复已经发布的帖子
# baidu.reply()
def all(cls, **kwargs):
ms = session.query(cls).filter_by(**kwargs).all()
return ms
def one(cls, **kwargs):
m = session.query(cls).filter_by(**kwargs).first()
return m
def setpermissions(request):
user = request.client_user_object
user_id = user.user_id
categories = session.query(Category).order_by(Category.category_display_order.asc())
return render_template('setpermissions.html', categories=categories)
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 get_scenario_query(request, scn_id): this_scenario = session.query(Scenario).filter(Scenario.scn_id==scn_id).all()[0] #data = [n for n in range(20)] data = [this_scenario.scn_query] result = json.dumps(data) return Response(result, mimetype='application/json')
def find_by_k_and_id(k):
record_query = session.query(Record)\
.filter(Record.k == k).filter(Record.id == id)
return record_query.one_or_none()
def show_items(category):
items = session.query(Item).filter_by(category_id=category.id).order_by(desc(Item.created_at))
return render('showitems.html', category=category, items=items)
def get_all_scenarios(request): all_scenarios = session.query(Scenario).all() #data = [n for n in range(20)] data = [scenario.scn_id for scenario in all_scenarios] result = json.dumps(data) return Response(result, mimetype='application/json')
def show_categories():
categories = session.query(Category).order_by(asc(Category.name))
return render('showcategories.html', categories=categories)
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)
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 find_by_date(date_object):
record_query = session.query(Record)\
.filter(Record.date_time == date_object)
return record_query.one_or_none()
def categories_json():
categories = session.query(Category).order_by(asc(Category.name))
return jsonify(Categories=[i.serialize for i in categories])
def find_by_k(k):
record_query = session.query(Record)\
.filter(Record.k == k)
return record_query.one_or_none()
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)