12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055 |
- #!/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
- # <http://www.gnu.org/licenses/>.
- 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 = '<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%"/>
- </svg>'
-
- 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! "</svg>", '
- <g><g transform="translate(562,288),scale(1.35185 1.35185)">
- <foreignObject width="480" height="270">
- <video width="480" height="270" controls="" autoplay="autoplay" muted="muted" xmlns="http://www.w3.org/1999/xhtml" >
- <source src="' + xml.xpath("string(//xmlns:text[starts-with(text(), 'url')])").sub("url=", "") +'" type="video/mp4"/>
- </video>
- </foreignObject>
- </g></g></svg>'
-
- 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!
|