def login():
user = get_current_user()
form = LoginForm()
if request.method == 'POST':
db = get_db()
if form.validate_on_submit():
user_email = str(form.email.data).lower()
password = form.password.data
db.execute(
'select user_id, email, password, can_scout, can_analyse, can_edit, admin from users where email = %s',
(user_email, ))
user_result = db.fetchone()
if user_result:
if check_password_hash(user_result['password'], password):
session['user'] = user_result['email']
flash('You have been logged in!', 'success')
return redirect(url_for('home'))
else:
flash('Login Unsuccessful. Please check username and password',
'danger')
return render_template('login.html', title='Login', user=user, form=form)
def view_all_stories(tech):
db = get_db()
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access. Please contact administrators', 'danger')
return redirect(url_for('home'))
db.execute(
'''
select
tsl.log_s_id log_s_id,
tsl.tech_name,
tsl.milestone,
substr(tsl.story_content, 1, 100)|| '...' story_content, tsl.story_year,
tsl.contributor contributor_id,
tsl.change_committed,
u.username contributor_name, tsl.contribute_time contribute_time
from tech_story_log tsl, users u, milestones m
where tsl.tech_name = %s
and tsl.contributor = u.user_id
and tsl.milestone = m.ms_name
order by tsl.story_year asc, m.milestone_id
''', (tech, ))
stories_results = db.fetchall()
return render_template('view_all_stories.html',
title='View Progress',
user=user,
stories=stories_results,
tech=tech)
def view_scout(log_id, mode='analytics'):
user = get_current_user()
if not user:
return redirect(url_for('login'))
db = get_db()
db.execute(
'''
select *, scout_time scout_time_dt
from tech_main_log join users
on contributor = user_id
where log_id = %s
''', (log_id, ))
scout = db.fetchone()
b = tuple(map(int, scout['impa_sector'].split(';'))) if len(
scout['impa_sector']) > 1 else '(' + str(scout['impa_sector']) + ')'
sectors_sql = 'select sector from impacted_sector_order where sec_id in ' + str(
b)
db.execute(sectors_sql)
sectors = db.fetchall()
return render_template('view_scout.html',
title='View Tech Scout',
user=user,
scout=scout,
sectors=sectors,
mode=mode)
def task_board_a():
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_analyse'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
############ get all tech scout outcomes that needs analysis ##############
## NOTE: Here we query the tech_main_log table #######
db = get_db()
## sqlite3 specific datetime function!!
db.execute('''select
log_id,
tech_name,
to_char(tml.scout_time, 'YYYY-MM-DD HH24:MI') scout_time,
username contributor_name
from (tech_main_log tml join users u on tml.contributor = u.user_id)
where tech_name in
(select distinct tech_name from tech_main_log)
order by lower(tech_name) asc''')
task_result = db.fetchall()
return render_template('task_board_a.html',
title='Task Board',
user=user,
tasks=task_result)
def view_log(log_s_id, mode='analytics'):
"""
For all users, see a form submitted by him/herself or other users. Users can only edit his/her own form and rewrite the corresponding entry in log table.
"""
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_analyse'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
db = get_db()
db.execute(
'''
select *, to_char(contribute_time,'YYYY-MM-DD HH24:MI') contribute_time_dt
from tech_story_log tsl join users
on contributor = user_id
where log_s_id = %s
''', (log_s_id, ))
story = db.fetchone()
return render_template('view_log.html',
title='View Tech Story',
user=user,
story=story,
mode=mode)
def repeat_scout_checker(form, mode = 'scout_only'):
db = get_db()
# check repeat form
if mode == 'scout_only':
db.execute('''
select log_id
from tech_main_log
where tech_name = %s
''', (form.tech_name.data, ))
existing_sc = db.fetchone()
elif mode == 'full_check':
db.execute('''
select log_id
from tech_main_log
where tech_name = %s
and description = %s
and impact = %s
and asso_names = %s
and emb_techs = %s
and category = %s
and desc_source = %s
''', (form.tech_name.data, form.description.data, form.impact.data, form.associate_names.data, form.embed_tech.data, form.category.data, form.sources.data))
existing_sc = db.fetchone()
return existing_sc
def edit_log(log_s_id):
"""
For all users, see a form submitted by him/herself or other users. Users can only edit his/her own form and rewrite the corresponding entry in log table.
"""
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_analyse'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
# get the log that will be edited
db = get_db()
story_cur = db.execute(
'''
select *, datetime(contribute_time,'unixepoch') contribute_time_dt
from tech_story_log tsl join users
on contributor = user_id
where log_s_id = ?
''', [log_s_id])
story = story_cur.fetchone()
tech = story['tech_name']
################### add in original input
form = EditTechStoryForm()
form.milestone.choices = milestones_tuplist
form.milestone.data = story[7]
form.story_year.data = story[4]
form.story_date.data = story[5]
form.story_content.data = story[6]
form.sources.data = story[8]
################## add in original input
if request.method == 'POST':
if form.validate_on_submit():
if repeat_story_checker(form):
flash(f'Edit field: please do not submit repeated content',
'danger')
else:
# commit changes
db.execute(
'''update tech_story_log
set contributor = ?, contribute_time = ?, story_year = ?, story_date = ?, story_content = ?, milestone = ?, sources = ?
where log_s_id = ?''',
(user['user_id'], datetime.timestamp(
datetime.now()), form.story_year.data,
form.story_date.data, form.story_content.data,
form.milestone.data, form.sources.data, log_s_id))
# we might need some cleaner functions here
db.commit()
flash(f'Tech Story Updated', 'success')
return redirect(url_for('tech_analytics', tech=tech))
return render_template('edit_log.html',
title='View Tech Story',
form=form,
user=user,
story=story)
def get_current_user():
user_result = None
if 'user' in session:
user_email = session['user']
db = get_db()
db.execute('select user_id, email, password, can_scout, can_analyse, can_edit, admin from users where email = %s', (user_email, )) # tuple for postgres
user_result = db.fetchone()
return user_result
def get_current_user():
user_result = None
if 'user' in session:
user_email = session['user']
db = get_db()
user_cur = db.execute(
'select user_id, email, password, can_scout, can_analyse, can_edit, admin from users where email = ?',
[user_email])
user_result = user_cur.fetchone()
return user_result
def tech_scout():
db = get_db()
######################### test only, link this part to the database later ###########################
# sectors = ['Land Selection ', 'Design ' ,'Entitlement ', 'Capital Stack ', 'Construction ','Leasing & Brokerage ','Asset Monitoring & Operations ','Acquisition & Disposition ','Demolition ','Redevelopment ']
#####################################################################################################
sectors_cur = db.execute(
'select sec_id, sector from impacted_sector_order')
sectors_results = sectors_cur.fetchall()
form = TechScoutForm()
if request.method == 'POST':
trigger = True
checkbox = request.form.getlist('mycheckbox')
if len(checkbox) == 0:
trigger = False
print(checkbox)
if form.validate() and trigger == True:
print('yea')
flash(f'Technology {form.tech_name.data} added', 'success')
db.execute(
'insert into tech_main_log (contributor, tech_name, scout_time, description, impact, desc_source, asso_names, impa_sector, emb_techs) values (?, ?, ?, ?, ?, ?, ?, ?, ?)',
[
current_user, form.tech_name.data,
datetime.timestamp(datetime.now()), form.description.data,
form.impact.data, form.sources.data,
form.associate_names.data, ';'.join(checkbox),
form.embed_tech.data
])
# we might need some cleaner functions here
db.commit()
return redirect(url_for('home'))
else:
flash(form.errors
if len(form.errors) != 0 else 'select Impact Sectors',
'danger') #spits out any and all errors**
# if form.validate_on_submit():
# print('yea')
# flash(f'Technology {form.tech_name.data} added')
# return redirect(url_for('home'))
# else:
return render_template('tech_scout.html',
title='Technology Scout',
form=form,
sectors=sectors_results)
def repeat_story_checker(form):
db = get_db()
# check repeat form
db.execute('''
select log_s_id
from tech_story_log
where story_year = %s
and story_date = %s
and milestone = %s
and story_content = %s
and sources = %s
''', (form.story_year.data, form.story_date.data, form.milestone.data, form.story_content.data, form.sources.data))
existing_ms = db.fetchone()
return existing_ms
def admin():
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['admin'] == 0:
flash('You are not an administrator', 'danger')
return redirect(url_for('home'))
db = get_db()
db.execute(
'select user_id, username, email, can_scout, can_analyse, can_edit, admin from users'
)
users_results = db.fetchall()
return render_template('admin.html', user=user, users=users_results)
def repeat_checker(form, table_name):
"""
This function requires that the field naming should be consistent in all tables and forms
"""
db = get_db()
form_data = [field.data for field in form]
sql_where = 'where'
for field in form[:-1]:
sql_where += str(field.name) + ' = %s and '
sql_where += str(form[-1].name) + ' = %s '
db.execute('select * from' + str(table_name) + sql_where, form_data)
existing = db.fetchone()
return existing
def repeat_story_checker(form):
db = get_db()
# check repeat form
existing_ms_cur = db.execute(
'''
select log_s_id
from tech_story_log
where story_year = ?
and story_date = ?
and milestone = ?
and story_content = ?
and sources = ?
''', [
form.story_year.data, form.story_date.data, form.milestone.data,
form.story_content.data, form.sources.data
])
existing_ms = existing_ms_cur.fetchone()
return existing_ms
def task_board_e():
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
# get all tech outcomes and their status
## NOTE: Here we query the tech_main_log table #######
db = get_db()
## sqlite3 specific datetime function!!
db.execute('''
select
tml.log_id log_id,
tml.tech_name tech_name,
tml.scout_count scout_count,
to_char(tml.latest_scout,'YYYY-MM-DD HH24:MI') latest_scout,
tml.change_committed change_committed,
tsl.story_count story_count,
to_char(tsl.latest_commit,'YYYY-MM-DD HH24:MI') latest_commit
from
(select distinct tech_name, max(log_id) log_id, count(*) scout_count, max(change_committed) change_committed,
max(scout_time) latest_scout
from tech_main_log
group by tech_name) tml left join
(select distinct tech_name, count(*) story_count, max(contribute_time) latest_commit
from tech_story_log
group by tech_name) tsl
on tml.tech_name = tsl.tech_name
order by lower(tml.tech_name) asc
''')
task_result = db.fetchall()
return render_template('task_board_e.html',
title='Task Board',
user=user,
tasks=task_result)
def register():
user = get_current_user()
form = RegistrationForm()
if request.method == 'POST':
db = get_db()
if form.validate_on_submit():
username = form.username.data
user_email = str(form.email.data).lower()
hashed_password = generate_password_hash(form.password.data,
method='sha256')
existing_user_cur = db.execute(
'select user_id from users where email = ? or username = ?',
[user_email, username])
existing_user = existing_user_cur.fetchone()
if existing_user:
flash('User email already exist', 'danger')
else:
db.execute(
'insert into users (username, email, password) values (?, ?, ?)',
[username, user_email, hashed_password])
db.commit()
flash('Account created', 'success')
session['user'] = form.email.data
return redirect(url_for('home'))
else:
flash('Invalid email or password')
return render_template('register.html',
title='Register',
form=form,
user=user)
def question():
form = ConfirmForm()
form.properties_1.data = session.get('properties_1')
form.properties_2.data = session.get('properties_2')
form.properties_3.data = session.get('properties_3')
if request.method == "POST":
if form.validate_on_submit():
db = get_db()
user_id = session.get('user_id')
gender = session.get('gender')
age = session.get('age')
address = session.get('address')
background = session.get('background')
properties_1 = form.properties_1.data
properties_2 = form.properties_2.data
properties_3 = form.properties_3.data
wechat = form.wechat.data
db.execute("""insert into user_main_after(user_id, gender, age, address,background,properties_1,
properties_2,properties_3,wechat) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)""",(user_id, gender, age,
address, background, properties_1, properties_2, properties_3,wechat))
return redirect(url_for('ending'))
return render_template('question.html',form=form)
def edit_scout(log_id):
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
##### get the scout that will be edited ######################################
db = get_db()
db.execute(
'''
select *, scout_time scout_time_dt
from tech_main_log join users
on contributor = user_id
where log_id = %s
''', (log_id, ))
scout = db.fetchone()
b = tuple(map(int, scout['impa_sector'].split(';'))) if len(
scout['impa_sector']) > 1 else '(' + str(scout['impa_sector']) + ')'
sectors_sql = 'select sector from impacted_sector_order where sec_id in ' + str(
b)
db.execute(sectors_sql)
current_sectors = db.fetchall()
db.execute('select sec_id, sector from impacted_sector_order')
all_sectors = db.fetchall()
##########################################################################
form = TechScoutForm()
if request.method == 'POST':
trigger = True
checkbox = request.form.getlist('mycheckbox')
if len(checkbox) == 0:
trigger = False #
if form.validate_on_submit():
if repeat_scout_checker(form, mode='full_check'):
flash(f'Edit field: please do not submit repeated content',
'danger')
else:
# commit changes
db.execute(
'''
update tech_main_log
set contributor = %s, tech_name = %s, scout_time = %s, description = %s, impact = %s, desc_source = %s, asso_names = %s, impa_sector = %s, emb_techs = %s, wiki_link = %s, category = %s
where log_id = %s
''',
(user['user_id'], form.tech_name.data,
datetime.now(default_tz), form.description.data,
form.impact.data, form.sources.data,
form.associate_names.data, ';'.join(checkbox),
form.embed_tech.data, form.wikilink.data,
form.category.data, log_id))
# we might need some cleaner functions here
flash(f'Tech Scout Updated', 'success')
return redirect(url_for('view_scout', log_id=log_id))
else:
flash(
form.errors if len(form.errors) != 0 else
'Select Impact Sectors', 'danger')
return render_template('edit_scout.html',
title='Edit Tech Scout',
form=form,
user=user,
scout=scout,
sectors=all_sectors,
selected_sec=current_sectors)
def edit_scout(log_id):
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
##### get the scout that will be edited ######################################
db = get_db()
scout_cur = db.execute(
'''
select *, datetime(scout_time,'unixepoch') scout_time_dt
from tech_main_log join users
on contributor = user_id
where log_id = ?
''', [log_id])
scout = scout_cur.fetchone()
b = tuple(map(int, scout['impa_sector'].split(';'))) if len(
scout['impa_sector']) > 1 else '(' + str(scout['impa_sector']) + ')'
sectors_sql = 'select sector from impacted_sector_order where sec_id in ' + str(
b)
sectors_cur = db.execute(sectors_sql)
current_sectors = sectors_cur.fetchall()
sectors_cur = db.execute(
'select sec_id, sector from impacted_sector_order')
all_sectors = sectors_cur.fetchall()
##########################################################################
############### add in original content ###########
# print('scout index', list(enumerate(scout))) # get the index from terminal...
print([i for i in all_sectors[0]])
print('cur', [i for i in current_sectors[0]])
form = TechScoutForm()
form.associate_names.data = scout[9]
form.category.data = scout[13]
form.description.data = scout[4]
form.impact.data = scout[6]
form.embed_tech.data = scout[10]
form.sources.data = scout[5]
form.wikilink.data = scout[12]
# tech_name,associate_names,category,description,impact,embed_tech,sources,wikilink,submit
############### add in original content ###########
if request.method == 'POST':
trigger = True
checkbox = request.form.getlist('mycheckbox')
if len(checkbox) == 0:
trigger = False #
if form.validate_on_submit():
if repeat_scout_checker(form, mode='full_check'):
flash(f'Edit field: please do not submit repeated content',
'danger')
else:
# commit changes
db.execute(
'''
update tech_main_log
set contributor = ?, tech_name = ?, scout_time = ?, description = ?, impact = ?, desc_source = ?, asso_names = ?, impa_sector = ?, emb_techs = ?, wiki_link = ?, category = ?
where log_id = ?
''',
(user['user_id'], form.tech_name.data,
datetime.timestamp(datetime.now()), form.description.data,
form.impact.data, form.sources.data,
form.associate_names.data, ';'.join(checkbox),
form.embed_tech.data, form.wikilink.data,
form.category.data, log_id))
# we might need some cleaner functions here
db.commit()
flash(f'Tech Scout Updated', 'success')
return redirect(url_for('view_scout', log_id=log_id))
else:
flash(
form.errors if len(form.errors) != 0 else
'Select Impact Sectors', 'danger')
return render_template('edit_scout.html',
title='Edit Tech Scout',
form=form,
user=user,
scout=scout,
sectors=all_sectors,
selected_sec=current_sectors)
def tech_scout():
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_scout'] == 0:
flash('No access to TechScout. Please contact administrators',
'danger')
return redirect(url_for('home'))
######################### test only, link this part to the database later ###########################
# sectors = ['Land Selection ', 'Design ' ,'Entitlement ', 'Capital Stack ', 'Construction ','Leasing & Brokerage ','Asset Monitoring & Operations ','Acquisition & Disposition ','Demolition ','Redevelopment ']
#####################################################################################################
db = get_db()
db.execute('select sec_id, sector from impacted_sector_order')
sectors_results = db.fetchall()
form = TechScoutForm()
tech_name = None
if request.method == 'POST':
trigger = True
checkbox = request.form.getlist('mycheckbox')
if len(checkbox) == 0:
trigger = False #%s
if form.validate_on_submit() and trigger:
if repeat_scout_checker(form):
flash(
f'Scout field: The Technology {form.tech_name.data} already exists. Please check.',
'danger')
else:
db.execute(
'''
insert into tech_main_log
(contributor, tech_name, scout_time, description, impact, desc_source, asso_names, impa_sector, emb_techs, wiki_link, category)
values
(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
''', (user['user_id'], form.tech_name.data,
datetime.now(default_tz),
form.description.data, form.impact.data,
form.sources.data, form.associate_names.data,
';'.join(checkbox), form.embed_tech.data,
form.wikilink.data, form.category.data))
# we might need some cleaner functions here
flash(f'Technology {form.tech_name.data} added', 'success')
return redirect(url_for('home'))
else:
flash(form.errors
if len(form.errors) != 0 else 'Select Impact Sectors',
'danger') #spits out any and all errors**
# if form.validate_on_submit():
# print('yea')
# flash(f'Technology {form.tech_name.data} added')
# return redirect(url_for('home'))
# else:
return render_template('tech_scout.html',
title='Technology Scout',
user=user,
form=form,
sectors=sectors_results,
tech_name=tech_name)
def tech_analytics(tech):
db = get_db()
# check user
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_analyse'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
# check & show progress
# get all finished milestones
################## TEST ONLY ########################
# use tech_story in the future
# sqlite3 specific functions: substr() for substring(), || for concat()
db.execute(
'''
select
log_s_id,
milestone,
substr(story_content, 1, 100)|| '...' story_content,
story_year,
contributor contributor_id,
username contributor_name,
to_char(contribute_time, 'YYYY-MM-DD HH24:MI') contribute_time
from tech_story_log inner join milestones on milestone = ms_name
inner join users on contributor = user_id
where tech_name = %s
order by story_year, milestone_id asc
''', (tech, ))
stories_results = db.fetchall()
db.execute(
'''
select ms_left.ms_name ms_name, ms.milestone_id ms_id
from milestones ms,
(select distinct m.ms_name
from milestones m
except
select distinct milestone ms
from tech_story_log
where tech_name = %s) ms_left
where ms_left.ms_name = ms.ms_name
order by ms.milestone_id
''', (tech, ))
ms_left = db.fetchall()
ms_left_l = [row[0] for row in ms_left]
#####################################################
form = TechAnalyticsForm()
form.milestone.choices = milestones_tuplist
if request.method == 'POST':
# form process
# dynamic selection
# form.milestone.choices = [('None', 'None')] + [(str(row[0]), str(row[0])) for row in ms_left]
if form.validate_on_submit():
# check repeat form
db.execute(
'''
select log_s_id
from tech_story_log
where story_content = %s
and contributor = %s
''',
(form.story_content.data, user['user_id']))
existing_ms = db.fetchone()
if existing_ms:
flash('You have already submitted this story', 'danger')
else:
# commit changes
db.execute(
'''insert into tech_story_log
(contributor, tech_name, contribute_time, story_year, story_date, story_content, milestone, sources)
values (%s, %s, %s, %s, %s, %s, %s, %s)''',
(user['user_id'], tech, datetime.now(default_tz),
form.story_year.data, form.story_date.data,
form.story_content.data, form.milestone.data,
form.sources.data))
# we might need some cleaner functions here
flash(f'New story added', 'success')
return redirect(url_for('tech_analytics', tech=tech))
return render_template('tech_analytics.html',
title='Technology Analytics',
form=form,
user=user,
milestones=milestones[1:],
ms_left=ms_left_l,
stories=stories_results,
tech=tech)
def tech_analytics(tech):
db = get_db()
# check user
user = get_current_user()
if not user:
return redirect(url_for('login'))
if user['can_analyse'] == 0:
flash('No access to TechAnalytics. Please contact administrators',
'danger')
return redirect(url_for('home'))
# check & show progress
# get all finished milestones
################## TEST ONLY ########################
# use tech_story in the future
# sqlite3 specific functions: substr() for substring(), || for concat()
stories_cur = db.execute(
'''
select
tsl.log_s_id log_s_id,
tsl.milestone,
substr(tsl.story_content, 1, 100)|| '...' story_content, tsl.story_year,
tsl.contributor contributor_id,
u.username contributor_name, datetime(tsl.contribute_time,'unixepoch') contribute_time
from tech_story_log tsl, users u, milestones m
where tsl.tech_name = ?
and tsl.contributor = u.user_id
and tsl.milestone = m.ms_name
order by tsl.story_year asc, m.milestone_id
''', [tech])
stories_results = stories_cur.fetchall()
ms_cur = db.execute(
'''
select ms_left.ms_name ms_name, ms.milestone_id ms_id
from milestones ms,
(select distinct m.ms_name
from milestones m
except
select distinct milestone ms
from tech_story_log
where tech_name = ?) ms_left
where ms_left.ms_name = ms.ms_name
order by ms.milestone_id
''', [tech])
ms_left = ms_cur.fetchall()
ms_left_l = [row[0] for row in ms_left]
#####################################################
form = TechAnalyticsForm()
form.milestone.choices = milestones_tuplist
if request.method == 'POST':
# form process
# dynamic selection
# form.milestone.choices = [('None', 'None')] + [(str(row[0]), str(row[0])) for row in ms_left]
if form.validate_on_submit():
# check repeat form
existing_ms_cur = db.execute(
'''
select log_s_id
from tech_story_log
where story_content = ?
and contributor = ?
''',
[form.story_content.data, user['user_id']])
existing_ms = existing_ms_cur.fetchone()
if existing_ms:
flash('You have already submitted this story', 'danger')
else:
# commit changes
db.execute(
'''insert into tech_story_log
(contributor, tech_name, contribute_time, story_year, story_date, story_content, milestone, sources)
values (?, ?, ?, ?, ?, ?, ?, ?)''', [
user['user_id'], tech,
datetime.timestamp(
datetime.now()), form.story_year.data,
form.story_date.data, form.story_content.data,
form.milestone.data, form.sources.data
])
# we might need some cleaner functions here
db.commit()
flash(f'New story added', 'success')
return redirect(url_for('tech_analytics', tech=tech))
return render_template('tech_analytics.html',
title='Technology Analytics',
form=form,
user=user,
milestones=milestones[:-1],
ms_left=ms_left_l,
stories=stories_results,
tech=tech)
def videos():
if request.method =='GET':
if 'idx' not in session:
print('here')
return redirect(url_for('homepage'))
def _helper(data):
# empty string may cause problem in db
if data == '':
return 'NA'
else:
return data
db = get_db()
# get video uuid, video_name, filepath from db
db.execute('select uuid, video_name, trajectory, video_server, trajectory_server from video_clip_meta_2')
video = db.fetchall()
index_l = random.randint(0, len(video) - 1)
index_r = random.randint(0, len(video) - 1)
while index_r == index_l:
index_r = random.randint(0, len(video) - 1)
video_l = video[index_l][1]
video_r = video[index_r][1]
video_l_dir = video[index_l][3] ## this is the video_path I need to pass in for videos.html
video_r_dir = video[index_r][3]
# video_l_dir = './static/VID_20190603_135848_00_014_347_402.mp4'
# video_r_dir = './static/VID_20190603_140440_00_015_618_634.mp4'
session['video_l_dir'] = video_l_dir
session['video_r_dir'] = video_r_dir
session['video_l_name'] = video_l
session['video_r_name'] = video_r # name used to choose html
session['trajectory_filepath_l'] = video[index_l][4]
session['trajectory_filepath_r'] = video[index_r][4]
form = PropertyForm()
if request.method == "POST":
session['idx'] = session.get('idx') + 1
# select left arrow
left_text = form.text_left.data
right_text = form.text_right.data
left_text_video = form.text_left_video.data
right_text_video = form.text_right_video.data
if left_text =='*' and right_text =='*': # read in the selection of model
volume_selection = 'equal'
elif left_text == '' and right_text == '*':
volume_selection = 'right'
elif left_text == '*' and right_text =='':
volume_selection = 'left'
else:
volume_selection = 'bug'
if left_text_video =='*' and right_text_video =='*': # read in the selection of video
selection = 'equal'
elif left_text_video == '' and right_text_video == '*':
selection = 'right'
elif left_text_video == '*' and right_text_video =='':
selection = 'left'
else:
selection = 'bug'
print('let_text',left_text,'right_text',right_text,"left_text_video",left_text_video,"right_text_video",right_text_video)
user_id = session.get('user_id')
gender = session.get('gender')
age = session.get('age')
address = session.get('address')
background = session.get('background')
db.execute("""insert into data_collection_2(user_id, gender, age, address, background,
video_l_dir, video_r_dir,
selection, volume_selection) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
[user_id, gender, age, address, background, video_l, video_r, selection, volume_selection])
if session['idx'] >= 12:
return redirect(url_for('question'))
# return render_template('videos.html',form = form)
else:
return redirect(url_for('videos'))
return render_template('videos.html', form = form, index = session['idx'])
def commit_scout(log_id):
"""
For editor users, commit a tech story to main table.
"""
user = get_current_user()
db = get_db()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access. Please contact administrators', 'danger')
return redirect(url_for('home'))
db.execute('select * from tech_main_log where log_id = %s', [log_id])
log = db.fetchone()
is_use = True if log['category'] == 'use' else False
is_prod = True if log['category'] == 'product' else False
is_proc = True if log['category'] == 'process' else False
########### commit changes to main tables ######################
db.execute('select * from tech_main where name = %s', [log['tech_name']])
tech_main_result = db.fetchone()
if tech_main_result:
db.execute(
'''
update tech_main
set name = %s,
description = %s,
impact = %s,
impa_sector = %s,
is_use = %s,
is_prod = %s,
is_proc = %s
''', (log['tech_name'], log['description'], log['impact'],
log['impa_sector'], is_use, is_prod, is_proc))
########### commit to other related tables #####################
# embedded techs
db.execute(
'''
update tech_embed
set embed_li = %s
where id = %s
''', (log['emb_techs'], tech_main_result['id']))
else: # if this is a new tech
db.execute(
'''
insert into tech_main
(name, description, impact, impa_sector, is_use, is_prod, is_proc)
values
(%s, %s, %s, %s, %s, %s, %s)
''', (log['tech_name'], log['description'], log['impact'],
log['impa_sector'], is_use, is_prod, is_proc))
db.execute('select * from tech_main where name = %s',
[log['tech_name']])
tech_main_result = db.fetchone()
########### commit to other related tables #####################
# embedded techs
db.execute(
'''
insert into tech_embed
(id, embed_li)
values
(%s, %s)
''', (tech_main_result['id'], log['emb_techs']))
# associated names to the lookup table
for name in list(map(lambda x: x.strip(), log['asso_names'].split(';'))):
db.execute(
'''
insert into tech_lookup
(tech_main_id, tech_lookup_name)
values
(%s, %s)
''', (tech_main_result['id'], name))
########### change the commit status #############################
db.execute(
'''
update tech_main_log
set change_committed = %s
where log_id = %s
''', (datetime.now(default_tz), log_id))
flash('Congrats! New technology scout committed to the main database.',
'success')
return redirect(url_for('home'))
def commit_story(log_s_id):
"""
For editor users, commit a tech story to main table.
"""
user = get_current_user()
db = get_db()
if not user:
return redirect(url_for('login'))
if user['can_edit'] == 0:
flash('No access. Please contact administrators', 'danger')
return redirect(url_for('home'))
# get log info
db.execute('select distinct * from tech_story_log where log_s_id = %s',
(log_s_id, ))
log = db.fetchone()
# check if tech exists
db.execute(
'''
select * from tech_main
where name = %s
''', (log['tech_name'], ))
tech = db.fetchone()
if not tech: # if the tech does not exist, commit the tech first
flash('Action not allowed. Please commit the technology scout firstly',
'danger')
return redirect(url_for('task_board_e'))
tech_id = tech['id']
# check if the milestone exists
db.execute(
'''
select * from tech_story
where name = %s
and story_content = %s
and milestone = %s
and story_year = %s
''', (log['tech_name'], log['story_content'],
log['milestone'], log['story_year']))
existing_story = db.fetchone()
if existing_story:
flash('The same story already exists', 'danger')
return redirect(url_for('view_log', log_s_id=log['log_s_id']))
########### prepare the variables #############################
if log['story_date']:
story_time = datetime.strptime(
str(log['story_year']) + '/' + log['story_date'], '%Y/%m/%d')
exact_time = 1
else:
story_time = random_date(log['story_year'])
exact_time = 0
db.execute(
'''
insert into tech_story
(id, name, story_time, story_content, milestone, exact_time, source_check, sources, story_year)
values
(%s,%s,%s,%s,%s,%s,%s,%s,%s)
''',
(tech_id, log['tech_name'], story_time, log['story_content'],
log['milestone'], exact_time, 1, log['sources'], log['story_year']))
########### commit to other related tables #####################
# if there are any
########### change the commit status ###########################
db.execute(
'''
update tech_story_log
set change_committed = %s
where log_s_id = %s
''', (datetime.now(default_tz), log_s_id))
flash('Congrats! New technology story committed to the main database.',
'success')
return redirect(url_for('view_all_stories', tech=log['tech_name']))
