· 9 years ago · Sep 21, 2017, 02:46 PM
1require 'mysql2'
2require 'bcrypt'
3
4# Stable 2
5# RB-file 2/2
6# Datenbankschnittstelle
7# ruby 1.9.3p484
8# v1.7.2s Final
9
10
11##
12# Wandelt einen Zeitwert aus UTC in Float (ganzzahlig um)
13# Parameter: eingabe (Time.utc)
14# Rueckgabewert(e): res (float)
15
16def ConvertTimeF(eingabe)
17 #00:00:07.111
18 if eingabe.eql?("NaN") then
19 return 0.0
20 end
21 if eingabe.size == (eingabe.count(".") + eingabe.count("0")+eingabe.count(":") ) then
22 return 0.0
23 else
24 h = ((eingabe[0..1]).to_f)*3600
25 m = ((eingabe[3..4]).to_f)*60
26 s = (eingabe[6..7]).to_f
27 res = 0.0
28 hts = (eingabe[eingabe.index(".")...eingabe.size]).to_f
29 res = h+m+s+hts
30 #res=((eingabe[1].to_f+eingabe[0].to_f*10)*3600)+((eingabe[3].to_f*10+eingabe[4].to_f)*60)+(eingabe[6].to_f*10+eingabe[7].to_f)+(eingabe[9].to_f/10+eingabe[10].to_f/100)
31 puts "Test 1 #{res}"
32 puts "Test 2 #{((eingabe[1].to_f+eingabe[0].to_f*10)*3600)+((eingabe[3].to_f*10+eingabe[4].to_f)*60)+(eingabe[6].to_f*10+eingabe[7].to_f)+(eingabe[9].to_f/10+eingabe[10].to_f/100)}"
33 return res
34 end
35end
36
37
38##
39# Wandelt einen Float Wert in hh:mm:s um
40# Methode wird fuer anpassen der Metadaten in der Playermaske genutzt
41# Parameter: eingabe (float)
42# Rueckgabewert(e): res (String als UTC Form)
43
44def ConvertTimeT(eingabe)
45 eingabe=eingabe.to_f
46 h=eingabe.to_i/3600
47 m=(eingabe.to_i-h*3600)/60
48 s=(eingabe-m*60).round(3)
49 if h<10 then h="0#{h}"end
50 if m<10 then m="0#{m}"end
51 if s<10 then s="0#{s}"end
52 return"#{h}:#{m}:#{s}"
53end
54
55
56##
57# Methode laed Einstellungsoptionen aus Dateien im Verzeichnis
58# Datenbank Zugriff wird ueber diese Methode ermoeglicht
59# Parameter: reg (Regexp) , confic_name (string)
60# Rueckgabewert(e): Array (string)/nil
61
62def load_config(reg,config_name)
63 con = Array.new
64 if File.exist?("info.cfg") then
65 File.open('info.cfg','r').readlines.each do |e|
66 if ((reg =~e)!=nil ) then con << e[((/'/ =~e).to_i+1)..(e.rindex('\'').to_i) -1]end
67 end
68 puts "[#{Time.now}] | Lade #{config_name}-config (info.cfg)"
69 return con
70 else
71 puts "[#{Time.now}] | [ERROR] Config-file fehlt im Ordner(info.cfg)"
72 return nil
73 end
74end
75
76
77db_con = load_config(/db_.*/,'DB')
78@@package_con = (load_config(/package_.*/,'PACKAGE')[0]).to_i
79@@checknumber = (load_config(/check_.*/,'CHECKNUM')[0]).to_i
80lied_id = 0
81
82
83##
84# Diese globale Variable ist die Schnittstelle zur Datenbank.
85# Alle Anfragen laufen somit ueber diesen Client.
86
87@@mysqlclient = Mysql2::Client.new(:host => db_con[0], :username => db_con[1],:password => db_con[2] ,:database => db_con[3],:port => db_con[4],:reconnect => true)
88
89
90
91##
92# Hilfsfunktion fuer SQL-Anweisungen (redundanz von SELECT-Aweisungen vermeiden)
93# Parameter: value (string) , table_con (string)
94# Rueckgabewert(e): SQL_Result (MYSQL::RESULT) / nil
95
96def sel(value,table ,con)
97 return "SELECT #{value} FROM #{table} WHERE #{con}"
98end
99
100
101##
102# Hilfsfunktion fuer TT.MM.JJJJ Ausgaben
103# Diese sind fuer das Anlegen von Zeitstempel wichtig
104# Parameter (optional): year (Int/String)
105# Rueckgabewert(e): time (String)
106
107def short_time(y = Time.now.year)
108 return "#{Time.now.day}.#{Time.now.month}.#{y}"
109end
110
111
112##
113# Hilfsfunktion um einen Script an eine SQL-Datenbank zu schicken
114# Parameter: script (String)
115# Rueckgabewert(e): tmp (MYSQL::RESULT) / Array([]) / error0* (string)
116
117def qry(script)
118 begin
119 tmp=@@mysqlclient.query(script.to_s,:as => :array)
120 if script.include?('SELECT') then
121 if to_array(tmp).size == 0 then
122 return [[]]
123 else
124 return tmp
125 end
126 else
127 return "error02: Es war eine Selection"
128 end
129 rescue => e
130 return "error03: SQL-Anweisung fehlerhaft\nSQL-ERROR:#{e.to_s}"
131 end
132end
133
134
135##
136# Diese globale Methode setzt den login um
137# PW-Eingabe wird mit dem HAsh aus DB verglichen
138# Parameter: Nutzer.Mail=u_mail (String) , Nutzer.PW=u_pw (String)
139# Rueckgabewert(e): ["Nutzer.Name" , "Nutzer.VName" , "Gruppen.Name",Nutzer.ID] / nil
140
141def login(u_mail,u_pw=" ")
142 tmp_pw = to_array(qry(sel("pw","nutzer","email=\'#{u_mail}\'")),'short')[0]
143 tmp_user = to_array(qry(sel("n.name,n.vname,g.name,n.id","nutzer n, nutzer_gruppen ng, gruppen g","n.email = \'#{u_mail}\' AND n.pw = \'#{u_pw}\' AND n.id = ng.nutzer_id AND ng.gruppen_id= g.id AND n.aktiv = 1")))
144 if ((not tmp_user.nil?) && tmp_user.size>1) then
145 tmp_user=tmp_user[0]
146 ts=((Time.utc(Time.now.year,Time.now.month,Time.now.day,Time.now.hour,Time.now.min,Time.now.sec)).to_s)[0...-4]
147 #tmp_user = to_array(tmp_user,'short')
148 qry("UPDATE nutzer SET zuletzt_online = NOW() WHERE id =#{tmp_user[3]}")
149 return tmp_user
150 end
151 #puts "Ausgabe aller Nutzer #{to_array(qry("select * from nutzer"))}"
152 begin
153 if (BCrypt::Password.new(tmp_pw)==u_pw) then
154 tmp_user = qry(sel("n.name,n.vname,g.name,n.id","nutzer n, nutzer_gruppen ng, gruppen g","n.email = \'#{u_mail}\' AND n.id = ng.nutzer_id AND ng.gruppen_id= g.id AND n.aktiv = 1"))
155 ts=((Time.utc(Time.now.year,Time.now.month,Time.now.day,Time.now.hour,Time.now.min,Time.now.sec)).to_s)[0...-4]
156 tmp_user = to_array(tmp_user,'short')
157 puts "#{Time.now.asctime} | [Login] Nutzer: #{tmp_user[3]} Mail: #{u_mail}"
158 qry("UPDATE nutzer SET zuletzt_online = NOW() WHERE id =#{tmp_user[3]}")
159 return tmp_user
160 else
161 return nil
162 end
163 rescue =>e
164 return nil
165 end
166end
167
168
169##
170# Diese globale Methode reduziert ein SQL-Ergebnis
171# Struktur wird von [[a],[b],..,[n]] zu [a,b,c,..,n] geaendert
172# Methode sollte nur benutzt, werden wenn man ein Array von Arrays der Laenge eins bekommt.
173# Andernfalls wird Information weg geschnitten.
174# Parameter: qry_result (Mysql2::Result) , mode (string)
175# Rueckgabewert(e): res (Array[String])
176
177def to_array(qry_result,mode='long')
178 if qry_result.class.name.eql?("Mysql2::Result") then
179 res = Array.new
180 qry_result.each do |item|
181 res.push(item)
182 end
183 if mode.eql?('long') then
184 return res
185 else
186 if res[0].class.name.eql?("Array") && mode=="vshort" then
187 return res[0][0]
188 else
189 return res[0]
190 end
191 end
192 else
193 return ""
194 end
195end
196
197
198##
199# Java Script erfordert ein Array vom gleichen Typ (String)
200# Dient zur auspassung der ausgabe im Player
201# Parameter: arr (Array(Array)) / arr (Array)
202# Rueckgabewert(e): arr (Array(string)) / nil
203
204def to_strg(arr)
205 p arr
206 if arr.class.name=="Array" and arr[0].class.name == "Array" and arr[0].size>0 then
207 puts "arr size i = #{arr.size}\narr size j = #{arr[0].size}"
208 for i in 0...(arr.size) do
209 #puts "[---------------------------------------]"
210 #puts "arr size i = #{arr.size}\narr size j = #{arr[i].size}"
211 for j in 0...(arr[i].size) do
212 #puts "to_strg := #{arr[i][j]}"
213 #puts "type #{arr[i][j].class.name}"
214 arr[i][j]="#{arr[i][j]}"
215 end
216 end
217 else
218 unless arr.nil? and arr=="" then
219 for j in 0...arr.size do
220 arr[j]="#{arr[j]}"
221 end
222 else
223 arr=nil
224 end
225 end
226 return arr
227end
228
229
230##
231# Er zeugt aus einem Datensatz eine CSV-Datei.
232# Parameter: data (Array)
233# Rueckgabewert(e): ret (String)
234
235def to_csv(data=["","","0"]) #package_con #[][]
236 ret="Nachname;Vorname;Anzahl der Lieder;Anzahl der Pakete\n"
237 puts "data = #{data}"
238 if data[0].class.name=='Array' then
239 for i in 0...data.size do
240 ret<<"#{data[i][0]};#{data[i][1]};#{data[i][2]};#{((data[i][2])/@@package_con).to_i}\n"
241 end
242 else
243 ret<<"#{data[0]};#{data[1]};#{data[2].to_i};#{((data[2].to_i)/@@package_con).to_i}"
244 end
245 return ret
246end
247
248def monthToSel(m)
249 ret = Array.new(12)
250 m=m.to_i
251 p m
252 p (m=='2')
253 p (m==2)
254 case m
255 when 1 then
256 ret[0]=String('selected="selected"')
257 when 2 then
258 ret[1]=String('selected="selected"')
259 when 3 then
260 ret[2]=String('selected="selected"')
261 when 4 then
262 ret[3]=String('selected="selected"')
263 when 5 then
264 ret[4]=String('selected="selected"')
265 when 6 then
266 ret[5]=String('selected="selected"')
267 when 7 then
268 ret[6]=String('selected="selected"')
269 when 8 then
270 ret[7]=String('selected="selected"')
271 when 9 then
272 ret[8]=String('selected="selected"')
273 when 10 then
274 ret[9]=String('selected="selected"')
275 when 11 then
276 ret[10]=String('selected="selected"')
277 when 12 then
278 ret[11]=String('selected="selected"')
279 end
280 return ret
281end
282
283def yearToSel(y)
284 ret=Array.new(4)
285 y=y.to_i
286 p "y=#{y} y.class=#{y.class.name}"
287 p (y=='2016')
288 p (y==2016)
289 case y
290 when 2015 then
291 ret[0]=String('selected="selected"')
292 when 2016 then
293 ret[1]=String('selected="selected"')
294 when 2017 then
295 ret[2]=String('selected="selected"')
296 when 2018 then
297 ret[3]=String('selected="selected"')
298 end
299 return ret
300end
301
302# Diese globale Methode legt einen neue Nutzer an sofern dieser noch nicht existiert (Email darf nur einmal in DB vorkommen).
303# Variablen/Parameter werdern so gesetzt:
304# n=Name , v=VName , e=Email , p=PW , g=Gruppen.Name , k=Kontakt , Aktiv=true ,
305# reg_seit="Time.now.day"+"."+Time.now.month+"."+Time.now.year,
306# Kommentar=nil , zuletzt_Online="Time.now.day"+"."+Time.now.month+"."+"null"
307# Rueckgabewert(e): erfolgreich = true / nil / "Nutzer ist schon in der Datenbank"
308
309def create_user(nn,vn,em,pw,geb,kon,kom,grp) #8 -> was sind die werte
310 unless geb.include?('-') then
311 t=(Time.utc(geb[6..9],geb[3..4],geb[0..1])).to_s
312 else
313 t=geb[0..9]
314 end
315 t=t[0...-4]
316 pw=BCrypt::Password.create(pw)
317 #qry("INSERT INTO nutzer (name,vname,email,pw,geb,kontakt,kommentar,reg_am,aktiv,zuletzt_online) VALUES (\'#{nn}\',\'#{vn}\',\'#{em}\',\'#{pw}\',\'#{t}\',\'#{kon}\',\'#{kom}\',NOW(),1,null")
318 qry("INSERT INTO nutzer (name,vname,email,pw,geb,kontakt,kommentar,reg_am,aktiv,zuletzt_online) \nVALUES (\'#{nn}\',\'#{vn}\'\n,\'#{em}\',\'#{pw}\',\'#{t}\',\'#{kon}\'\n,\'#{kom}\',\nNOW(),1,\nNOW())")
319 tmp_uid = to_array(qry(sel("id","nutzer","email = \'#{em}\'")),'short')
320 ##tmp_gid = to_array(qry(sel("ID","Gruppen","Name = #{g}")),'short')[0]
321 qry("INSERT INTO nutzer_gruppen (nutzer_id,gruppen_id) VALUES (#{tmp_uid[0]},#{grp})")
322 puts "[#{Time.now}] | [ADD] Nutzer_ID: #{tmp_uid} Nutzer_Name: #{nn} Nutzer_EMail: #{em}"
323 return true
324end
325
326
327##
328# Die globale Methode Change User bekommt alle (not Null) Informationsfelder und ersetzt diese. (komplettes ueberschreiben)
329# Die ID kann nicht geaendert werden und Zeitstempel bleiben so wie sie vorher waren.
330# Benutzer koennen deativiert werden, falls diese nicht mehr gebraucht werden.
331# Ein spaeteres Loeschen wird ueber ein spezielles Admin-Sql-Fenster realisiert.
332# Variablen/Parameter werdern so gesetzt:
333# id=ID, n=Name, v=VName, e=Email, p=PW, g=Gruppen.Name, kon=Kontakt, kom=Komentar, ak=aktiv
334# Rueckgabewert(e): true/false (Bool)
335
336def change_user(id,nn,vn,em,pw,geb,kon,kom,grp,ak)
337 unless geb.include?('-') then
338 t=(Time.utc(geb[6..9],geb[3..4],geb[0..1])).to_s
339 else
340 t=geb[0..9]
341 end
342 if pw=="" then
343 up_u=qry("UPDATE nutzer SET name=\'#{nn}\',vname=\'#{vn}\',email=\'#{em}\',geb=\'#{t}\',kontakt=\'#{kon}\',kommentar=\'#{kom}\',aktiv=#{ak} WHERE ID=#{id}")
344 up_gr=qry("UPDATE nutzer_gruppen SET gruppen_id=#{grp} WHERE nutzer_id=#{id}")
345 else
346 begin
347 pw=BCrypt::Password.create(pw)
348 up_u=qry("UPDATE nutzer SET name=\'#{nn}\',vname=\'#{vn}\',email=\'#{em}\',pw=\'#{pw}\',geb=\'#{t}\',kontakt=\'#{kon}\',kommentar=\'#{kom}\',aktiv=#{ak} WHERE ID=#{id}")
349 up_gr=qry("UPDATE nutzer_gruppen SET gruppen_id=#{grp} WHERE nutzer_id=#{id}")
350 puts "[#{Time.now}] | [CHANGE] Nutzer_ID: #{id} Nutzer_Name: #{nn} Nutzer_EMail: #{em}"
351 if up_u.include?("error0") || up_gr.include?("error0") then
352 return false
353 else
354 return true
355 end
356 rescue => e
357 return false
358 end
359 end
360end
361
362
363##
364# Die globale Methode delete_user loescht alle deaktivierten Nutzer.
365# Diese Funktion kann auch dierekt ueber die Datenbank genutzt werden.
366# Rueckgabewert(e): true (bool) / "Es konnte.." (string)
367
368def delete_user
369 tmp_users = to_array(qry(sel("id.name","nutzer","aktiv=0")))
370 if (tmp_user.eql?("[[]]")) then
371 return "Es konnten keine deaktivierten Nutzer gefunden Werden"
372 else
373 qry("DELETE FROM nutzer WHERE aktiv=0 and id>2")
374 puts "[#{Time.now}] | [DELETE_USER(S)] #{tmp_users}"
375 return true
376 end
377end
378
379
380##
381# Hilfsfunktion um nutzer finden, gibt Nutzer fuer den Login zurueck
382# Parameter: such_krit (string)
383# Rueckgabewert(e): Array(Array)/['', 'Null', 'Null', 'Null', 'Null', 'Null', 'Null', 0, 'Null', 3]
384
385def find_user(such_krit)
386 sk="%#{such_krit}%"
387 #tmp_user = to_array(qry(sel("id,name,vname,geb,email,kontakt,kommentar,aktiv,zuletzt_online","nutzer","name LIKE \'#{sk}\' OR vname LIKE \'#{sk}\' OR email LIKE \'#{sk}\'")),'short')
388 tmp_user = to_array(qry(sel("id,name,vname,geb,email,kontakt,kommentar,aktiv,zuletzt_online","nutzer","email LIKE \'#{sk}\'")))
389 if tmp_user.nil? or tmp_user.size==0 then
390 return ['', 'Null', 'Null', 'Null', 'Null', 'Null', 'Null', 0, 'Null', 3]
391 else
392 puts "tmp_user [0]=#{tmp_user[0]}"
393 tmp_grp = to_array(qry(sel("ng.gruppen_id","nutzer_gruppen ng","#{tmp_user[0][0]}=ng.nutzer_id")))
394 if tmp_grp.nil? or tmp_grp.size==0 then
395 tmp_user<<"0"
396 else
397 tmp_user[0]<<tmp_grp[0][0].to_s
398 end
399 return tmp_user
400 end
401end
402
403
404##
405# Hilfsfunktion um alle Nutzer anzeigen zulassen
406# Parameter: krit (string)
407# Rueckgabewert(e): Array(Array)
408
409def all_user(krit="")
410 return to_array(qry("SELECT id,name,vname,geb,email,kontakt,kommentar,aktiv,zuletzt_online FROM nutzer #{krit}"))
411end
412
413
414##
415# Hilfsfunktion um beim erstellen eines neuen Nutzers (admin) keinen vorhandenen zu ueberschreiben
416# Parameter: email (string)
417# Rueckgabewert(e): true/false (bool)
418
419def exists(email)
420 count=to_array(qry("SELECT COUNT(DISTINCT email) FROM nutzer where email=\'#{email}\'"))
421 if count[0][0]==1 then
422 return true
423 else
424 return false
425 end
426end
427
428
429##
430# Diese globale Methode gibt die Anzahl der vollstaendig bearbeiteten Pakete zurueck.
431# Parameter: uid (String)
432# Rueckgabewert(e): 0/(tmp_count/package_con) (Int)
433
434def show_packages(uid,t=nil)
435 tmp_count =to_array(qry(sel("COUNT(nutzer_id)","nutzer_lieder","nutzer_id=#{uid} AND bezahlt=0")),'short')
436 if tmp_count.nil? then
437 return 0
438 else
439 if t.nil? then
440 tmp=(@@package_con).to_i
441 return (tmp_count[0]/tmp)
442 else
443 return (tmp_count[0]).to_i
444 end
445 end
446end
447
448
449##
450# Diese globale Methode setzt die Anzahl der Pakete neu.
451# Parameter: value (int)
452# Rueckgabewert(e): true (Bool)/nil
453
454def set_packagesize(value)
455 if File.exist?("info.cfg") then
456 File.write('info.cfg',File.read('info.cfg').gsub(/package_size=.*/,"package_size=\'#{value}\'"))
457 @@package_con=value
458 puts "[#{Time.now}] | [CHANGE] info.cfg new package_size = #{value} "
459 return true
460 else
461 puts "[#{Time.now}] | [ERROR] Config-file fehlt im Ordner(info.cfg)"
462 return nil
463 end
464end
465
466
467##
468# Diese globale Methode setzt die Anzahl der Bearbeitungen fuer die Logik neu.
469# Parameter: value (int)
470# Rueckgabewert(e): true (Bool)/nil
471
472def set_checknum(value)
473 if File.exist?("info.cfg") then
474 File.write('info.cfg',File.read('info.cfg').gsub(/check_num=.*/,"check_num=\'#{value}\'"))
475 @@checknumber=value
476 puts "[#{Time.now}] | [CHANGE] info.cfg new checknum = #{value} "
477 return true
478 else
479 puts "[#{Time.now}] | [ERROR] Config-file fehlt im Ordner(info.cfg)"
480 return nil
481 end
482end
483
484
485##
486# Diese globale Methode gibt eine Liste von noch unbearbeitet Liedern aus.
487# Parameter: uid (String)
488# Rueckgabewert(e): tmp_tracks (Array[String])
489
490def show_usertracks(uid)
491 tmp_tracks = Array.new
492 tmp_tracks = qry(sel("l.id,l.Titel,l.Interpret,l.Album,l.Jahr,l.Dauer","Nutzer_Lied nl ,Lieder l","nl.nutzer_id<>#{uid} AND nl.lieder_id=l.id"))
493 return tmp_tracks
494end
495
496
497##
498# Hilfsmehode weist dem Player (Nutzer) ein Liederpfad zu.
499# Parameter: track_id (int)
500# Rueckgabewert(e): Array(Array)
501
502def select_track(track_id)
503 return (sel("speicher_pfad","lieder","id=#{track_id}"))
504end
505
506
507##
508# Diese globale Methode gibt eine Liste von Lieder zurueck, die von der Logik abgewiesen wurde.(wegen zu grosser Abweichung der Metadaten).
509# Rueckgabewert(e): tmp_tracks (Array(String))
510
511def show_invalidtracks
512 tmp_tracks = to_array(qry(sel("COUNT(DISTINCT id)","lieder ","valid <> 1")),'short')
513 return tmp_tracks[0]
514end
515
516
517##
518# Weist einen Nutzer ein Lied zu.
519# Parameter: user_id (int)
520# Rueckgabewert(e): Array(string)
521
522def get_user_track(user_id)
523 return to_array(qry("SELECT l.id,l.speicher_pfad
524 FROM lieder l where not exists
525 (select nl.lieder_id from nutzer_lieder nl where nl.nutzer_id=#{user_id} AND nl.lieder_id=l.id);"),'short')
526end
527
528
529##
530# Weist einen redakteur ein invalides Lied zu.
531# Rueckgabewert(e): Array(string))
532
533def get_red_track
534 return to_array(qry("SELECT id,speicher_pfad FROM lieder where valid=0"),'short')
535end
536
537
538##
539# Hilfsfunktion die den "nohup" Log des Servers ausliesst.
540# Rueckgabewert(e): log (string)
541
542def get_log
543 ln=0
544 if File.exist?("nohup.out") then
545 log=String.new
546 File.open('nohup.out','r').readlines.each do |e|
547 log << "|<p style=\"text-align:left\">#{ln}| #{e}"
548 ln+=1
549 end
550 else
551 log = "Log-File fehlt"
552 end
553 return log
554end
555
556
557##
558# Gibt zu einem bestimmten Lied die Beatpositionen zurueck
559# Parameter: lied_id (int)
560# Rueckgabewert(e): tmp_list (Array(string))
561
562def get_beatlist(lied_id)
563 tmp_list = to_array(qry("SELECT zeit from beatpos where lied_id = #{lied_id}"))
564 return tmp_list
565end
566
567##
568# Hilfsfunktion um das HTML-layout den Gruppen anzupassen.
569# Parameter: id(int)
570# Rueckgabewert(e): Array (String)
571
572def to_group(id)
573 case id.to_s
574 when '3' then
575 return ["checked=true","",""]
576 when '2' then
577 return ["","checked=true",""]
578 when '1' then
579 return ["","","checked=true"]
580 else
581 return ["","",""]
582 end
583end
584
585
586##
587# Hilfsfunktion um das HTML-layout an das Feld "active" anzupassen.
588# Parameter: b (int)
589# Rueckgabewert(e): Array (String)
590
591def to_active(b)
592 if (b==1) then
593 return ['checked=true','']
594 else
595 return ['','checked=true']
596 end
597end
598
599
600##
601# Hilfsfunktion um das HTML-layout an das Feld Starttyp im Player anzupassen.
602# Parameter: type (string)
603# Rueckgabewert(e): Array (string)
604
605def to_starttyp(type)
606 case type[0]
607 when 'Fade In' then
608 return [String('selected="selected"'),'']
609 when 'Cold Start' then
610 return ['',String('selected="selected"')]
611 else
612 return ['error','error']
613 end
614end
615
616
617
618##
619# Hilfsfunktion um das HTML-layout an das Feld Endtype im Player anzupassen.
620# Parameter: type (string)
621# Rueckgabewert(e): Array (string)
622
623def to_endtyp(type)
624 puts "#to_endtyp =#{type}"
625 case type[0]
626 when 'Fade Out' then
627 return [String('selected="selected"'),'','']
628 when 'Cold End' then
629 return ['',String('selected="selected"'),'']
630 when 'Quick End'
631 return ['','',String('selected="selected"')]
632 else
633 return ['','','']
634 end
635end
636
637
638##
639# Hilfsfunktion um das HTML-layout an das Feld Tonart im Player anzupassen.
640# Parameter: arr (string)
641# Rueckgabewert(e): Array (string)
642
643def to_select(arr)
644 ret=Array.new(24)
645 puts "#to_select arr =#{arr}"
646 case arr[0]
647 when 'C Dur' then
648 ret[0]=String('selected="selected"')
649 when 'Cis/Des Dur'
650 ret[1]=String('selected="selected"')
651 when 'D Dur' then
652 ret[2]=String('selected="selected"')
653 when 'Dis/Es Dur' then
654 ret[3]=String('selected="selected"')
655 when 'E Dur' then
656 ret[4]=String('selected="selected"')
657 when 'F Dur' then
658 ret[5]=String('selected="selected"')
659 when 'Fis/Ges Dur' then
660 ret[6]=String('selected="selected"')
661 when 'G Dur' then
662 ret[7]=String('selected="selected"')
663 when 'Gis/As Dur' then
664 ret[8]=String('selected="selected"')
665 when 'A Dur' then
666 ret[9]=String('selected="selected"')
667 when 'Ais/B Dur' then
668 ret[10]=String('selected="selected"')
669 when 'H Dur' then
670 ret[11]=String('selected="selected"')
671 when 'C Moll' then
672 ret[12]=String('selected="selected"')
673 when 'Cis/Des Moll' then
674 ret[13]=String('selected="selected"')
675 when 'D Moll' then
676 ret[14]=String('selected="selected"')
677 when 'Dis/Es Moll' then
678 ret[15]=String('selected="selected"')
679 when 'E Moll' then
680 ret[16]=String('selected="selected"')
681 when 'F Moll' then
682 ret[17]=String('selected="selected"')
683 when 'Fis/Ges Moll' then
684 ret[18]=String('selected="selected"')
685 when 'G Moll' then
686 ret[19]=String('selected="selected"')
687 when 'Gis/As Moll' then
688 ret[20]=String('selected="selected"')
689 when 'A Moll' then
690 ret[21]=String('selected="selected"')
691 when 'Ais/B Moll' then
692 ret[22]=String('selected="selected"')
693 when 'H Moll' then
694 ret[23]=String('selected="selected"')
695 else
696 ret.clear
697 end
698 puts "#to_select ret =#{ret}"
699 return ret
700end
701
702
703##
704# Die Logik wird durch diese Methode realisiert.
705# Es selektiert nach einer Abweichung vom Durchschnitt und setzt diese Lieder valid=0
706# Parameter: x (int)
707# Rueckgabewert(e): true (bool)
708
709def logik(x)
710 qry("-- Logikdatensatz fuer start eintragen (x = lied_id) (vom Backend bestimmter Starttyp nicht betrachtet)
711 INSERT INTO start
712 VALUES (#{x} AS lied_id,
713 (SELECT MIN(ABS((SELECT AVG(zeit)
714 FROM start S
715 WHERE S.lied_id = #{x}) - S2.zeit))
716 FROM start S2
717 WHERE S2.lied_id = #{x}) AS zeit,
718 (SELECT starttyp_id
719 FROM start S
720 WHERE S.lied_id = #{x}
721 AND S.nutzer_id <> 0
722 AND COUNT(SELECT *
723 FROM start S2
724 WHERE S2.lied_id = #{x}
725 AND S2.starttyp_id = S.starttyp_id
726 AND S2.nutzer_id <> 0)
727 > ALL(COUNT(SELECT *
728 FROM start S3
729 WHERE S3.lied_id = #{x}
730 AND S3.starttyp_id <> S.starttyp_id
731 AND S3.nutzer_id <> 0
732 GROUP BY starttyp_id))) AS starttyp_id,
733 1 AS nutzer_id);
734
735 -- Konsistenz der Start-Datensaetze pruefen
736 UPDATE lieder
737 SET valid=0
738 WHERE lied_id=#{x} and
739 (count(
740 select *
741 from start
742 where lied_id=#{x}
743 and starttyp_id=0 and nutzer_id<>0)>=1)
744 AND
745 (count(
746 select *
747 from start
748 where lied_id=#{x}
749 and starttyp_id=1 and nutzer_id<>0)>=1);
750 UPDATE lieder
751 SET valid=0
752 WHERE lied_id=#{x} AND
753 ((1.5<STD(select zeit
754 FROM start
755 where lied_id=#{x}
756 AND nutzer_id<>1
757 and nutzer_id<>0))
758 OR
759 (3<MAX(
760 ABS((select zeit
761 FROM start
762 where lied_id=#{x}
763 AND nutzer_id<>1
764 AND nutzer_id=0)-
765 (SELECT zeit
766 FROM start
767 where lied_id=#{x}
768 AND nutzer_id<>1
769 AND nutzer_id<>0)
770 )
771 ))
772 );
773
774 UPDATE lieder
775 SET geprueft=1
776 WHERE lieder_id=#{x};
777
778 -- Logikdatensatz fuer ende eintragen (vom Backend bestimmter Endtyp nicht betrachtet)
779 INSERT INTO ende
780 VALUES (#{x} AS lied_id,
781 (SELECT MIN(ABS((SELECT AVG(zeit)
782 FROM ende E
783 WHERE E.lied_id = #{x}) - E2.zeit))
784 FROM ende E2
785 WHERE E2.lied_id = #{x}) AS zeit,
786 (SELECT endtyp_id
787 FROM ende E
788 WHERE E.lied_id = #{x}
789 AND E.nutzer_id <> 0
790 AND COUNT(SELECT *
791 FROM ende E2
792 WHERE E2.lied_id = #{x}
793 AND E2.endtyp_id = E.endtyp_id
794 AND E2.nutzer_id <> 0)
795 > ALL(COUNT(SELECT *
796 FROM ende E3
797 WHERE E3.lied_id = #{x}
798 AND E3.endtyp_id <> E.endtyp_id
799 AND E3.nutzer_id <> 0
800 GROUP BY endtyp_id))) AS endtyp_id,
801 1 as nutzer_id);
802 -- Konsistenz der Ende-Datensaetze pruefen
803 UPDATE lieder
804 SET valid=0
805 WHERE lied_id=#{x} and
806 (count(
807 select *
808 from ende
809 where lied_id=#{x}
810 and starttyp_id=0
811 and nutzer_id<>0)>=1)
812 AND
813 (count(
814 select *
815 from ende
816 where lied_id=#{x}
817 and starttyp_id=1
818 and nutzer_id<>0)>=1);
819 UPDATE lieder
820 SET valid=0
821 WHERE lied_id=#{x} AND
822 ((1.5<STD(select zeit
823 FROM ende
824 where lied_id=#{x}
825 AND nutzer_id<>1
826 and nutzer_id<>0))
827 OR
828 (3<MAX(
829 ABS((select zeit
830 FROM ende
831 where lied_id=#{x}
832 AND nutzer_id<>1
833 AND nutzer_id=0)-
834 (SELECT zeit
835 FROM ende
836 where lied_id=#{x}
837 AND nutzer_id<>1
838 AND nutzer_id<>0)
839 )
840 ))
841 );
842
843 UPDATE lieder
844 SET geprueft=1
845 WHERE lieder_id=#{x};
846 -- Logikdatensatz fuer markin eintragen
847 INSERT INTO mark
848 VALUES (#{x} AS lied_id,
849 (SELECT MIN(ABS(SELECT AVG(zeit)
850 FROM mark M
851 WHERE M.lied_id = #{x} - M2.zeit
852 AND M.markin = 1 - M2.zeit))
853 FROM mark M2
854 WHERE M2.lied_id = #{x}
855 AND M.markin = 1
856 AND M.markin = M2.markin) AS zeit,
857 1 AS markin,
858 (SELECT tonart_id
859 FROM mark M
860 WHERE M.tonart_id = #{x}
861 AND M.markin = 1
862 AND COUNT(SELECT *
863 FROM mark M2
864 WHERE M2.lied_id = #{x}
865 AND M2.tonart_id = M.tonart_id
866 AND M2.markin = M.markin)
867 > ALL(COUNT(SELECT *
868 FROM mark M3
869 WHERE M3.lied_id = #{x}
870 AND M3.tonart_id <> M.tonart_id
871 AND M3.markin = M.markin
872 GROUP BY tonart_id))) AS tonart_id,
873 1 AS nutzer_id);
874 -- Konsistenz der Markin-Datensaetze pruefen
875 UPDATE lieder
876 SET valid=0
877 WHERE lied_id=#{x} and
878 (count(
879 select *
880 from mark
881 where lied_id=#{x}
882 AND markin=1
883 AND starttyp_id=0
884 AND nutzer_id<>0)>=1)
885 AND
886 (count(
887 select *
888 from mark
889 where lied_id=#{x}
890 AND markin=1
891 AND starttyp_id=1
892 AND nutzer_id<>0)>=1);
893 UPDATE lieder
894 SET valid=0
895 WHERE lied_id=#{x} AND
896 ((1.5<STD(select zeit
897 FROM mark
898 where lied_id=#{x}
899 AND markin=1
900 AND nutzer_id<>1
901 AND nutzer_id<>0))
902 OR
903 (3<MAX(
904 ABS((select zeit
905 FROM mark
906 where lied_id=#{x}
907 AND markin=1
908 AND nutzer_id<>1
909 AND nutzer_id=0)-
910 (SELECT zeit
911 FROM mark
912 where lied_id=#{x}
913 AND markin=1
914 AND nutzer_id<>1
915 AND nutzer_id<>0)
916 )
917 ))
918 );
919
920 CREATE VIEW tonartcount AS
921 COUNT(SELECT *
922 FROM mark
923 WHERE lied_id=#{x}
924 AND markin=1
925 AND nutzer_id<>0
926 GROUP BY tonart_id) AS Anzahl
927
928 UPDATE lieder
929 SET valid=0
930 WHERE lied_id=#{x}
931 AND 2<(Count(
932 Select Anzahl
933 from tonartcount
934 where Anzahl>0))
935
936 UPDATE lieder
937 SET geprueft=1
938 WHERE lieder_id=#{x};
939 -- Logikdatensatz fuer markout eintragen
940 INSERT INTO mark
941 VALUES (#{x} AS lied_id,
942 (SELECT MIN(ABS((SELECT AVG(zeit)
943 FROM mark M
944 WHERE M.lied_id = #{x}
945 AND M.markin = 0) - M2.zeit))
946 FROM mark M2
947 WHERE M2.lied_id = #{x}
948 AND M.markin = 0
949 AND M.markin = M2.markin) AS zeit,
950 0 AS markin,
951 (SELECT tonart_id
952 FROM mark M
953 WHERE M.tonart_id = #{x}
954 AND M.markin = 0
955 AND COUNT(SELECT *
956 FROM mark M2
957 WHERE M2.lied_id = #{x}
958 AND M2.tonart_id = M.tonart_id
959 AND M2.markin = M.markin)
960 > ALL(COUNT(SELECT *
961 FROM mark M3
962 WHERE M3.lied_id = #{x}
963 AND M3.tonart_id <> M.tonart_id
964 AND M3.markin = M.markin
965 GROUP BY tonart_id))) AS tonart_id,
966 1 AS nutzer_id);
967 UPDATE lieder
968 SET valid=0
969 WHERE lied_id=#{x} AND
970 ((1.5<STD(select zeit
971 FROM mark
972 where lied_id=#{x}
973 AND markin=0
974 AND nutzer_id<>1
975 AND nutzer_id<>0))
976 OR
977 (3<MAX(
978 ABS((select zeit
979 FROM mark
980 where lied_id=#{x}
981 AND markin=0
982 AND nutzer_id<>1
983 AND nutzer_id=0)-
984 (SELECT zeit
985 FROM mark
986 where lied_id=#{x}
987 AND markin=0
988 AND nutzer_id<>1
989 AND nutzer_id<>0)
990 )
991 ))
992 );
993
994 CREATE VIEW tonartcount AS
995 COUNT(SELECT *
996 FROM mark
997 WHERE lied_id=#{x}
998 AND markin=0
999 AND nutzer_id<>0
1000 GROUP BY tonart_id) AS Anzahl
1001
1002 UPDATE lieder
1003 SET valid=0
1004 WHERE lied_id=#{x}
1005 AND 2<(Count(
1006 Select Anzahl
1007 from tonartcount
1008 where Anzahl>0))
1009
1010 UPDATE lieder
1011 SET geprueft=1
1012 WHERE lieder_id=#{x};
1013 -- Logikdatensatz fuer bpms durchgehend eintragen
1014 INSERT INTO bpms
1015 VALUES (#{x} AS lied_id,
1016 (SELECT bpm
1017 FROM bpms B
1018 WHERE B.lied_id = #{x}
1019 AND B.nutzer_id = 0) AS bpm,
1020 (SELECT durchgehend
1021 FROM bpms B
1022 WHERE B.lied_id = #{x}
1023 AND COUNT(SELECT *
1024 FROM bpms B2
1025 WHERE B2.lied_id = #{x}
1026 AND B2.durchgehend = B.durchgehend)
1027 > ALL(COUNT(SELECT *
1028 FROM bpms B3
1029 WHERE B3.lied_id = #{x}
1030 AND B3.durchgehend <> B.durchgehend
1031 GROUP BY durchgehend))) AS durchgehend,
1032 1 AS nutzer_id);
1033 -- Konsistenz der bpms-Datensaetze pruefen
1034 UPDATE lieder
1035 SET valid=0
1036 WHERE lied_id=#{x} and
1037 (count(
1038 select *
1039 from bpms
1040 where lied_id=#{x}
1041 AND durchgehend=0
1042 AND nutzer_id<>0)>=1)
1043 AND
1044 (count(
1045 select *
1046 from bpms
1047 where lied_id=#{x}
1048 AND durchgehend=1
1049 AND nutzer_id<>0)>=1);
1050
1051 UPDATE lieder
1052 SET geprueft=1
1053 WHERE lieder_id=#{x};
1054 -- Logikdatensatz fuer ges_tonart eintragen
1055 INSERT INTO ges_tonart
1056 VALUES (#{x} AS lied_id,
1057 (SELECT tonart_id
1058 FROM ges_tonart T
1059 WHERE T.lied_id = #{x}
1060 AND COUNT(SELECT *
1061 FROM ges_tonart T2
1062 WHERE T2.lied_id = #{x}
1063 AND T2.tonart_id = T.tonart_id)
1064 > ALL(COUNT(SELECT *
1065 FROM ges_tonart T3
1066 WHERE T3.lied_id = #{x}
1067 AND T3.tonart_id <> T.tonart_id
1068 GROUP BY tonart_id))) AS tonart_id,
1069 1 AS nutzer_id);
1070
1071
1072 -- Konsistenz der MarkinTonart-Datensaetze pruefen
1073 CREATE VIEW tonartcount AS
1074 COUNT(SELECT *
1075 FROM ges_tonart
1076 WHERE lied_id=#{x}
1077 AND nutzer_id<>0
1078 GROUP BY tonart_id) AS Anzahl
1079 UPDATE lieder
1080 SET valid=0
1081 WHERE lied_id=#{x}
1082 AND 2<(Count(
1083 Select Anzahl
1084 from tonartcount
1085 where Anzahl>0))
1086
1087 UPDATE lieder
1088 SET geprueft=1
1089 WHERE lieder_id=#{x};
1090 ")
1091 return true
1092end