from server import * @app.route('/sort/log') @register_navbar('Sortierlog', icon='sort-by-attributes-alt') @mod_required def sort_log(): return render_template('sortlog.html',sortlog=query(''' SELECT sortlog.*, lectures.id as lecture_id, lectures.title as lecture_title, lectures.course_id as course_id, courses.title as course_title FROM sortlog JOIN lectures ON lectures.id = sortlog.lecture_id JOIN courses ON courses.id = lectures.course_id ORDER BY sortlog.`when` DESC ''')) @app.route('/sort/now') @mod_required def sort_now(): courses = query('SELECT * FROM courses') formats = query('SELECT * FROM formats ORDER BY prio') for c in courses: existingvideos = query('SELECT videos.path FROM videos JOIN lectures ON (videos.lecture_id = lectures.id) WHERE lectures.course_id = ?',c['id']) lectures = query('SELECT * from lectures where course_id = ?',c['id']) coursepath = config['VIDEOMOUNT']+c['handle'] try: files = os.listdir(coursepath) except FileNotFoundError: files = [] for f in files: # if the video is in the table "videos" already, skip it exists = False for e in existingvideos: # vpnonline/08ws-swt/08ws-swt-081118.mp4 e_filename = e['path'].split('/',2)[1] if f == e_filename: exists = True break if exists: continue filepath = coursepath + '/' + f # filenames: <handle>-<sorter>-<format>.mp4 # sorter musst be found with fuzzy matching. musst be one or more of the following: (inside the loop) splitfilename = f.replace('_','-').replace(' ','-').split('-') if not os.path.splitext(f)[1] == '.mp4': continue data = {'keywords': []} # parse the file name and save all data in 'data' for s in splitfilename: s = s.replace('.mp4','') #-<YYMMDD> (date) #-<HHMM> (time) #-<keyword> # Looking for keywords in: id,title,speaker,comment, comma seperated list in internal try: if len(s) == 6: data['date'] = datetime.strptime(s,'%y%m%d').date() elif len(s) == 4: data['time'] = datetime.strptime(s,'%H%M').time() else: data['keywords'].append(s) except ValueError: data['keywords'].append(s) # try to match the file on a single lecture matches = [] # first try date and time (if one of them is set) if 'date' in data: for l in lectures: if ((l['time'].date() == data['date']) and not ('time' in data)) or (('time' in data) and l['time'] == datetime.combine(data['date'],data['time'])) : matches.append(l) # if we can't match based on date and time, we have to match keywords if ((len(matches) != 1) and (len(data['keywords']) > 0)): found = False for field in ['id','title','speaker','comment','internal']: #only test lectures with the correct date/time if len(matches) == 0: matches.extend(lectures) for l in matches: for k in data['keywords']: # first test for exact match, else make it asci and try substring test if (k == l[field]) or (str(k).lower() in str(l[field]).lower().replace('ä','ae').replace('ü','ue').replace('ö','oe').replace('ß','ss') ): found = True matches = [l] if found: break if found: break if found: break # now we should have found exactly one match if len(matches) == 1: # now match the format for i in formats: #we match the last part of the file name without the extension formatstring = splitfilename[-1].split('.',1)[0].lower() if formatstring in i['keywords'].replace(',',' ').split(' '): data['format'] = i['id'] break # if we found the format, insert the video if not 'format' in data: data['format'] = 0 modify('BEGIN') video_id = modify('INSERT INTO videos_data (lecture_id,visible,path,video_format,title,comment,internal,file_modified,time_created,time_updated,created_by,hash,file_size) VALUES (?,0,?,?,"","","",?,?,?,?,"",?)',matches[0]['id'],c['handle']+'/'+f,data['format'],datetime.now(),datetime.now(),datetime.now(),session['user']['givenName'],os.stat(coursepath+'/'+f).st_size) query('INSERT INTO sortlog (lecture_id,video_id,path,`when`) VALUES (?,?,?,?)',matches[0]['id'],video_id,c['handle']+'/'+f,datetime.now()) modify('COMMIT') # for debuging only # else: # d = [] # for m in matches: # d.append(m['id']) # print('failed',{"data":data,"path":f,"results":d}) if 'ref' in request.values: return redirect(request.values['ref']) else: return 'OK', 200