WAS/server/was.rb

#!/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 ORDER BY station ASC"
    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
    CreateStation::createDB(7)
  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
     
    #bring given pattern into correct string (initially pattern could be in different order than required)
    pattern = [];
    fullcode = JSON.parse( File.read(File.join(File.dirname(__dir__),"server/json/ProductCode.json")))
    fullcode["pattern"].each do |child|
      pattern.push(form["parts"][child["abk"]])
    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)    
      #check if assignment would overlap
      if (!ListAssignmentSearch::prepare(@a[0][:appconf]["wasurl"], pattern.join('.'), form["station"], form["assignmentText"]).any?)   
        $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"]])
        
        ret = {:id => id}
      end
      Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
    else
      #pp "Im 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
      
      #Search 4 assignment if none found add assignment
      #pp ListAssignmentSearch::prepare(@a[0][:appconf]["wasurl"], pattern.join('.'), form["station"], form["assignmentText"])
      if (!ListAssignmentSearch::prepare(@a[0][:appconf]["wasurl"], pattern.join('.'), form["station"], form["assignmentText"]).any?)   
        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"]])
        #pp "Added Assignment"
        ret = {:id => checkimage[0][0]}
      end
      Riddl::Parameter::Complex.new('list','application/json',JSON::pretty_generate(ret))
    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 RenameAssignmentImagePath < Riddl::Implementation
  def response
    $db.execute("UPDATE assignments SET image = '#{JSON.parse(@p[0].value.read)["url"]}' WHERE ID = #{@r[1]}")
  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 self::prepare(appconf, pat, pat_station, pat_value)
    #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(appconf + "ui/TransformProductCode.php?ProductCode=" + pat))
    
    #remove empty entries from pattern
    pattern.each do |key, value|
      if(value == "")
        if(key == "Zi")
        elsif (key == "Kf")
        else
          pattern.delete(key)
        end
      end
    end
    
    
    emptypatternparts = []
    #fullcode = JSON.parse( File.read(File.join("json" , "ProductCode.json")))
    #puts File.join(File.dirname(__dir__),"server/json/ProductCode.json")
    fullcode = JSON.parse( File.read(File.join(File.dirname(__dir__),"server/json/ProductCode.json")))
    
    fullcode["pattern"].each do |key|
      if(!pattern.has_key?(key["abk"]) )
        emptypatternparts.push(key["abk"])
      end
    end
    

    #$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 + ", " + emptypatternparts.map{ |key| "patternassignments " + key}.join(', ')
      
      
      searchstring = searchstring + " , patternassignments STATION , patternassignments ValText"    
      searchstring = searchstring + " WHERE " + pattern.map{ |key, val| pattern.keys[0] + ".AssID == " + key + ".AssID"}.join(' AND ');
      searchstring = searchstring + " AND " + pattern.map{ |key, val| pattern.keys[0] + ".ID == " + key + ".ID"}.join(' AND ')
      
      
      searchstring = searchstring + " AND " + emptypatternparts.map{ |key| pattern.keys[0] + ".AssID == " + key + ".AssID"}.join(' AND ')
      searchstring = searchstring + " AND " + emptypatternparts.map{ |key| pattern.keys[0] + ".ID == " + key + ".ID"}.join(' AND ')
      searchstring = searchstring + " AND " + emptypatternparts.map{ |key| key + ".type == '" + key + "' AND " + key + ".value == ''" }.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 == '" + pat_station + "'"
      searchstring = searchstring + " AND ValText.type == 'TEXT'"  + " AND ValText.value == '" + pat_value + "'"
      
      
      searchstring = searchstring + " AND " + pattern.map{ |key, val|
        #In case a value has the option "KeineAngabe" empty values are allowed
        if(val.start_with?("KeineAngabe"))
          key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*' or " + key + ".value == '')"
        else
          key + ".type == '" + key + "' AND (" + key + ".value == '"+ val +"' or " + key + ".value == '*')"
        end
        }.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 == '" + pat_station + "'"
      searchstring = searchstring + " AND ValText.type == 'TEXT'"  + " AND ValText.value == '" + pat_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
    return ret

  end
  
  def response
    ret = ListAssignmentSearch::prepare(@a[0][:appconf]["wasurl"], @p[0].value, @p[1].value, @p[2].value)
    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, opts if post 'list'
      on resource 'search' do
        run ListAssignmentSearch, opts if get
      end
      on resource '\d+' do
        run GetAssignment if get
        run RenameAssignmentImagePath if put 'list'
        run DeleteAssignment if delete
        on resource '\d+' do
          run DeleteSingleAssignment if delete
        end
      end
    end
  end
end.loop!