was.rb 34 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063
  1. #!/usr/bin/ruby
  2. #
  3. # This file is part of centurio.work/ing/commands.
  4. #
  5. # centurio.work/ing/commands is free software: you can redistribute it and/or
  6. # modify it under the terms of the GNU General Public License as published by
  7. # the Free Software Foundation, either version 3 of the License, or (at your
  8. # option) any later version.
  9. #
  10. # centurio.work/ing/commands is distributed in the hope that it will be useful,
  11. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
  13. # Public License for more details.
  14. #
  15. # You should have received a copy of the GNU General Public License along with
  16. # centurio.work/ing/commands (file COPYING in the main directory). If not, see
  17. # <http://www.gnu.org/licenses/>.
  18. require 'rubygems'
  19. require 'json'
  20. require 'riddl/server'
  21. require 'riddl/client'
  22. require 'fileutils'
  23. require 'nokogiri'
  24. require 'sqlite3'
  25. require 'net/http'
  26. $db = SQLite3::Database.open 'database/stations.db'
  27. class GetStations < Riddl::Implementation
  28. def response
  29. result = $db.execute "SELECT * FROM stations"
  30. builder = Nokogiri::XML::Builder.new do |xml|
  31. xml.stations {
  32. result.each do |row|
  33. xml.station(:id => row[0]){
  34. resultstation = $db.execute "SELECT * FROM station WHERE station = #{row[0]}"
  35. resultstation.each do |row|
  36. xml.pattern(:id => row[1], :value => row[2], :description => row[3], :changed => row[4])
  37. end
  38. }
  39. end
  40. }
  41. end
  42. #puts builder.to_xml
  43. Riddl::Parameter::Complex.new('stations','application/xml',builder.to_xml)
  44. end
  45. end
  46. class CreateStation < Riddl::Implementation
  47. def self::createDB(n)
  48. #result = $db.execute "SELECT MAX(station) FROM stations"
  49. #if(result[0][0] == nil)
  50. # $db.execute "INSERT INTO stations (station) VALUES (?)", 0
  51. #else
  52. # $db.execute "INSERT INTO stations (station) VALUES (?)", result[0][0]+1
  53. #end
  54. #$db.execute ("INSERT INTO stations (station, name) VALUES (?,?)", [n,n])
  55. $db.execute("INSERT OR IGNORE INTO stations (station, name) VALUES (?,?)", [n,n])
  56. end
  57. def response
  58. GetStation::createDB
  59. end
  60. end
  61. class GetStation < Riddl::Implementation
  62. def self::prepare(id)
  63. result = $db.execute "SELECT * FROM station WHERE station = #{id}"
  64. Nokogiri::XML::Builder.new do |xml|
  65. xml.station(:id => id){
  66. result.each do |row|
  67. xml.pattern(:id => row[1], :value => row[2], :description => row[3], :changed => row[4])
  68. end
  69. }
  70. end
  71. end
  72. def response
  73. builder = GetStation::prepare(@r.last)
  74. Riddl::Parameter::Complex.new('station','application/xml',builder.to_xml)
  75. end
  76. end
  77. class CreatePattern < Riddl::Implementation
  78. def response
  79. doc = Nokogiri::XML(@p[0].value)
  80. result = $db.execute "SELECT MAX(patternID) FROM station"
  81. if(result[0][0] == nil)
  82. id = 0
  83. else
  84. id = result[0][0] +1
  85. end
  86. values = doc.xpath("/*/@value")[0].value.split(".")
  87. $db.execute("INSERT INTO station (station, patternID, pattern, description, date, P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [@r.last, id, doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10]])
  88. ret = {:id => id}
  89. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  90. end
  91. end
  92. class GetPattern < Riddl::Implementation
  93. def response
  94. result = $db.execute "SELECT * FROM station WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  95. builder = Nokogiri::XML::Builder.new do |xml|
  96. xml.pattern(:id => result[0][1], :value => result[0][2], :description => result[0][3], :changed => result[0][4])
  97. end
  98. Riddl::Parameter::Complex.new('pattern','application/xml',builder.to_xml)
  99. end
  100. end
  101. class UpdatePattern < Riddl::Implementation
  102. def response
  103. doc = Nokogiri::XML(@p[0].value)
  104. #puts "UPdate Pattern" + @r[0] + @r[1] + " value " + doc.xpath("/*/@value")[0].value
  105. #$db.execute("DELETE FROM station WHERE station = ? AND patternID = ?", [@r[0], @r[1]])
  106. values = doc.xpath("/*/@value")[0].value.split(".")
  107. #$db.execute("INSERT INTO station (station, patternID, pattern, description, date, P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [@r[0], @r[1], doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10]])
  108. #$db.execute("UPDATE image SET imageID = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [999999, @r[0], @r[1], i, @r[4]])
  109. $db.execute("Update station SET pattern = ?, description = ?, date = ?, P0 = ?, P1 = ?, P2 = ?, P3 = ?, P4 = ?, P5 = ?, P6 = ?, P7 = ?, P8 = ?, P9 = ?, P10 = ? WHERE station = ? AND patternID = ?", [doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], @r[0], @r[1]])
  110. #$db.execute("INSERT INTO station (station, patternID, pattern, description, date) VALUES (?,?,?,?,?)", [@r[0], @r[1]], doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value)
  111. #$db.execute("UPDATE station SET pattern = ? and description = ? and date = ? WHERE station = ? and patternID = ?", [doc.xpath("/*/@value")[0].value, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, @r[0], @r[1]])
  112. end
  113. end
  114. class DeletePattern < Riddl::Implementation
  115. def response
  116. $db.execute("DELETE FROM station WHERE station = ? AND patternID = ?", [@r[0], @r[1]])
  117. end
  118. end
  119. class DuplicatePattern < Riddl::Implementation
  120. def response
  121. #duplicate db etwas mühsam da der eintrag kopiert werden muss und dabei die unique ID verändert werden muss daher geht insert into select nicht so ganz.
  122. result = $db.execute "SELECT MAX(patternID) FROM station"
  123. if(result[0][0] == nil)
  124. id = 0
  125. else
  126. id = result[0][0] +1
  127. end
  128. result = $db.execute "SELECT * FROM station WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  129. result[0][1] = id;
  130. questionmarks = ""
  131. result[0].length().times{questionmarks = questionmarks + ",?"}
  132. questionmarks[0] = ""
  133. $db.execute( "INSERT INTO station values (#{questionmarks})", result[0])
  134. #duplicate error
  135. result = $db.execute "SELECT * FROM error WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  136. if(result[0] != nil)
  137. result.each do |row|
  138. row[1] = id;
  139. questionmarks = ""
  140. row.length().times{questionmarks = questionmarks + ",?"}
  141. questionmarks[0] = ""
  142. $db.execute( "INSERT INTO error values (#{questionmarks})", row)
  143. end
  144. end
  145. #duplicate image db
  146. result = $db.execute "SELECT * FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  147. if(result[0][1] != nil)
  148. result.each do |row|
  149. row[1] = id;
  150. questionmarks = ""
  151. row.length().times{questionmarks = questionmarks + ",?"}
  152. questionmarks[0] = ""
  153. $db.execute( "INSERT INTO image values (#{questionmarks})", row)
  154. end
  155. end
  156. #duplicate images
  157. pathorigin = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
  158. pathdestination = File.join(File.dirname(__dir__),'images/uploads', @r[0], id.to_s)
  159. FileUtils.mkdir_p(pathdestination)
  160. FileUtils.copy_entry pathorigin, pathdestination
  161. #duplicate Replacement db
  162. result = $db.execute "SELECT * FROM replacements WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  163. if(result[0][1] != nil)
  164. result.each do |row|
  165. row[1] = id;
  166. questionmarks = ""
  167. row.length().times{questionmarks = questionmarks + ",?"}
  168. questionmarks[0] = ""
  169. $db.execute( "INSERT INTO replacements values (#{questionmarks})", row)
  170. end
  171. end
  172. ret = {:id => id}
  173. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  174. end
  175. end
  176. class SaveError < Riddl::Implementation
  177. def response
  178. $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID))"
  179. #Delete all, afterwards insert
  180. $db.execute("DELETE FROM error WHERE station = ? AND patternID = ?", [@r[0], @r[1]])
  181. doc = Nokogiri::XML(@p[0].value)
  182. doc.xpath(".//reason").each do |node|
  183. $db.execute("INSERT INTO error (station, patternID, error) VALUES (?,?,?)", [@r[0], @r[1], node.text])
  184. end
  185. end
  186. end
  187. class GetError < Riddl::Implementation
  188. def response
  189. $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID))"
  190. result = $db.execute "SELECT * FROM error WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  191. builder = Nokogiri::XML::Builder.new do |xml|
  192. xml.error(){
  193. result.each do |row|
  194. xml.reason row[2]
  195. end
  196. }
  197. end
  198. Riddl::Parameter::Complex.new('error','application/xml',builder.to_xml)
  199. end
  200. end
  201. class SaveReplacement < Riddl::Implementation
  202. def response
  203. #Delete all, afterwards insert
  204. $db.execute("DELETE FROM replacements WHERE station = ? AND patternID = ?", [@r[0], @r[1]])
  205. doc = Nokogiri::XML(@p[0].value)
  206. doc.xpath(".//item").each do |node|
  207. $db.execute("INSERT INTO replacements (station, patternID, abbreviation, url) VALUES (?,?,?,?)", [@r[0], @r[1], node.xpath(".//abbreviation").text, node.xpath(".//url").text])
  208. end
  209. end
  210. end
  211. class GetReplacement < Riddl::Implementation
  212. def response
  213. result = $db.execute "SELECT * FROM replacements WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  214. builder = Nokogiri::XML::Builder.new do |xml|
  215. xml.replacement(){
  216. result.each do |row|
  217. xml.item(){
  218. xml.abbreviation row[2]
  219. xml.url row[3]
  220. }
  221. end
  222. }
  223. end
  224. Riddl::Parameter::Complex.new('replacement','application/xml',builder.to_xml)
  225. end
  226. end
  227. class GetImages < Riddl::Implementation
  228. def response
  229. formatted_no_decl = Nokogiri::XML::Node::SaveOptions::FORMAT +
  230. Nokogiri::XML::Node::SaveOptions::NO_DECLARATION
  231. result = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]}"
  232. builder = Nokogiri::XML::Builder.new do |xml|
  233. xml.images(){
  234. result.each do |row|
  235. xml<< GetImage::prepare(@r[0], @r[1], row[2]).to_xml( save_with:formatted_no_decl )
  236. end
  237. }
  238. end
  239. Riddl::Parameter::Complex.new('images','application/xml',builder.to_xml)
  240. end
  241. end
  242. class UploadImage < Riddl::Implementation
  243. def response
  244. lang = @p[@p.length-1].value
  245. $db.execute "CREATE TABLE IF NOT EXISTS image(station INT, patternID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID),PRIMARY KEY(station,patternID,imageID,language))"
  246. result = $db.execute "SELECT MAX(imageID) FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]} and language = '#{lang}'"
  247. if(result[0][0] == nil)
  248. id = 0
  249. else
  250. id = result[0][0] +1
  251. end
  252. #puts JSON.pretty_generate(@p)
  253. i = 0
  254. while i < @p.length-1 do
  255. item = @p[i]
  256. if(item != nil && item.name == "files[]")
  257. path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s)
  258. FileUtils.mkdir_p(path)
  259. #juergen nach alternative fragen
  260. readFile = File.read(item.value.inspect.to_s[/Tempfile:(.*?)>/m, 1])
  261. File.open(File.join(path, lang + ".svg"), 'wb') do |file|
  262. file.write(readFile.to_s)
  263. end
  264. $db.execute("INSERT INTO image (station, patternID, imageID, language, label) VALUES (?,?,?,?,?)", [@r[0], @r[1], id, lang, "Label"])
  265. id += 1
  266. end
  267. i +=1
  268. end
  269. end
  270. end
  271. class ReorderImages < Riddl::Implementation
  272. def response
  273. #remove brackets
  274. iter = @p[0].value.read.chop
  275. iter[0] =""
  276. iter = iter.split(",").map(&:to_i)
  277. i = 0
  278. while i < iter.length do
  279. if(i != iter[i])
  280. #swap
  281. path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], i.to_s)
  282. path2 = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], iter[i].to_s)
  283. tmp = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
  284. FileUtils.mv(path + "/" + @r[4] + ".svg", tmp)
  285. FileUtils.mv(path2 + "/" + @r[4] + ".svg", path)
  286. FileUtils.mv(tmp + "/" + @r[4] + ".svg", path2)
  287. #DB Swap
  288. result = $db.execute "SELECT MAX(imageID) FROM image"
  289. if(result[0][0] == nil)
  290. maxImgId = 0
  291. else
  292. maxImgId = result[0][0] +1
  293. end
  294. #Wegen 999999 fragen
  295. #nutze max ID+1 zum tauschen
  296. $db.execute("UPDATE image SET imageID = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [maxImgId, @r[0], @r[1], i, @r[4]])
  297. $db.execute("UPDATE image SET imageID = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [i, @r[0], @r[1], iter[i], @r[4]])
  298. $db.execute("UPDATE image SET imageID = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [iter[i], @r[0], @r[1], maxImgId, @r[4]])
  299. iter.map! do |item|
  300. if(item == i)
  301. iter[i]
  302. else
  303. item
  304. end
  305. end
  306. end
  307. i +=1
  308. end
  309. end
  310. end
  311. class GetImage < Riddl::Implementation
  312. def self::prepare(station, pattern, imageID)
  313. result = $db.execute "SELECT * FROM image WHERE station = #{station} and patternID = #{pattern} and imageID = #{imageID}"
  314. Nokogiri::XML::Builder.new do |xml|
  315. xml.image(:id => imageID){
  316. result.each do |row|
  317. xml.variant(:lang => row[3], :label => row[4]){
  318. xml.text("https://centurio.work/customers/evva/was/images/uploads/#{station}/#{pattern}/#{imageID}/#{row[3]}.svg")
  319. }
  320. end
  321. }
  322. end
  323. end
  324. def response
  325. builder = GetImage::prepare(@r[0], @r[1], @r[3])
  326. Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
  327. end
  328. end
  329. class UpdateImageLabel < Riddl::Implementation
  330. def response
  331. $db.execute("UPDATE image SET label = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [@p[0].value, @r[0], @r[1], @r[3], @r[4]])
  332. end
  333. end
  334. class GetRealImage < Riddl::Implementation
  335. def response
  336. #split on "." and tacke [0] to allow for e.g. de-at.svg
  337. #would lead to error on de.at.svg //should this be fixed? would also lead to error if everything after last "." would be removed in case of only having de.at
  338. #https://centurio.work/customers/evva/was/server/0/0/images/3/de-at?video=xyz
  339. img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3], @r[4].split(".")[0] + ".svg"))
  340. if false #Currently replacements are done on the client side
  341. if(@p[0].nil?)
  342. puts "Undefined p"
  343. else
  344. if(@p[0].name == "video" && @p[0].value)
  345. xml = Nokogiri.parse img
  346. puts "Width " + xml.xpath("string(//xmlns:image/@width)")
  347. puts "Height " + xml.xpath("string(//xmlns:image/@height)")
  348. #puts "Posi " + xml.xpath("string(//xmlns:clipPath/path/@height)")
  349. puts "Posi " + xml.xpath("string(//xmlns:image/following-sibling::clipPath/@id)")
  350. puts xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "")
  351. # text
  352. img = img.sub! "</svg>", '
  353. <g><g transform="translate(562,288),scale(1.35185 1.35185)">
  354. <foreignObject width="480" height="270">
  355. <video width="480" height="270" controls="" autoplay="autoplay" muted="muted" xmlns="http://www.w3.org/1999/xhtml" >
  356. <source src="' + xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "") +'" type="video/mp4"/>
  357. </video>
  358. </foreignObject>
  359. </g></g></svg>'
  360. else
  361. @p.each do |item|
  362. unless item.name.nil? || item.value.nil?
  363. img.sub! item.name, item.value
  364. end
  365. end
  366. end
  367. end
  368. end
  369. Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img)
  370. end
  371. end
  372. class DeleteImage < Riddl::Implementation
  373. def response
  374. #puts "Delete Image"
  375. #puts File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg")
  376. File.delete(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg")) if File.exist?(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3] , @r[4] + ".svg"))
  377. $db.execute("DELETE FROM image WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [@r[0], @r[1], @r[3], @r[4]])
  378. result = $db.execute "SELECT MAX(imageID) FROM image WHERE station = #{@r[0]} and patternID = #{@r[1]} and language = '#{@r[4]}'"
  379. if(result[0][0] == nil)
  380. id = 0
  381. else
  382. id = result[0][0] +1
  383. end
  384. #reorder if !end gets deleted
  385. if(id == @r[3])
  386. return
  387. else
  388. cur = @r[3].to_i + 1
  389. prev = @r[3].to_i
  390. while cur < id.to_i do
  391. $db.execute("UPDATE image SET imageID = ? WHERE station = ? AND patternID = ? AND imageID = ? AND language = ?", [prev, @r[0], @r[1], cur, @r[4]])
  392. src = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], cur.to_s, @r[4] + ".svg")
  393. target = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], prev.to_s, @r[4] + ".svg")
  394. FileUtils.mv(src, target)
  395. cur+=1
  396. prev += 1
  397. end
  398. end
  399. end
  400. end
  401. class ListSearch < Riddl::Implementation
  402. def response
  403. ret = {
  404. :patternID => "/patternID",
  405. :multi => "/multi",
  406. :juergen => "/juergen"
  407. }
  408. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  409. end
  410. end
  411. class SearchPattern < Riddl::Implementation
  412. def response
  413. iter = @p[0].value.split(".")
  414. searchstring = ""
  415. count = 0
  416. iter.each do |item|
  417. if !item.nil?
  418. if item.to_s != "*"
  419. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  420. end
  421. count += 1
  422. end
  423. end
  424. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  425. if result.length > 0
  426. ret = {
  427. :amount => result.length.to_s,
  428. :ids => result
  429. }
  430. end
  431. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  432. #puts the first found string
  433. #result = $db.execute "SELECT P0,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10 FROM station WHERE station = 0" + searchstring
  434. #puts "found" + result.length.to_s
  435. #result[0].each do |item|
  436. # if !item.nil?
  437. # puts item + "."
  438. # end
  439. #end
  440. end
  441. end
  442. class SearchImages < Riddl::Implementation
  443. def response
  444. iter = @p[0].value.split(".")
  445. searchstring = ""
  446. count = 0
  447. #search = K.*.9.*
  448. #match: = K.5.9.7
  449. #match: = K.5.9.*
  450. #match: = K.*.*.*
  451. #no match: = K.*.8.*
  452. iter.each do |item|
  453. if !item.nil?
  454. if item.to_s != "*"
  455. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  456. else
  457. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  458. end
  459. count += 1
  460. end
  461. end
  462. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  463. ret = []
  464. if result.length > 0
  465. result.each do |item|
  466. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  467. result2.each do |item2|
  468. ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
  469. end
  470. end
  471. end
  472. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  473. end
  474. end
  475. class SearchImagesSingle < Riddl::Implementation
  476. def response
  477. iter = @p[0].value.split(".")
  478. searchstring = ""
  479. count = 0
  480. iter.each do |item|
  481. if !item.nil?
  482. if item.to_s != "*"
  483. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  484. else
  485. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  486. end
  487. count += 1
  488. end
  489. end
  490. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  491. count = 0
  492. pattern = 0
  493. image = 0
  494. if result.length > 0
  495. result.each do |item|
  496. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  497. result2.each do |item2|
  498. if(count == @r[3].to_i)
  499. puts "Found "
  500. pattern = item2[1]
  501. image = item2[2]
  502. end
  503. count += 1
  504. end
  505. end
  506. end
  507. img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0].to_s, pattern.to_s, image.to_s, "de-at.svg"))
  508. Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img)
  509. end
  510. end
  511. class SearchImages2 < Riddl::Implementation
  512. def response
  513. iter = @p[0].value.split(".")
  514. searchstring = ""
  515. count = 0
  516. #search = K.*.9.*
  517. #match: = K.5.9.7
  518. #match: = K.5.9.*
  519. #match: = K.*.*.*
  520. #no match: = K.*.8.*
  521. iter.each do |item|
  522. if !item.nil?
  523. if item.to_s != "*"
  524. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  525. else
  526. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  527. end
  528. count += 1
  529. end
  530. end
  531. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  532. ret = []
  533. if result.length > 0
  534. result.each do |item|
  535. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  536. result2.each do |item2|
  537. ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
  538. end
  539. end
  540. end
  541. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  542. end
  543. end
  544. class SearchImages2Single < Riddl::Implementation
  545. def response
  546. iter = @p[0].value.split(".")
  547. searchstring = ""
  548. count = 0
  549. iter.each do |item|
  550. if !item.nil?
  551. if item.to_s != "*"
  552. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  553. else
  554. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  555. end
  556. count += 1
  557. end
  558. end
  559. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  560. count = 0
  561. pattern = 0
  562. image = 0
  563. if result.length > 0
  564. result.each do |item|
  565. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  566. result2.each do |item2|
  567. if(count == @r[3].to_i)
  568. puts "Found "
  569. pattern = item2[1]
  570. image = item2[2]
  571. end
  572. count += 1
  573. end
  574. end
  575. end
  576. #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg
  577. img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0].to_s, pattern.to_s, image.to_s, "de-at.svg"))
  578. uri = "https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=" + @r[0].to_s + "/" + pattern.to_s + "/" + image.to_s + "/de-at.svg"
  579. string = '<html><body style="margin: 0"><iframe style="position: absolute; height: 100%; width: 100%; border: none" src="' + uri +'" title="description"></body></html>'
  580. Riddl::Parameter::Complex.new('html','text/html',string)
  581. end
  582. end
  583. class SearchImages3Single < Riddl::Implementation
  584. def response
  585. iter = @p[0].value.split(".")
  586. searchstring = ""
  587. count = 0
  588. iter.each do |item|
  589. if !item.nil?
  590. if item.to_s != "*"
  591. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  592. else
  593. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  594. end
  595. count += 1
  596. end
  597. end
  598. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  599. count = 0
  600. pattern = 0
  601. image = 0
  602. builder = Nokogiri::XML::Builder.new do |xml|
  603. xml.image(:id => @r[3].to_s){
  604. if result.length > 0
  605. result.each do |item|
  606. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  607. result2.each do |item2|
  608. if(count == @r[3].to_i)
  609. result3 = $db.execute "SELECT DISTINCT station, patternID, imageID, language, label FROM image WHERE station = #{@r[0]} and patternID = #{item[0]} and imageID =#{item2[2]}"
  610. result3.each do |item3|
  611. uri = "https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=" + @r[0].to_s + "/" + item3[1].to_s + "/" + item3[2].to_s + "/" + item3[3].to_s + ".svg"
  612. @p.each_with_index do |item, index|
  613. if index != 0
  614. uri += "&" + item.name.to_s + "=" + item.value.to_s
  615. end
  616. end
  617. xml.variant(:lang => item3[3].to_s, :label => item3[4].to_s){
  618. xml.text(uri)
  619. }
  620. end
  621. end
  622. count += 1
  623. end
  624. end
  625. end
  626. }
  627. end
  628. #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg
  629. #puts builder.to_xml
  630. Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
  631. end
  632. end
  633. class SearchErrors < Riddl::Implementation
  634. def response
  635. iter = @p[0].value.split(".")
  636. searchstring = ""
  637. count = 0
  638. #search = K.*.9.*
  639. #match: = K.5.9.7
  640. #match: = K.5.9.*
  641. #match: = K.*.*.*
  642. #no match: = K.*.8.*
  643. iter.each do |item|
  644. if !item.nil?
  645. if item.to_s != "*"
  646. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  647. else
  648. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  649. end
  650. count += 1
  651. end
  652. end
  653. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  654. ret = []
  655. if result.length > 0
  656. result.each do |item|
  657. result2 = $db.execute "SELECT DISTINCT error FROM error WHERE station = #{@r[0]} and patternID = #{item[0]}"
  658. result2.each do |item2|
  659. ret << item2[0].to_s
  660. end
  661. end
  662. end
  663. ret = ret.uniq
  664. builder = Nokogiri::XML::Builder.new do |xml|
  665. xml.reason{
  666. if result.length > 0
  667. ret.each do |item|
  668. xml.reason(:lang => "de-at"){
  669. xml.text(item)
  670. }
  671. end
  672. end
  673. }
  674. end
  675. Riddl::Parameter::Complex.new('errors','application/xml',builder.to_xml)
  676. end
  677. end
  678. class SearchImagesReverse < Riddl::Implementation
  679. def response
  680. iter = @p[0].value.split(".")
  681. searchstring = ""
  682. count = 0
  683. #search = K.*.9.*
  684. #match: = K.5.9.7
  685. #match: = K.5.9.*
  686. #match: = K.*.*.*
  687. #no match: = K.*.8.*
  688. iter.each do |item|
  689. if !item.nil?
  690. if item.to_s != "*"
  691. searchstring = searchstring + " and (P" + count.to_s + "='*' or P" + count.to_s + " = '" + item.to_s + "')"
  692. else
  693. searchstring = searchstring + " and (P" + count.to_s + "!=''" + ")"
  694. end
  695. count += 1
  696. end
  697. end
  698. result = $db.execute "SELECT patternID FROM station WHERE station = " + @r[0] + searchstring
  699. ret = []
  700. if result.length > 0
  701. result.each do |item|
  702. result2 = $db.execute "SELECT DISTINCT station, patternID, imageID FROM image WHERE station = #{@r[0]} and patternID = #{item[0]}"
  703. result2.each do |item2|
  704. ret << item2[0].to_s + "/" + item2[1].to_s + "/" + item2[2].to_s
  705. end
  706. end
  707. end
  708. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  709. end
  710. end
  711. def createDB(opts)
  712. $db.execute("PRAGMA foreign_keys=ON");
  713. $db.execute "CREATE TABLE IF NOT EXISTS stations(station INT, name VARCHAR(20), PRIMARY KEY(station))"
  714. opts[:appconf]["stations"].each { |n|
  715. CreateStation::createDB(n)
  716. }
  717. # Unique pattern
  718. # $db.execute "CREATE TABLE IF NOT EXISTS station(station INT, patternID INT, pattern CHARACTER(20), description TEXT, date TEXT, P0 VARCHAR(2), P1 VARCHAR(2), P2 VARCHAR(2), P3 VARCHAR(2), P4 VARCHAR(2), P5 VARCHAR(2), P6 VARCHAR(2), P7 VARCHAR(2), P8 VARCHAR(2), P9 VARCHAR(2), P10 VARCHAR(2), PRIMARY KEY(station,patternID), UNIQUE(station, pattern))"
  719. $db.execute "CREATE TABLE IF NOT EXISTS station(station INT, patternID INT, pattern CHARACTER(256), description TEXT, date TEXT, P0 VARCHAR(10), P1 VARCHAR(10), P2 VARCHAR(10), P3 VARCHAR(10), P4 VARCHAR(10), P5 VARCHAR(10), P6 VARCHAR(10), P7 VARCHAR(10), P8 VARCHAR(10), P9 VARCHAR(10), P10 VARCHAR(10), PRIMARY KEY(station,patternID), UNIQUE(station, patternID))"
  720. $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE)"
  721. $db.execute "CREATE TABLE IF NOT EXISTS replacements(station INT, patternID INT, abbreviation TEXT, url TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE)"
  722. $db.execute "CREATE TABLE IF NOT EXISTS image(station INT, patternID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE,PRIMARY KEY(station,patternID,imageID,language))"
  723. # $db.execute "CREATE TABLE IF NOT EXISTS error(station INT, patternID INT, error TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID) ON DELETE CASCADE)"
  724. # $db.execute "CREATE TABLE IF NOT EXISTS image(station INT, patternID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(station) REFERENCES station(station), FOREIGN KEY(patternID) REFERENCES station(patternID) ON DELETE CASCADE,PRIMARY KEY(station,patternID,imageID,language))"
  725. end
  726. server = Riddl::Server.new(File.join(__dir__,'/was.xml'), :host => 'localhost') do |opts|
  727. accessible_description true
  728. cross_site_xhr true
  729. createDB(opts)
  730. # opts[:appconf]
  731. on resource do
  732. run GetStations if get
  733. run CreateStation if post
  734. on resource '\d+' do
  735. run GetStation if get
  736. run CreatePattern if post 'pattern'
  737. on resource '\d+' do
  738. run GetPattern if get
  739. run UpdatePattern if put 'pattern'
  740. run DeletePattern if delete
  741. run DuplicatePattern if put 'patternID'
  742. on resource 'error' do
  743. run SaveError if put 'error'
  744. run GetError if get
  745. #should add deleteerror
  746. end
  747. on resource 'replacement' do
  748. run SaveReplacement if put 'replacement'
  749. run GetReplacement if get
  750. #should add deleteReplacement
  751. end
  752. on resource 'images' do
  753. run GetImages if get
  754. #run CreateImage if post #'image'
  755. run UploadImage if post
  756. on resource 'reorder' do
  757. on resource do
  758. run ReorderImages if put 'orderlist'
  759. end
  760. end
  761. on resource '\d+' do
  762. run GetImage if get
  763. on resource do
  764. run UpdateImageLabel if put 'label'
  765. run GetRealImage if get
  766. run DeleteImage if delete
  767. end
  768. end
  769. end
  770. end
  771. on resource 'search' do
  772. run ListSearch if get
  773. on resource 'patternID' do
  774. run SearchPattern if get
  775. end
  776. on resource 'images' do
  777. run SearchImages if get
  778. on resource '\d+' do
  779. run SearchImagesSingle if get
  780. end
  781. end
  782. on resource 'imagesServerSide' do
  783. run SearchImages2 if get
  784. on resource '\d+' do
  785. run SearchImages2Single if get
  786. end
  787. end
  788. on resource 'imagesWIZ' do
  789. run SearchImages2 if get
  790. on resource '\d+' do
  791. run SearchImages3Single if get
  792. end
  793. end
  794. on resource 'errorsWIZ' do
  795. run SearchErrors if get
  796. end
  797. on resource 'imagesReverse' do
  798. run SearchImagesReverse if get
  799. end
  800. end
  801. end
  802. end
  803. end.loop!