was.rb 37 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055
  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. require 'uri'
  27. $db = SQLite3::Database.open 'database/stations.db'
  28. class GetJsonFiles < Riddl::Implementation
  29. def response
  30. Dir.chdir( __dir__ + '/json')
  31. ret = [];
  32. Dir.glob('*').select{ |e|
  33. ret.append(e)
  34. }
  35. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  36. end
  37. end
  38. class GetJson < Riddl::Implementation
  39. def response
  40. Dir.chdir( __dir__ + '/json')
  41. Riddl::Parameter::Complex.new('list','application/json',File.open(@r[1]).read) rescue Riddl::Parameter::Complex.new('list','application/json', '{"error":"No such file"}')
  42. end
  43. end
  44. class SaveJson < Riddl::Implementation
  45. def response
  46. Dir.chdir( __dir__ + '/json')
  47. File.write(@r.last,(@p[0].value.read))
  48. end
  49. end
  50. class GetStations < Riddl::Implementation
  51. def response
  52. result = $db.execute "SELECT * FROM stations"
  53. builder = Nokogiri::XML::Builder.new do |xml|
  54. xml.stations {
  55. result.each do |row|
  56. xml.station(:id => row[0]){
  57. resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{row[0]}"
  58. resultstation.each do |row|
  59. xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){
  60. xml.parts{
  61. resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}"
  62. resultpattern.each do |row2|
  63. xml.send(row2[1], row2[2])
  64. end
  65. }
  66. }
  67. end
  68. }
  69. end
  70. }
  71. end
  72. #puts builder.to_xml
  73. Riddl::Parameter::Complex.new('stations','application/xml',builder.to_xml)
  74. end
  75. end
  76. class CreateStation < Riddl::Implementation
  77. def self::createDB(n)
  78. #result = $db.execute "SELECT MAX(station) FROM stations"
  79. #if(result[0][0] == nil)
  80. # $db.execute "INSERT INTO stations (station) VALUES (?)", 0
  81. #else
  82. # $db.execute "INSERT INTO stations (station) VALUES (?)", result[0][0]+1
  83. #end
  84. #$db.execute ("INSERT INTO stations (station, name) VALUES (?,?)", [n,n])
  85. $db.execute("INSERT OR IGNORE INTO stations (station, name) VALUES (?,?)", [n,n])
  86. end
  87. def response
  88. GetStation::createDB
  89. end
  90. end
  91. class GetStation < Riddl::Implementation
  92. def self::prepare(id)
  93. resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{id}"
  94. Nokogiri::XML::Builder.new do |xml|
  95. xml.station(:id => id){
  96. resultstation.each do |row|
  97. xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){
  98. xml.parts{
  99. resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}"
  100. resultpattern.each do |row2|
  101. xml.send(row2[1], row2[2])
  102. end
  103. }
  104. }
  105. end
  106. }
  107. end
  108. end
  109. def response
  110. builder = GetStation::prepare(@r.last)
  111. Riddl::Parameter::Complex.new('station','application/xml',builder.to_xml)
  112. end
  113. end
  114. class CreatePattern < Riddl::Implementation
  115. def response
  116. doc = Nokogiri::XML(@p[0].value)
  117. result = $db.execute "SELECT MAX(internalID) FROM stationpattern"
  118. if(result[0][0] == nil)
  119. id = 0
  120. else
  121. id = result[0][0] +1
  122. end
  123. $db.execute("INSERT INTO stationpattern (station, internalID, description, date) VALUES (?,?,?,?)", [@r.last, id, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value])
  124. doc.xpath(".//parts/*").each do |node|
  125. #if(node.text != "")
  126. $db.execute("INSERT INTO pattern (internalID, type, value) VALUES (?,?,?)", [id, node.name, node.text])
  127. #end
  128. end
  129. ret = {:id => id}
  130. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  131. end
  132. end
  133. class GetPattern < Riddl::Implementation
  134. def response
  135. resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{@r[0]} AND internalID = #{@r[1]}"
  136. builder = Nokogiri::XML::Builder.new do |xml|
  137. resultstation.each do |row|
  138. xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){
  139. xml.parts{
  140. resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}"
  141. resultpattern.each do |row2|
  142. xml.send(row2[1], row2[2])
  143. end
  144. }
  145. }
  146. end
  147. end
  148. Riddl::Parameter::Complex.new('pattern','application/xml',builder.to_xml)
  149. end
  150. end
  151. class UpdatePattern < Riddl::Implementation
  152. def response
  153. doc = Nokogiri::XML(@p[0].value)
  154. $db.execute("Update stationpattern SET description = ?, date = ? WHERE internalID = ?", [doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, @r[1]])
  155. $db.execute("DELETE FROM pattern WHERE internalID = ?", [@r[1]])
  156. doc.xpath(".//parts/*").each do |node|
  157. #if(node.text != "")
  158. $db.execute("INSERT INTO pattern (internalID, type, value) VALUES (?,?,?)", [@r[1], node.name, node.text])
  159. #end
  160. end
  161. end
  162. end
  163. class DeletePattern < Riddl::Implementation
  164. def response
  165. $db.execute("DELETE FROM stationpattern WHERE internalID = ?", [@r[1]])
  166. end
  167. end
  168. class DuplicatePattern < Riddl::Implementation
  169. def response
  170. #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.
  171. result = $db.execute "SELECT MAX(internalID) FROM stationpattern"
  172. if(result[0][0] == nil)
  173. id = 0
  174. else
  175. id = result[0][0] +1
  176. end
  177. result = $db.execute "SELECT * FROM stationpattern WHERE internalID = #{@r[1]}"
  178. result[0][1] = id;
  179. $db.execute( "INSERT INTO stationpattern values (?,?,?,?)", result[0])
  180. result = $db.execute "SELECT * FROM pattern WHERE internalID = #{@r[1]}"
  181. result.each do |row|
  182. row[0] = id;
  183. $db.execute( "INSERT INTO pattern values (?,?,?)", row)
  184. end
  185. #duplicate error
  186. #$db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  187. result = $db.execute "SELECT * FROM error WHERE internalID = #{@r[1]}"
  188. if(result[0] != nil)
  189. result.each do |row|
  190. row[0] = id;
  191. questionmarks = ""
  192. row.length().times{questionmarks = questionmarks + ",?"}
  193. questionmarks[0] = ""
  194. $db.execute( "INSERT INTO error values (#{questionmarks})", row)
  195. end
  196. end
  197. #duplicate image db
  198. #$db.execute "CREATE TABLE IF NOT EXISTS image(internalID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  199. result = $db.execute "SELECT * FROM image WHERE internalID = #{@r[1]}"
  200. if(result[0] != nil)
  201. result.each do |row|
  202. row[0] = id;
  203. questionmarks = ""
  204. row.length().times{questionmarks = questionmarks + ",?"}
  205. questionmarks[0] = ""
  206. $db.execute( "INSERT INTO image values (#{questionmarks})", row)
  207. end
  208. end
  209. #duplicate images
  210. pathorigin = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
  211. pathdestination = File.join(File.dirname(__dir__),'images/uploads', @r[0], id.to_s)
  212. if (Dir.exist?(pathorigin))
  213. FileUtils.mkdir_p(pathdestination)
  214. FileUtils.copy_entry pathorigin, pathdestination
  215. end
  216. #duplicate Replacement db
  217. #$db.execute "CREATE TABLE IF NOT EXISTS replacements(internalID INT, abbreviation TEXT, url TEXT, ordering INT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  218. result = $db.execute "SELECT * FROM replacements WHERE internalID = #{@r[1]}"
  219. if(result[0] != nil)
  220. result.each do |row|
  221. row[0] = id;
  222. questionmarks = ""
  223. row.length().times{questionmarks = questionmarks + ",?"}
  224. questionmarks[0] = ""
  225. $db.execute( "INSERT INTO replacements values (#{questionmarks})", row)
  226. end
  227. end
  228. ret = {:id => id}
  229. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  230. end
  231. end
  232. class SaveError < Riddl::Implementation
  233. def response
  234. $db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  235. #Delete all, afterwards insert
  236. $db.execute("DELETE FROM error WHERE internalID = ?", [@r[1]])
  237. doc = Nokogiri::XML(@p[0].value)
  238. doc.xpath(".//reason").each do |node|
  239. $db.execute("INSERT INTO error (internalID, error) VALUES (?,?)", [@r[1], node.text])
  240. end
  241. end
  242. end
  243. class GetError < Riddl::Implementation
  244. def response
  245. result = $db.execute "SELECT * FROM error WHERE internalID = #{@r[1]}"
  246. builder = Nokogiri::XML::Builder.new do |xml|
  247. xml.error(){
  248. result.each do |row|
  249. xml.reason row[1]
  250. end
  251. }
  252. end
  253. Riddl::Parameter::Complex.new('error','application/xml',builder.to_xml)
  254. end
  255. end
  256. class SaveReplacement < Riddl::Implementation
  257. def response
  258. # $db.execute "CREATE TABLE IF NOT EXISTS replacements(internalID INT, abbreviation TEXT, url TEXT, ordering INT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  259. #Delete all, afterwards insert
  260. $db.execute("DELETE FROM replacements WHERE internalID = ?", [@r[1]])
  261. order = 0;
  262. doc = Nokogiri::XML(@p[0].value)
  263. doc.xpath(".//item").each do |node|
  264. $db.execute("INSERT INTO replacements (internalID, abbreviation, url, ordering) VALUES (?,?,?,?)", [@r[1], node.xpath(".//abbreviation").text, node.xpath(".//url").text, order])
  265. order += 1
  266. end
  267. end
  268. end
  269. class GetReplacement < Riddl::Implementation
  270. def response
  271. result = $db.execute "SELECT * FROM replacements WHERE internalID = #{@r[1]} ORDER BY ordering ASC"
  272. builder = Nokogiri::XML::Builder.new do |xml|
  273. xml.replacement(){
  274. result.each do |row|
  275. xml.item(){
  276. xml.abbreviation row[1]
  277. xml.url row[2]
  278. }
  279. end
  280. }
  281. end
  282. Riddl::Parameter::Complex.new('replacement','application/xml',builder.to_xml)
  283. end
  284. end
  285. class GetImages < Riddl::Implementation
  286. def response
  287. formatted_no_decl = Nokogiri::XML::Node::SaveOptions::FORMAT +
  288. Nokogiri::XML::Node::SaveOptions::NO_DECLARATION
  289. #$db.execute "CREATE TABLE IF NOT EXISTS image(internalID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  290. result = $db.execute "SELECT DISTINCT imageID FROM image WHERE internalID = #{@r[1]} ORDER BY imageID ASC"
  291. builder = Nokogiri::XML::Builder.new do |xml|
  292. xml.images(){
  293. result.each do |row|
  294. xml<< GetImage::prepare(@r[0], @r[1], row[0], @a[0][:appconf]["wasurl"]).to_xml( save_with:formatted_no_decl )
  295. end
  296. }
  297. end
  298. Riddl::Parameter::Complex.new('images','application/xml',builder.to_xml)
  299. end
  300. end
  301. class UploadImage < Riddl::Implementation
  302. def response
  303. lang = @p[@p.length-1].value
  304. #$db.execute "CREATE TABLE IF NOT EXISTS image(internalID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  305. result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{lang}'"
  306. if(result[0][0] == nil)
  307. id = 0
  308. else
  309. id = result[0][0] +1
  310. end
  311. #puts JSON.pretty_generate(@p)
  312. i = 0
  313. while i < @p.length-1 do
  314. item = @p[i]
  315. if(item != nil && item.name == "files[]")
  316. path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s)
  317. FileUtils.mkdir_p(path)
  318. #juergen nach alternative fragen
  319. readFile = File.read(item.value.inspect.to_s[/Tempfile:(.*?)>/m, 1])
  320. File.open(File.join(path, lang + ".svg"), 'wb') do |file|
  321. file.write(readFile.to_s)
  322. end
  323. $db.execute("INSERT INTO image (internalID, imageID, language, label) VALUES (?,?,?,?)", [@r[1], id, lang, "Label"])
  324. id += 1
  325. end
  326. i +=1
  327. end
  328. end
  329. end
  330. class AddExternalImage < Riddl::Implementation
  331. def response
  332. doc = Nokogiri::XML(@p[0].value)
  333. url = doc.xpath("/externalImage/url").text
  334. lang = doc.xpath("/externalImage/lang").text
  335. result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{lang}'"
  336. if(result[0][0] == nil)
  337. id = 0
  338. else
  339. id = result[0][0] +1
  340. end
  341. #Create link file (we create a file so moving/deleting works always the same and the database does not have to be changed for external files) only thing that had to be changed was imgReplacement.php
  342. image = '<svg width="100%" height="100%" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" overflow="hidden"> <image href="' + url +'" height="100%" width="100%"/>
  343. </svg>'
  344. path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s)
  345. FileUtils.mkdir_p(path)
  346. File.open(File.join(path, lang + ".svg"), 'wb') do |file|
  347. file.write(image)
  348. end
  349. #Create DB entry as usual
  350. $db.execute("INSERT INTO image (internalID, imageID, language, label) VALUES (?,?,?,?)", [@r[1], id, lang, url])
  351. end
  352. end
  353. class ReorderImages < Riddl::Implementation
  354. def response
  355. #remove brackets
  356. iter = @p[0].value.read.chop
  357. iter[0] =""
  358. iter = iter.split(",").map(&:to_i)
  359. i = 0
  360. while i < iter.length do
  361. if(i != iter[i])
  362. #swap
  363. path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], i.to_s)
  364. path2 = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], iter[i].to_s)
  365. tmp = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1])
  366. FileUtils.mv(path + "/" + @r[4] + ".svg", tmp)
  367. FileUtils.mv(path2 + "/" + @r[4] + ".svg", path)
  368. FileUtils.mv(tmp + "/" + @r[4] + ".svg", path2)
  369. #DB Swap
  370. result = $db.execute "SELECT MAX(imageID) FROM image"
  371. if(result[0][0] == nil)
  372. maxImgId = 0
  373. else
  374. maxImgId = result[0][0] +1
  375. end
  376. #Wegen 999999 fragen
  377. #nutze max ID+1 zum tauschen
  378. $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [maxImgId, @r[1], i, @r[4]])
  379. $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [i, @r[1], iter[i], @r[4]])
  380. $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [iter[i], @r[1], maxImgId, @r[4]])
  381. iter.map! do |item|
  382. if(item == i)
  383. iter[i]
  384. else
  385. item
  386. end
  387. end
  388. end
  389. i +=1
  390. end
  391. end
  392. end
  393. class GetImage < Riddl::Implementation
  394. def self::prepare(station, pattern, imageID, wasurl)
  395. result = $db.execute "SELECT * FROM image WHERE internalID = #{pattern} and imageID = #{imageID}"
  396. Nokogiri::XML::Builder.new do |xml|
  397. xml.image(:id => imageID){
  398. result.each do |row|
  399. xml.variant(:lang => row[2], :label => row[3]){
  400. xml.text(wasurl + "images/uploads/#{station}/#{pattern}/#{imageID}/#{row[3]}.svg")
  401. }
  402. end
  403. }
  404. end
  405. end
  406. def response
  407. builder = GetImage::prepare(@r[0], @r[1], @r[3], @a[0][:appconf]["wasurl"])
  408. Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
  409. end
  410. end
  411. class UpdateImageLabel < Riddl::Implementation
  412. def response
  413. $db.execute("UPDATE image SET label = ? WHERE internalID = ? AND imageID = ? AND language = ?", [@p[0].value, @r[1], @r[3], @r[4]])
  414. end
  415. end
  416. #Not used, prototype for replacing image serverside
  417. class GetRealImage < Riddl::Implementation
  418. def response
  419. #split on "." and tacke [0] to allow for e.g. de-at.svg
  420. #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
  421. #https://centurio.work/customers/evva/was/server/0/0/images/3/de-at?video=xyz
  422. img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3], @r[4].split(".")[0] + ".svg"))
  423. if false #Currently replacements are done on the client side
  424. if(@p[0].nil?)
  425. puts "Undefined p"
  426. else
  427. if(@p[0].name == "video" && @p[0].value)
  428. xml = Nokogiri.parse img
  429. puts "Width " + xml.xpath("string(//xmlns:image/@width)")
  430. puts "Height " + xml.xpath("string(//xmlns:image/@height)")
  431. #puts "Posi " + xml.xpath("string(//xmlns:clipPath/path/@height)")
  432. puts "Posi " + xml.xpath("string(//xmlns:image/following-sibling::clipPath/@id)")
  433. puts xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "")
  434. # text
  435. img = img.sub! "</svg>", '
  436. <g><g transform="translate(562,288),scale(1.35185 1.35185)">
  437. <foreignObject width="480" height="270">
  438. <video width="480" height="270" controls="" autoplay="autoplay" muted="muted" xmlns="http://www.w3.org/1999/xhtml" >
  439. <source src="' + xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "") +'" type="video/mp4"/>
  440. </video>
  441. </foreignObject>
  442. </g></g></svg>'
  443. else
  444. @p.each do |item|
  445. unless item.name.nil? || item.value.nil?
  446. img.sub! item.name, item.value
  447. end
  448. end
  449. end
  450. end
  451. end
  452. Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img)
  453. end
  454. end
  455. class DeleteImage < Riddl::Implementation
  456. def response
  457. 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"))
  458. $db.execute("DELETE FROM image WHERE internalID = ? AND imageID = ? AND language = ?", [@r[1], @r[3], @r[4]])
  459. result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{@r[4]}'"
  460. if(result[0][0] == nil)
  461. id = 0
  462. else
  463. id = result[0][0] +1
  464. end
  465. #reorder if !end gets deleted
  466. if(id == @r[3])
  467. return
  468. else
  469. cur = @r[3].to_i + 1
  470. prev = @r[3].to_i
  471. while cur < id.to_i do
  472. $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [prev, @r[1], cur, @r[4]])
  473. src = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], cur.to_s, @r[4] + ".svg")
  474. target = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], prev.to_s, @r[4] + ".svg")
  475. FileUtils.mv(src, target)
  476. cur+=1
  477. prev += 1
  478. end
  479. end
  480. end
  481. end
  482. class ListSearch < Riddl::Implementation
  483. def response
  484. ret = {
  485. :imagesWIZAll => "/imagesWIZ?pattern=...",
  486. :imagesWIZSingle => "/imagesWIZ/0..*/?pattern=...",
  487. :errorsWIZ => "/errorsWIZ/"
  488. }
  489. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  490. end
  491. end
  492. class SearchImages < Riddl::Implementation
  493. def response
  494. #https://centurio.work/customers/evva/was/ui/TransformProductCode.php?ProductCode=E.A.PZ.DZ-S.31.31.MP.SEP
  495. pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value))
  496. #Get first key dynamicly and then build sql query
  497. searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID"
  498. searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ')
  499. searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ')
  500. searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC"
  501. result = $db.execute searchstring;
  502. puts searchstring
  503. #Search for images with one or multiple found patternIDs
  504. ret = []
  505. if result.length > 0
  506. result.each do |item|
  507. result2 = $db.execute "SELECT DISTINCT internalID, imageID FROM image WHERE internalID = #{item[0]} ORDER BY imageID ASC"
  508. result2.each do |item2|
  509. result3 = $db.execute "SELECT DISTINCT station FROM stationpattern WHERE internalID = #{item2[0]} AND station = #{@r[0]}"
  510. if(result3[0] != nil)
  511. ret << @r[0] + "/" + item2[0].to_s + "/" + item2[1].to_s
  512. end
  513. end
  514. end
  515. end
  516. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  517. end
  518. end
  519. class SearchImagesSingle < Riddl::Implementation
  520. def response
  521. pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value))
  522. searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID"
  523. searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ')
  524. searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ')
  525. searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC"
  526. result = $db.execute searchstring;
  527. count = 0
  528. pattern = 0
  529. image = 0
  530. builder = Nokogiri::XML::Builder.new do |xml|
  531. xml.image(:id => @r[3].to_s){
  532. if result.length > 0
  533. result.each do |item|
  534. result2 = $db.execute "SELECT DISTINCT internalID, imageID FROM image WHERE internalID = #{item[0]} ORDER BY imageID ASC"
  535. result2.each do |item2|
  536. result3 = $db.execute "SELECT DISTINCT internalID, imageID, language, label FROM image WHERE internalID = #{item[0]} and imageID =#{item2[1]}"
  537. result3.each do |item3|
  538. result4 = $db.execute "SELECT DISTINCT station FROM stationpattern WHERE internalID = #{item3[0]} AND station = #{@r[0]}"
  539. if(result4[0] != nil)
  540. if(count == @r[3].to_i)
  541. #ret << @r[0] + "/" + item2[0].to_s + "/" + item2[1].to_s
  542. uri = @a[0][:appconf]["wasurl"] + "ui/imageReplacement.php?___image___=" + @r[0].to_s + "/" + item3[0].to_s + "/" + item3[1].to_s + "/" + item3[2].to_s + ".svg"
  543. @p.each_with_index do |item, index|
  544. if index != 0
  545. uri += "&" + item.name.to_s + "=" + item.value.to_s
  546. end
  547. end
  548. xml.variant(:lang => item3[2].to_s, :label => item3[3].to_s){
  549. xml.text(uri)
  550. }
  551. end
  552. count += 1
  553. end
  554. end
  555. end
  556. end
  557. end
  558. }
  559. end
  560. #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg
  561. #puts builder.to_xml
  562. Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml)
  563. end
  564. end
  565. class SearchErrors < Riddl::Implementation
  566. def response
  567. pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value))
  568. searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID"
  569. searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ')
  570. searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ')
  571. searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC"
  572. result = $db.execute searchstring;
  573. ret = []
  574. if result.length > 0
  575. result.each do |item|
  576. result2 = $db.execute "SELECT DISTINCT error FROM error WHERE internalID = #{item[0]} ORDER BY internalID ASC"
  577. result2.each do |item2|
  578. ret << item2[0].to_s
  579. end
  580. end
  581. end
  582. ret = ret.uniq
  583. builder = Nokogiri::XML::Builder.new do |xml|
  584. xml.reason{
  585. if result.length > 0
  586. ret.each do |item|
  587. xml.reason(:lang => "de-at"){
  588. xml.text(item)
  589. }
  590. end
  591. end
  592. }
  593. end
  594. Riddl::Parameter::Complex.new('errors','application/xml',builder.to_xml)
  595. end
  596. end
  597. class GetAssignments < Riddl::Implementation
  598. def response
  599. ret = {}
  600. resultstation = $db.execute "SELECT * FROM assignments"
  601. resultstation.each do |row|
  602. ret[row[0]] = row[1]
  603. end
  604. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  605. end
  606. end
  607. class SaveNewAssignment < Riddl::Implementation
  608. def response
  609. form = JSON.parse(@p[0].value.read);
  610. result = $db.execute "SELECT MAX(ID) FROM assignments"
  611. if(result[0][0] == nil)
  612. id = 0
  613. else
  614. id = result[0][0] +1
  615. end
  616. checkimage = $db.execute "SELECT * FROM assignments WHERE image = '#{form["img"]}'"
  617. #if image not stored create new assignment with pattern, otherwise only add pattern
  618. if(checkimage[0] == nil)
  619. $db.execute("INSERT INTO assignments (ID, image) VALUES (?,?)", [id, form["img"]])
  620. form["parts"].each{ |key,val|
  621. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, key, val])
  622. }
  623. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, "STATION", form["station"]])
  624. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, "TEXT", form["assignmentText"]])
  625. else
  626. maxID = $db.execute "SELECT MAX(AssID) FROM patternassignments WHERE ID ='#{checkimage[0][0]}'"
  627. if(maxID[0][0] == nil)
  628. maxID = 0
  629. else
  630. maxID = maxID[0][0] +1
  631. end
  632. form["parts"].each{ |key,val|
  633. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, key, val])
  634. }
  635. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, "STATION", form["station"]])
  636. $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, "TEXT", form["assignmentText"]])
  637. end
  638. end
  639. end
  640. class GetAssignment < Riddl::Implementation
  641. def response
  642. ret = {}
  643. allAssIds = $db.execute "SELECT DISTINCT AssID FROM patternassignments WHERE ID = #{@r[1]}"
  644. allAssIds.each do |row|
  645. station = $db.execute "SELECT value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type == 'STATION'"
  646. stationvalue = Integer(station[0][0])
  647. #old where pattern is stored as string
  648. #singleItem = $db.execute "SELECT value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type != 'STATION'"
  649. #if(ret[stationvalue] == nil)
  650. # ret[stationvalue] = Hash.new()
  651. #end
  652. #ret[stationvalue][row[0]] = singleItem.join('.')
  653. singleItem = $db.execute "SELECT type, value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type != 'STATION'"
  654. if(ret[stationvalue] == nil)
  655. ret[stationvalue] = Hash.new()
  656. end
  657. ret[stationvalue][row[0]] = Hash.new()
  658. singleItem.each do |it|
  659. ret[stationvalue][row[0]][it[0]] = it[1]
  660. end
  661. end
  662. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  663. end
  664. end
  665. class DeleteAssignment < Riddl::Implementation
  666. def response
  667. $db.execute("DELETE FROM assignments WHERE ID = ?", [@r[1]])
  668. end
  669. end
  670. class DeleteSingleAssignment < Riddl::Implementation
  671. def response
  672. $db.execute("DELETE FROM patternassignments WHERE AssID = ? AND ID = ?", [@r[2], @r[1]])
  673. end
  674. end
  675. class ListAssignmentSearch < Riddl::Implementation
  676. def response
  677. #https://centurio.work/customers/evva/was/ui/TransformProductCode.php?ProductCode=E.A.PZ.DZ-S.31.31.MP.SEP
  678. pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value))
  679. #$db.execute "CREATE TABLE IF NOT EXISTS patternassignments(ID INT, AssID INT, type CHARACTER(20), value CHARACTER(20), FOREIGN KEY(ID) REFERENCES assignments(ID) ON DELETE CASCADE)"
  680. #Get first key dynamicly and then build sql query
  681. if(pattern.keys[0] != nil)
  682. searchstring = "SELECT " + pattern.keys[0] + ".ID AS ID"
  683. searchstring = searchstring + " FROM " + pattern.map{ |key, val| "patternassignments " + key}.join(', ')
  684. searchstring = searchstring + " , patternassignments STATION , patternassignments ValText"
  685. searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".AssID == " + key + ".AssID"}.join(' AND ') + " AND ";
  686. searchstring = searchstring + pattern.map{ |key, val| pattern.keys[0] + ".ID == " + key + ".ID"}.join(' AND ')
  687. searchstring = searchstring + " AND " + pattern.keys[0] + ".AssID == STATION.AssID"
  688. searchstring = searchstring + " AND " + pattern.keys[0] + ".AssID == ValText.AssID"
  689. searchstring = searchstring + " AND " + pattern.keys[0] + ".ID == STATION.ID"
  690. searchstring = searchstring + " AND " + pattern.keys[0] + ".ID == ValText.ID"
  691. searchstring = searchstring + " AND STATION.type == 'STATION'" + " AND STATION.value == '" + @p[1].value + "'"
  692. searchstring = searchstring + " AND ValText.type == 'TEXT'" + " AND ValText.value == '" + @p[2].value + "'"
  693. searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC"
  694. #puts searchstring;
  695. else
  696. searchstring = "SELECT * FROM "
  697. searchstring = searchstring + "patternassignments STATION , patternassignments ValText"
  698. searchstring = searchstring + " WHERE"
  699. searchstring = searchstring + " STATION.type == 'STATION'" + " AND STATION.value == '" + @p[1].value + "'"
  700. searchstring = searchstring + " AND ValText.type == 'TEXT'" + " AND ValText.value == '" + @p[2].value + "'"
  701. searchstring = searchstring + " AND ValText.AssID == STATION.AssID"
  702. searchstring = searchstring + " ORDER BY ID ASC"
  703. end
  704. result = $db.execute searchstring;
  705. #Search for images with one or multiple found patternIDs
  706. ret = []
  707. if result.length > 0
  708. result.each do |item|
  709. result2 = $db.execute "SELECT DISTINCT image FROM assignments WHERE ID = #{item[0]}"
  710. result2.each do |item2|
  711. ret << item2[0]
  712. end
  713. end
  714. end
  715. Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
  716. end
  717. end
  718. def createDB(opts)
  719. $db.execute("PRAGMA foreign_keys=ON");
  720. $db.execute "CREATE TABLE IF NOT EXISTS stations(station INT, name VARCHAR(20), PRIMARY KEY(station))"
  721. opts[:appconf]["stations"].each { |n|
  722. CreateStation::createDB(n)
  723. }
  724. #vor DB struktur umstellung
  725. #$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))"
  726. #$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)"
  727. #$db.execute "CREATE TABLE IF NOT EXISTS replacements(station INT, patternID INT, abbreviation TEXT, url TEXT, ordering INT, FOREIGN KEY(station, patternID) REFERENCES station(station, patternID) ON DELETE CASCADE)"
  728. #$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))"
  729. $db.execute "CREATE TABLE IF NOT EXISTS stationpattern(station INT, internalID INT, description TEXT, date TEXT, PRIMARY KEY(internalID), UNIQUE(internalID))"
  730. $db.execute "CREATE TABLE IF NOT EXISTS pattern(internalID INT, type CHARACTER(20), value CHARACTER(20), FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  731. $db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  732. $db.execute "CREATE TABLE IF NOT EXISTS replacements(internalID INT, abbreviation TEXT, url TEXT, ordering INT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  733. $db.execute "CREATE TABLE IF NOT EXISTS image(internalID INT, imageID INT, language CHARACTER(20), label TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)"
  734. $db.execute "CREATE TABLE IF NOT EXISTS assignments(ID INT, image TEXT, PRIMARY KEY(ID) UNIQUE (ID))"
  735. $db.execute "CREATE TABLE IF NOT EXISTS patternassignments(ID INT, AssID INT, type CHARACTER(20), value CHARACTER(20), FOREIGN KEY(ID) REFERENCES assignments(ID) ON DELETE CASCADE)"
  736. end
  737. server = Riddl::Server.new(File.join(__dir__,'/was.xml'), :host => 'localhost') do |opts|
  738. accessible_description true
  739. cross_site_xhr true
  740. createDB(opts)
  741. # opts[:appconf]
  742. on resource do
  743. run GetStations if get
  744. run CreateStation if post
  745. on resource 'cutimage' do
  746. run CutImage if get
  747. #should add deleteReplacement
  748. end
  749. on resource 'json' do
  750. run GetJsonFiles if get
  751. on resource '.*' do
  752. run SaveJson if put 'list'
  753. run GetJson if get
  754. end
  755. #should add deleteReplacement
  756. end
  757. on resource '\d+' do
  758. run GetStation if get
  759. run CreatePattern if post 'pattern'
  760. on resource '\d+' do
  761. run GetPattern if get
  762. run UpdatePattern if put 'pattern'
  763. run DeletePattern if delete
  764. run DuplicatePattern if put 'patternID'
  765. on resource 'error' do
  766. run SaveError if put 'error'
  767. run GetError if get
  768. #should add deleteerror
  769. end
  770. on resource 'replacement' do
  771. run SaveReplacement if put 'replacement'
  772. run GetReplacement if get
  773. #should add deleteReplacement
  774. end
  775. on resource 'images' do
  776. run GetImages, opts if get
  777. #run CreateImage if post #'image'
  778. run AddExternalImage if post 'externalImage'
  779. run UploadImage if post
  780. on resource 'reorder' do
  781. on resource do
  782. run ReorderImages if put 'orderlist'
  783. end
  784. end
  785. on resource '\d+' do
  786. run GetImage, opts if get
  787. on resource do
  788. run UpdateImageLabel if put 'label'
  789. run GetRealImage if get
  790. run DeleteImage if delete
  791. end
  792. end
  793. end
  794. end
  795. on resource 'search' do
  796. run ListSearch if get
  797. on resource 'imagesWIZ' do
  798. run SearchImages, opts if get
  799. on resource '\d+' do
  800. run SearchImagesSingle, opts if get
  801. end
  802. end
  803. on resource 'errorsWIZ' do
  804. run SearchErrors, opts if get
  805. end
  806. end
  807. end
  808. on resource 'assignments' do
  809. run GetAssignments if get
  810. run SaveNewAssignment if post 'list'
  811. on resource 'search' do
  812. run ListAssignmentSearch, opts if get
  813. end
  814. on resource '\d+' do
  815. run GetAssignment if get
  816. run DeleteAssignment if delete
  817. on resource '\d+' do
  818. run DeleteSingleAssignment if delete
  819. end
  820. end
  821. end
  822. end
  823. end.loop!