#!/usr/bin/ruby # # This file is part of centurio.work/ing/commands. # # centurio.work/ing/commands is free software: you can redistribute it and/or # modify it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or (at your # option) any later version. # # centurio.work/ing/commands is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General # Public License for more details. # # You should have received a copy of the GNU General Public License along with # centurio.work/ing/commands (file COPYING in the main directory). If not, see # . require 'rubygems' require 'json' require 'riddl/server' require 'riddl/client' require 'fileutils' require 'nokogiri' require 'sqlite3' require 'net/http' require 'uri' $db = SQLite3::Database.open 'database/stations.db' class GetJsonFiles < Riddl::Implementation def response Dir.chdir( __dir__ + '/json') ret = []; Dir.glob('*').select{ |e| ret.append(e) } Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class GetJson < Riddl::Implementation def response Dir.chdir( __dir__ + '/json') Riddl::Parameter::Complex.new('list','application/json',File.open(@r[1]).read) rescue Riddl::Parameter::Complex.new('list','application/json', '{"error":"No such file"}') end end class SaveJson < Riddl::Implementation def response Dir.chdir( __dir__ + '/json') File.write(@r.last,(@p[0].value.read)) end end class GetStations < Riddl::Implementation def response result = $db.execute "SELECT * FROM stations" builder = Nokogiri::XML::Builder.new do |xml| xml.stations { result.each do |row| xml.station(:id => row[0]){ resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{row[0]}" resultstation.each do |row| xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){ xml.parts{ resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}" resultpattern.each do |row2| xml.send(row2[1], row2[2]) end } } end } end } end #puts builder.to_xml Riddl::Parameter::Complex.new('stations','application/xml',builder.to_xml) end end class CreateStation < Riddl::Implementation def self::createDB(n) #result = $db.execute "SELECT MAX(station) FROM stations" #if(result[0][0] == nil) # $db.execute "INSERT INTO stations (station) VALUES (?)", 0 #else # $db.execute "INSERT INTO stations (station) VALUES (?)", result[0][0]+1 #end #$db.execute ("INSERT INTO stations (station, name) VALUES (?,?)", [n,n]) $db.execute("INSERT OR IGNORE INTO stations (station, name) VALUES (?,?)", [n,n]) end def response GetStation::createDB end end class GetStation < Riddl::Implementation def self::prepare(id) resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{id}" Nokogiri::XML::Builder.new do |xml| xml.station(:id => id){ resultstation.each do |row| xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){ xml.parts{ resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}" resultpattern.each do |row2| xml.send(row2[1], row2[2]) end } } end } end end def response builder = GetStation::prepare(@r.last) Riddl::Parameter::Complex.new('station','application/xml',builder.to_xml) end end class CreatePattern < Riddl::Implementation def response doc = Nokogiri::XML(@p[0].value) result = $db.execute "SELECT MAX(internalID) FROM stationpattern" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end $db.execute("INSERT INTO stationpattern (station, internalID, description, date) VALUES (?,?,?,?)", [@r.last, id, doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value]) doc.xpath(".//parts/*").each do |node| #if(node.text != "") $db.execute("INSERT INTO pattern (internalID, type, value) VALUES (?,?,?)", [id, node.name, node.text]) #end end ret = {:id => id} Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class GetPattern < Riddl::Implementation def response resultstation = $db.execute "SELECT * FROM stationpattern WHERE station = #{@r[0]} AND internalID = #{@r[1]}" builder = Nokogiri::XML::Builder.new do |xml| resultstation.each do |row| xml.pattern(:id => row[1], :description => row[2], :changed => row[3]){ xml.parts{ resultpattern = $db.execute "SELECT * FROM pattern WHERE internalID = #{row[1]}" resultpattern.each do |row2| xml.send(row2[1], row2[2]) end } } end end Riddl::Parameter::Complex.new('pattern','application/xml',builder.to_xml) end end class UpdatePattern < Riddl::Implementation def response doc = Nokogiri::XML(@p[0].value) $db.execute("Update stationpattern SET description = ?, date = ? WHERE internalID = ?", [doc.xpath("/*/@description")[0].value, doc.xpath("/*/@changed")[0].value, @r[1]]) $db.execute("DELETE FROM pattern WHERE internalID = ?", [@r[1]]) doc.xpath(".//parts/*").each do |node| #if(node.text != "") $db.execute("INSERT INTO pattern (internalID, type, value) VALUES (?,?,?)", [@r[1], node.name, node.text]) #end end end end class DeletePattern < Riddl::Implementation def response $db.execute("DELETE FROM stationpattern WHERE internalID = ?", [@r[1]]) end end class DuplicatePattern < Riddl::Implementation def response #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. result = $db.execute "SELECT MAX(internalID) FROM stationpattern" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end result = $db.execute "SELECT * FROM stationpattern WHERE internalID = #{@r[1]}" result[0][1] = id; $db.execute( "INSERT INTO stationpattern values (?,?,?,?)", result[0]) result = $db.execute "SELECT * FROM pattern WHERE internalID = #{@r[1]}" result.each do |row| row[0] = id; $db.execute( "INSERT INTO pattern values (?,?,?)", row) end #duplicate error #$db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)" result = $db.execute "SELECT * FROM error WHERE internalID = #{@r[1]}" if(result[0] != nil) result.each do |row| row[0] = id; questionmarks = "" row.length().times{questionmarks = questionmarks + ",?"} questionmarks[0] = "" $db.execute( "INSERT INTO error values (#{questionmarks})", row) end end #duplicate image db #$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)" result = $db.execute "SELECT * FROM image WHERE internalID = #{@r[1]}" if(result[0] != nil) result.each do |row| row[0] = id; questionmarks = "" row.length().times{questionmarks = questionmarks + ",?"} questionmarks[0] = "" $db.execute( "INSERT INTO image values (#{questionmarks})", row) end end #duplicate images pathorigin = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1]) pathdestination = File.join(File.dirname(__dir__),'images/uploads', @r[0], id.to_s) if (Dir.exist?(pathorigin)) FileUtils.mkdir_p(pathdestination) FileUtils.copy_entry pathorigin, pathdestination end #duplicate Replacement db #$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)" result = $db.execute "SELECT * FROM replacements WHERE internalID = #{@r[1]}" if(result[0] != nil) result.each do |row| row[0] = id; questionmarks = "" row.length().times{questionmarks = questionmarks + ",?"} questionmarks[0] = "" $db.execute( "INSERT INTO replacements values (#{questionmarks})", row) end end ret = {:id => id} Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class SaveError < Riddl::Implementation def response $db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)" #Delete all, afterwards insert $db.execute("DELETE FROM error WHERE internalID = ?", [@r[1]]) doc = Nokogiri::XML(@p[0].value) doc.xpath(".//reason").each do |node| $db.execute("INSERT INTO error (internalID, error) VALUES (?,?)", [@r[1], node.text]) end end end class GetError < Riddl::Implementation def response result = $db.execute "SELECT * FROM error WHERE internalID = #{@r[1]}" builder = Nokogiri::XML::Builder.new do |xml| xml.error(){ result.each do |row| xml.reason row[1] end } end Riddl::Parameter::Complex.new('error','application/xml',builder.to_xml) end end class SaveReplacement < Riddl::Implementation def response # $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)" #Delete all, afterwards insert $db.execute("DELETE FROM replacements WHERE internalID = ?", [@r[1]]) order = 0; doc = Nokogiri::XML(@p[0].value) doc.xpath(".//item").each do |node| $db.execute("INSERT INTO replacements (internalID, abbreviation, url, ordering) VALUES (?,?,?,?)", [@r[1], node.xpath(".//abbreviation").text, node.xpath(".//url").text, order]) order += 1 end end end class GetReplacement < Riddl::Implementation def response result = $db.execute "SELECT * FROM replacements WHERE internalID = #{@r[1]} ORDER BY ordering ASC" builder = Nokogiri::XML::Builder.new do |xml| xml.replacement(){ result.each do |row| xml.item(){ xml.abbreviation row[1] xml.url row[2] } end } end Riddl::Parameter::Complex.new('replacement','application/xml',builder.to_xml) end end class GetImages < Riddl::Implementation def response formatted_no_decl = Nokogiri::XML::Node::SaveOptions::FORMAT + Nokogiri::XML::Node::SaveOptions::NO_DECLARATION #$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)" result = $db.execute "SELECT DISTINCT imageID FROM image WHERE internalID = #{@r[1]} ORDER BY imageID ASC" builder = Nokogiri::XML::Builder.new do |xml| xml.images(){ result.each do |row| xml<< GetImage::prepare(@r[0], @r[1], row[0], @a[0][:appconf]["wasurl"]).to_xml( save_with:formatted_no_decl ) end } end Riddl::Parameter::Complex.new('images','application/xml',builder.to_xml) end end class UploadImage < Riddl::Implementation def response lang = @p[@p.length-1].value #$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)" result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{lang}'" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end #puts JSON.pretty_generate(@p) i = 0 while i < @p.length-1 do item = @p[i] if(item != nil && item.name == "files[]") path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s) FileUtils.mkdir_p(path) #juergen nach alternative fragen readFile = File.read(item.value.inspect.to_s[/Tempfile:(.*?)>/m, 1]) File.open(File.join(path, lang + ".svg"), 'wb') do |file| file.write(readFile.to_s) end $db.execute("INSERT INTO image (internalID, imageID, language, label) VALUES (?,?,?,?)", [@r[1], id, lang, "Label"]) id += 1 end i +=1 end end end class AddExternalImage < Riddl::Implementation def response doc = Nokogiri::XML(@p[0].value) url = doc.xpath("/externalImage/url").text lang = doc.xpath("/externalImage/lang").text result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{lang}'" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end #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 image = ' ' path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], id.to_s) FileUtils.mkdir_p(path) File.open(File.join(path, lang + ".svg"), 'wb') do |file| file.write(image) end #Create DB entry as usual $db.execute("INSERT INTO image (internalID, imageID, language, label) VALUES (?,?,?,?)", [@r[1], id, lang, url]) end end class ReorderImages < Riddl::Implementation def response #remove brackets iter = @p[0].value.read.chop iter[0] ="" iter = iter.split(",").map(&:to_i) i = 0 while i < iter.length do if(i != iter[i]) #swap path = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], i.to_s) path2 = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], iter[i].to_s) tmp = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1]) FileUtils.mv(path + "/" + @r[4] + ".svg", tmp) FileUtils.mv(path2 + "/" + @r[4] + ".svg", path) FileUtils.mv(tmp + "/" + @r[4] + ".svg", path2) #DB Swap result = $db.execute "SELECT MAX(imageID) FROM image" if(result[0][0] == nil) maxImgId = 0 else maxImgId = result[0][0] +1 end #Wegen 999999 fragen #nutze max ID+1 zum tauschen $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [maxImgId, @r[1], i, @r[4]]) $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [i, @r[1], iter[i], @r[4]]) $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [iter[i], @r[1], maxImgId, @r[4]]) iter.map! do |item| if(item == i) iter[i] else item end end end i +=1 end end end class GetImage < Riddl::Implementation def self::prepare(station, pattern, imageID, wasurl) result = $db.execute "SELECT * FROM image WHERE internalID = #{pattern} and imageID = #{imageID}" Nokogiri::XML::Builder.new do |xml| xml.image(:id => imageID){ result.each do |row| xml.variant(:lang => row[2], :label => row[3]){ xml.text(wasurl + "images/uploads/#{station}/#{pattern}/#{imageID}/#{row[3]}.svg") } end } end end def response builder = GetImage::prepare(@r[0], @r[1], @r[3], @a[0][:appconf]["wasurl"]) Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml) end end class UpdateImageLabel < Riddl::Implementation def response $db.execute("UPDATE image SET label = ? WHERE internalID = ? AND imageID = ? AND language = ?", [@p[0].value, @r[1], @r[3], @r[4]]) end end #Not used, prototype for replacing image serverside class GetRealImage < Riddl::Implementation def response #split on "." and tacke [0] to allow for e.g. de-at.svg #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 #https://centurio.work/customers/evva/was/server/0/0/images/3/de-at?video=xyz img = File.read(File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], @r[3], @r[4].split(".")[0] + ".svg")) if false #Currently replacements are done on the client side if(@p[0].nil?) puts "Undefined p" else if(@p[0].name == "video" && @p[0].value) xml = Nokogiri.parse img puts "Width " + xml.xpath("string(//xmlns:image/@width)") puts "Height " + xml.xpath("string(//xmlns:image/@height)") #puts "Posi " + xml.xpath("string(//xmlns:clipPath/path/@height)") puts "Posi " + xml.xpath("string(//xmlns:image/following-sibling::clipPath/@id)") puts xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "") # text img = img.sub! "", ' ' else @p.each do |item| unless item.name.nil? || item.value.nil? img.sub! item.name, item.value end end end end end Riddl::Parameter::Complex.new('theRealImage','image/svg+xml',img) end end class DeleteImage < Riddl::Implementation def response 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")) $db.execute("DELETE FROM image WHERE internalID = ? AND imageID = ? AND language = ?", [@r[1], @r[3], @r[4]]) result = $db.execute "SELECT MAX(imageID) FROM image WHERE internalID = #{@r[1]} and language = '#{@r[4]}'" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end #reorder if !end gets deleted if(id == @r[3]) return else cur = @r[3].to_i + 1 prev = @r[3].to_i while cur < id.to_i do $db.execute("UPDATE image SET imageID = ? WHERE internalID = ? AND imageID = ? AND language = ?", [prev, @r[1], cur, @r[4]]) src = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], cur.to_s, @r[4] + ".svg") target = File.join(File.dirname(__dir__),'images/uploads', @r[0], @r[1], prev.to_s, @r[4] + ".svg") FileUtils.mv(src, target) cur+=1 prev += 1 end end end end class ListSearch < Riddl::Implementation def response ret = { :imagesWIZAll => "/imagesWIZ?pattern=...", :imagesWIZSingle => "/imagesWIZ/0..*/?pattern=...", :errorsWIZ => "/errorsWIZ/" } Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class SearchImages < Riddl::Implementation def response #https://centurio.work/customers/evva/was/ui/TransformProductCode.php?ProductCode=E.A.PZ.DZ-S.31.31.MP.SEP pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value)) #Get first key dynamicly and then build sql query searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID" searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ') searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ') searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC" result = $db.execute searchstring; puts searchstring #Search for images with one or multiple found patternIDs ret = [] if result.length > 0 result.each do |item| result2 = $db.execute "SELECT DISTINCT internalID, imageID FROM image WHERE internalID = #{item[0]} ORDER BY imageID ASC" result2.each do |item2| result3 = $db.execute "SELECT DISTINCT station FROM stationpattern WHERE internalID = #{item2[0]} AND station = #{@r[0]}" if(result3[0] != nil) ret << @r[0] + "/" + item2[0].to_s + "/" + item2[1].to_s end end end end Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class SearchImagesSingle < Riddl::Implementation def response pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value)) searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID" searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ') searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ') searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC" result = $db.execute searchstring; count = 0 pattern = 0 image = 0 builder = Nokogiri::XML::Builder.new do |xml| xml.image(:id => @r[3].to_s){ if result.length > 0 result.each do |item| result2 = $db.execute "SELECT DISTINCT internalID, imageID FROM image WHERE internalID = #{item[0]} ORDER BY imageID ASC" result2.each do |item2| result3 = $db.execute "SELECT DISTINCT internalID, imageID, language, label FROM image WHERE internalID = #{item[0]} and imageID =#{item2[1]}" result3.each do |item3| result4 = $db.execute "SELECT DISTINCT station FROM stationpattern WHERE internalID = #{item3[0]} AND station = #{@r[0]}" if(result4[0] != nil) if(count == @r[3].to_i) #ret << @r[0] + "/" + item2[0].to_s + "/" + item2[1].to_s 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" @p.each_with_index do |item, index| if index != 0 uri += "&" + item.name.to_s + "=" + item.value.to_s end end xml.variant(:lang => item3[2].to_s, :label => item3[3].to_s){ xml.text(uri) } end count += 1 end end end end end } end #https://centurio.work/customers/evva/was/ui/imageReplacement.php?___image___=8/23/0/de-at.svg #puts builder.to_xml Riddl::Parameter::Complex.new('image','application/xml',builder.to_xml) end end class SearchErrors < Riddl::Implementation def response pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value)) searchstring = "SELECT " + pattern.keys[0] + ".internalID AS ID" searchstring = searchstring + " FROM " + pattern.map{ |key, val| "pattern " + key}.join(', ') searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".internalID == " + key + ".internalID"}.join(' AND ') searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC" result = $db.execute searchstring; ret = [] if result.length > 0 result.each do |item| result2 = $db.execute "SELECT DISTINCT error FROM error WHERE internalID = #{item[0]} ORDER BY internalID ASC" result2.each do |item2| ret << item2[0].to_s end end end ret = ret.uniq builder = Nokogiri::XML::Builder.new do |xml| xml.reason{ if result.length > 0 ret.each do |item| xml.reason(:lang => "de-at"){ xml.text(item) } end end } end Riddl::Parameter::Complex.new('errors','application/xml',builder.to_xml) end end class GetAssignments < Riddl::Implementation def response ret = {} resultstation = $db.execute "SELECT * FROM assignments" resultstation.each do |row| ret[row[0]] = row[1] end Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class SaveNewAssignment < Riddl::Implementation def response form = JSON.parse(@p[0].value.read); result = $db.execute "SELECT MAX(ID) FROM assignments" if(result[0][0] == nil) id = 0 else id = result[0][0] +1 end checkimage = $db.execute "SELECT * FROM assignments WHERE image = '#{form["img"]}'" #if image not stored create new assignment with pattern, otherwise only add pattern if(checkimage[0] == nil) $db.execute("INSERT INTO assignments (ID, image) VALUES (?,?)", [id, form["img"]]) form["parts"].each{ |key,val| $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, key, val]) } $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, "STATION", form["station"]]) $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [id, 0, "TEXT", form["assignmentText"]]) else maxID = $db.execute "SELECT MAX(AssID) FROM patternassignments WHERE ID ='#{checkimage[0][0]}'" if(maxID[0][0] == nil) maxID = 0 else maxID = maxID[0][0] +1 end form["parts"].each{ |key,val| $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, key, val]) } $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, "STATION", form["station"]]) $db.execute("INSERT INTO patternassignments (ID, AssID, type, value) VALUES (?,?,?,?)", [checkimage[0][0], maxID, "TEXT", form["assignmentText"]]) end end end class GetAssignment < Riddl::Implementation def response ret = {} allAssIds = $db.execute "SELECT DISTINCT AssID FROM patternassignments WHERE ID = #{@r[1]}" allAssIds.each do |row| station = $db.execute "SELECT value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type == 'STATION'" stationvalue = Integer(station[0][0]) #old where pattern is stored as string #singleItem = $db.execute "SELECT value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type != 'STATION'" #if(ret[stationvalue] == nil) # ret[stationvalue] = Hash.new() #end #ret[stationvalue][row[0]] = singleItem.join('.') singleItem = $db.execute "SELECT type, value FROM patternassignments WHERE ID = #{@r[1]} AND AssID = #{row[0]} AND type != 'STATION'" if(ret[stationvalue] == nil) ret[stationvalue] = Hash.new() end ret[stationvalue][row[0]] = Hash.new() singleItem.each do |it| ret[stationvalue][row[0]][it[0]] = it[1] end end Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end class DeleteAssignment < Riddl::Implementation def response $db.execute("DELETE FROM assignments WHERE ID = ?", [@r[1]]) end end class DeleteSingleAssignment < Riddl::Implementation def response $db.execute("DELETE FROM patternassignments WHERE AssID = ? AND ID = ?", [@r[2], @r[1]]) end end class ListAssignmentSearch < Riddl::Implementation def response #https://centurio.work/customers/evva/was/ui/TransformProductCode.php?ProductCode=E.A.PZ.DZ-S.31.31.MP.SEP pattern = JSON.parse(Net::HTTP.get URI(@a[0][:appconf]["wasurl"] + "ui/TransformProductCode.php?ProductCode=" + @p[0].value)) #$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)" #Get first key dynamicly and then build sql query if(pattern.keys[0] != nil) searchstring = "SELECT " + pattern.keys[0] + ".ID AS ID" searchstring = searchstring + " FROM " + pattern.map{ |key, val| "patternassignments " + key}.join(', ') searchstring = searchstring + " , patternassignments STATION , patternassignments ValText" searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".AssID == " + key + ".AssID"}.join(' AND ') + " AND "; searchstring = searchstring + pattern.map{ |key, val| pattern.keys[0] + ".ID == " + key + ".ID"}.join(' AND ') searchstring = searchstring + " AND " + pattern.keys[0] + ".AssID == STATION.AssID" searchstring = searchstring + " AND " + pattern.keys[0] + ".AssID == ValText.AssID" searchstring = searchstring + " AND " + pattern.keys[0] + ".ID == STATION.ID" searchstring = searchstring + " AND " + pattern.keys[0] + ".ID == ValText.ID" searchstring = searchstring + " AND STATION.type == 'STATION'" + " AND STATION.value == '" + @p[1].value + "'" searchstring = searchstring + " AND ValText.type == 'TEXT'" + " AND ValText.value == '" + @p[2].value + "'" searchstring = searchstring + " AND " + pattern.map{ |key, val| key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"}.join(' AND ') + " ORDER BY ID ASC" #puts searchstring; else searchstring = "SELECT * FROM " searchstring = searchstring + "patternassignments STATION , patternassignments ValText" searchstring = searchstring + " WHERE" searchstring = searchstring + " STATION.type == 'STATION'" + " AND STATION.value == '" + @p[1].value + "'" searchstring = searchstring + " AND ValText.type == 'TEXT'" + " AND ValText.value == '" + @p[2].value + "'" searchstring = searchstring + " AND ValText.AssID == STATION.AssID" searchstring = searchstring + " ORDER BY ID ASC" end result = $db.execute searchstring; #Search for images with one or multiple found patternIDs ret = [] if result.length > 0 result.each do |item| result2 = $db.execute "SELECT DISTINCT image FROM assignments WHERE ID = #{item[0]}" result2.each do |item2| ret << item2[0] end end end Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret)) end end def createDB(opts) $db.execute("PRAGMA foreign_keys=ON"); $db.execute "CREATE TABLE IF NOT EXISTS stations(station INT, name VARCHAR(20), PRIMARY KEY(station))" opts[:appconf]["stations"].each { |n| CreateStation::createDB(n) } #vor DB struktur umstellung #$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))" #$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)" #$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)" #$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))" $db.execute "CREATE TABLE IF NOT EXISTS stationpattern(station INT, internalID INT, description TEXT, date TEXT, PRIMARY KEY(internalID), UNIQUE(internalID))" $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)" $db.execute "CREATE TABLE IF NOT EXISTS error(internalID INT, error TEXT, FOREIGN KEY(internalID) REFERENCES stationpattern(internalID) ON DELETE CASCADE)" $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)" $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)" $db.execute "CREATE TABLE IF NOT EXISTS assignments(ID INT, image TEXT, PRIMARY KEY(ID) UNIQUE (ID))" $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)" end server = Riddl::Server.new(File.join(__dir__,'/was.xml'), :host => 'localhost') do |opts| accessible_description true cross_site_xhr true createDB(opts) # opts[:appconf] on resource do run GetStations if get run CreateStation if post on resource 'cutimage' do run CutImage if get #should add deleteReplacement end on resource 'json' do run GetJsonFiles if get on resource '.*' do run SaveJson if put 'list' run GetJson if get end #should add deleteReplacement end on resource '\d+' do run GetStation if get run CreatePattern if post 'pattern' on resource '\d+' do run GetPattern if get run UpdatePattern if put 'pattern' run DeletePattern if delete run DuplicatePattern if put 'patternID' on resource 'error' do run SaveError if put 'error' run GetError if get #should add deleteerror end on resource 'replacement' do run SaveReplacement if put 'replacement' run GetReplacement if get #should add deleteReplacement end on resource 'images' do run GetImages, opts if get #run CreateImage if post #'image' run AddExternalImage if post 'externalImage' run UploadImage if post on resource 'reorder' do on resource do run ReorderImages if put 'orderlist' end end on resource '\d+' do run GetImage, opts if get on resource do run UpdateImageLabel if put 'label' run GetRealImage if get run DeleteImage if delete end end end end on resource 'search' do run ListSearch if get on resource 'imagesWIZ' do run SearchImages, opts if get on resource '\d+' do run SearchImagesSingle, opts if get end end on resource 'errorsWIZ' do run SearchErrors, opts if get end end end on resource 'assignments' do run GetAssignments if get run SaveNewAssignment if post 'list' on resource 'search' do run ListAssignmentSearch, opts if get end on resource '\d+' do run GetAssignment if get run DeleteAssignment if delete on resource '\d+' do run DeleteSingleAssignment if delete end end end end end.loop!