sorter.py 4.48 KB
Newer Older
Andreas Valder's avatar
Andreas Valder committed
1
2
from server import *

3
4
5
6
7
8
9
10
11
12
13
14
15
16
@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 
17
			ORDER BY sortlog.`when` DESC
Andreas Valder's avatar
Andreas Valder committed
18
			LIMIT 50
19
20
21
22
		'''))


@app.route('/sort/now')
23
@sched_func(60)
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
@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)
52
			splitfilename = f.replace('_','-').replace(' ','-').split('-')
53
54
55
56
57
			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:
58
				s = s.replace('.mp4','')
59
60
61
				#-<YYMMDD> (date)
				#-<HHMM> (time)
				#-<keyword>
62
63
64
				#	Looking for keywords in: id,title,speaker,comment, comma seperated list in internal
				try:
					if len(s) == 6:
65
						data['date'] = datetime.strptime(s,'%y%m%d').date()
66
					elif  len(s) == 4:
67
						data['time'] = datetime.strptime(s,'%H%M').time()
68
69
70
					else:	
						data['keywords'].append(s)
				except ValueError:
71
72
73
74
75
76
77
					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:
78
					if ((l['time'].date() == data['date']) and not ('time' in data)) or (('time' in data) and l['time'] == datetime.combine(data['date'],data['time'])) :
79
80
81
82
83
84
						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
85
86
					if len(matches) == 0:
						matches.extend(lectures)
87
88
					for l in matches:
						for k in data['keywords']:
89
90
							# 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') ):
91
92
93
94
95
96
97
98
99
100
101
102
103
								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
104
105
					formatstring = splitfilename[-1].split('.',1)[0].lower()
					if formatstring in i['keywords'].replace(',',' ').split(' '):
106
107
108
						data['format'] = i['id']
						break
				# if we found the format, insert the video
109
110
111
112
113
114
115
116
117
118
119
120
				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})
121
122
123
124
125
126

		
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	else:
		return 'OK',  200
Andreas Valder's avatar
Andreas Valder committed
127