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']))