Dashboard/dashboard

#!/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 'xml/smart'
require 'riddl/server'
require 'fileutils'
require 'typhoeus'
require 'sqlite3'


class ConfigSites < Riddl::Implementation
  def response  
    Riddl::Parameter::Complex.new('ui','text/html',File.open(File.join(__dir__,'template','index.html')))
  end
end




class GetAllConfigs < Riddl::Implementation
  def response  
    Dir.chdir( __dir__  + '/data')
    databack = JSON::pretty_generate(Dir.glob('*/').sort_by{|x| x.downcase});
    Dir.chdir( __dir__)
    Riddl::Parameter::Complex.new('list','application/json',databack)
  end
end


class GetAllVisus < Riddl::Implementation
  def response  
  
    Riddl::Parameter::Complex.new('ui','text/html',File.open(File.join(__dir__,'data', @r[-2],'visus.html')))
  end
end


class Get < Riddl::Implementation
  def response
    #Riddl::Parameter::Complex.new('ui','text/html',File.open(File.join(__dir__,'template','template.html')))
    Riddl::Parameter::Complex.new('ui','text/html',File.open(File.join(__dir__,'data', @r.last,'index.html')))
  end
end



class GetAllData < Riddl::Implementation
  def response
    
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    if !db.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Entries';").empty?

    
      #db.execute "CREATE TABLE IF NOT EXISTS Entries(seriennummer TEXT PRIMARY KEY, produktcode TEXT, activity TEXT, cpeeInstance INTEGER)"

      db.results_as_hash = true
      alldata = {};
      alldata["data"] = (db.execute "Select * from Entries ORDER BY __orderID__ ASC")
    
    
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump(alldata))
    
    else 
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump({}))
    
    end
    
  end
end


class AddColumn < Riddl::Implementation
  def response
  
    colname = @p[0].value
    colvalue = @p[1].value
    
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    
    searchstring = "SELECT DISTINCT	__orderID__, alldata FROM Entries"
    result = db.execute searchstring;

    result.each do |item|
      key = item[0]
      newalldata = item[1]
      
      thaData = JSON.parse(newalldata)
      thaData[colname] = colvalue;
      
      db.execute("UPDATE Entries SET alldata = ? WHERE __orderID__ = ?", thaData.to_json, key )
    end   
    
    Riddl::Parameter::Complex.new('value','application/json',JSON.dump({status: "looks good?"}))
  end
end


class DeleteColumn < Riddl::Implementation
  def response
    colname = @p[0].value
    
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    
    searchstring = "SELECT DISTINCT	__orderID__, alldata FROM Entries"
    result = db.execute searchstring;

    result.each do |item|
      key = item[0]
      newalldata = item[1]
      
      thaData = JSON.parse(newalldata)
      thaData.delete(colname)
      
      db.execute("UPDATE Entries SET alldata = ? WHERE __orderID__ = ?", thaData.to_json, key )
    end   
    
    
    Riddl::Parameter::Complex.new('value','application/json',JSON.dump({status: "looks good?"}))
  end
end        


class GetNData < Riddl::Implementation
  def response    
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    if !db.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Entries';").empty?
      db.results_as_hash = true
      alldata = {};
      alldata["data"] = (db.execute "Select * from Entries ORDER BY __orderID__ ASC LIMIT 1 OFFSET " + @r[-1])
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump(alldata))
    else 
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump({}))
    end
  end
end

class DeleteNData < Riddl::Implementation
  def response
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    if !db.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Entries';").empty?
      db.results_as_hash = true
      alldata = {};
      alldata["data"] = (db.execute "Select * from Entries ORDER BY __orderID__ ASC LIMIT 1 OFFSET " + @r[-1])
      if alldata["data"] != []
      
      
        id = alldata["data"][0]["__orderID__"].to_s
        db.execute("DELETE FROM Entries WHERE __orderID__ = '" + id + "'")
      end
    else 
      nil
    end
    @a[0].send("reset")
  end
end

# used when executed from e.g. website, in such a case no cpee instance is given
class AddEntry < Riddl::Implementation
  def response
    newEntry = JSON.parse(@p[0].value.read)
    
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    
    # in case primary key is given
    # PRIMARY KEY   Momentan noch nicht implementiert
    if newEntry.has_key?("pkvalue")
      db.execute "CREATE TABLE IF NOT EXISTS Entries(__orderID__ INTEGER PRIMARY KEY AUTOINCREMENT, alldata TEXT, pkvalue TEXT, cpeeInstance INTEGER)"
      db.execute("INSERT OR REPLACE INTO Entries (alldata, pkvalue, cpeeInstance) VALUES (?,?,?)",JSON.dump(newEntry["alldata"]), newEntry["pkvalue"], "Direkt")
      hash = {__orderID__: db.execute("select last_insert_rowid()")[0], alldata: JSON.dump(newEntry["alldata"]), pkvalue: newEntry["pkvalue"], cpeeInstance: "Direkt"};
    else
      db.execute "CREATE TABLE IF NOT EXISTS Entries(__orderID__ INTEGER PRIMARY KEY AUTOINCREMENT, alldata TEXT, cpeeInstance INTEGER)"
      db.execute("INSERT OR REPLACE INTO Entries (alldata, cpeeInstance) VALUES (?,?)",JSON.dump(newEntry["alldata"]), "Direkt")
      hash = {__orderID__: db.execute("select last_insert_rowid()")[0], alldata: JSON.dump(newEntry["alldata"]), cpeeInstance: "Direkt"};
    end
    
    
    
    @a[0].send("reset")
 
 
 
    nil
    
  end
end

class Put < Riddl::Implementation
  def self::putentry(r, p, h)
  
    Dir.mkdir(File.join('data',r.last)) rescue nil
    Dir.mkdir(File.join('data',r.last, "js")) rescue nil
    Dir.mkdir(File.join('data',r.last, "visus")) rescue nil
    
    
    #prevent overriding ui and index in case user changed something there
    if !File.file?(File.join('data',r.last,'js/ui.js'))
      File.write(File.join('data',r.last,'js/ui.js'),File.open("defaultContent/js/ui.js").read)
    end
    
    if !File.file?(File.join('data',r.last,'index.html'))
      file = File.open("defaultContent/index.html").read
      newfile = file.gsub("!replaceThisString!", r.last)

      File.write(File.join('data',r.last,'index.html'), newfile)
    end
    
    #prevent overriding visualization
    if !File.file?(File.join('data',r.last,'visus.html'))
      File.write(File.join('data',r.last,'visus.html'),File.open("defaultContent/visus.html").read)
    end
    
    
    fname = File.join('data',r.last,'data.db')
    db = SQLite3::Database.open fname
    
    
    #in case primary key is given
    if p[1] != nil 
      keyfunction = ""
      if p[2] != nil      
        keyfunction = p[2].value
      end
      db.execute "CREATE TABLE IF NOT EXISTS Entries(__orderID__ INTEGER PRIMARY KEY AUTOINCREMENT, alldata TEXT, pkvalue TEXT " + keyfunction + ", cpeeInstance INTEGER)"
      db.execute("INSERT OR REPLACE INTO Entries (alldata, pkvalue, cpeeInstance) VALUES (?,?,?)",p[0].value, p[1].value, h['CPEE_INSTANCE'])
      hash = {__orderID__: db.execute("select last_insert_rowid()")[0], alldata: p[0].value, pkvalue: p[1].value, cpeeInstance: h['CPEE_INSTANCE']};
    else
      db.execute "CREATE TABLE IF NOT EXISTS Entries(__orderID__ INTEGER PRIMARY KEY AUTOINCREMENT, alldata TEXT, cpeeInstance INTEGER)"
      db.execute("INSERT OR REPLACE INTO Entries (alldata, cpeeInstance) VALUES (?,?)",p[0].value, h['CPEE_INSTANCE'])
      hash = {__orderID__: db.execute("select last_insert_rowid()")[0], alldata: p[0].value, cpeeInstance: h['CPEE_INSTANCE']};
    end
    hash;
  end
  
  
  def response  
    hash = Put::putentry(@r, @p, @h)
  

    @a[0].send("reset")
 
    nil
  end
end

class PostHtmlVals < Riddl::Implementation
  def response
    
    Dir.mkdir(File.join('data',@r[-2])) rescue nil
    Dir.mkdir(File.join('data',@r[-2], "js")) rescue nil
    Dir.mkdir(File.join('data',@r[-2], "visus")) rescue nil
    
    
    #prevent overriding ui and index in case user changed something there
    if !File.file?(File.join('data',@r[-2],'js/ui.js'))
      File.write(File.join('data',@r[-2],'js/ui.js'),File.open("defaultContent/js/ui.js").read)
    end
    
    if !File.file?(File.join('data',@r[-2],'index.html'))
      file = File.open("defaultContent/index.html").read
      newfile = file.gsub("!replaceThisString!", @r[-2])

      File.write(File.join('data',@r[-2],'index.html'), newfile)
    end
    
    #prevent overriding visualization
    if !File.file?(File.join('data',@r[-2],'visus.html'))
      File.write(File.join('data',@r[-2],'visus.html'),File.open("defaultContent/visus.html").read)
    end
    
    
    file = JSON['{}']

    
    @p.each_with_index  do |child, idx|
      file[child.name] = child.value
    end
    
    
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
      
    db.execute "CREATE TABLE IF NOT EXISTS Entries(__orderID__ INTEGER PRIMARY KEY AUTOINCREMENT, alldata TEXT, cpeeInstance INTEGER)"
    db.execute("INSERT OR REPLACE INTO Entries (alldata, cpeeInstance) VALUES (?,?)", JSON[file], "none")
    
    
    @a[0].send("reset")
    Riddl::Parameter::Complex.new('ui','text/html',  '<html><head><meta http-equiv="refresh" content="0; url =' +@h["REFERER"]+'"/></head>')
  end
end



class PutPrio < Riddl::Implementation
  def response
    hash = Put::putentry(@r, @p, @h) #add entry    
    hash = Move::moveentry(hash[:__orderID__][0], 0,@r) #move to front
    @a[0].send("reset")
    nil
  end
end


class PutPrioN < Riddl::Implementation
  def response
    
    #move all back
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    #Updating entries directly + X would result in unique constraint failed
    #Therefore updates first to negative values then *-1
    db.execute "UPDATE Entries SET __orderID__= 0 - (__orderID__ + " + @p[1].value  + ")"
    db.execute "UPDATE Entries SET __orderID__= __orderID__ *-1"

    #Add entries
    i = 0
    while i <  @p[1].value.to_i  do
      #db.execute("INSERT OR REPLACE INTO Entries (__orderID__, alldata, cpeeInstance) VALUES (?,?,?)",i, @p[0].value, @h['CPEE_INSTANCE'])
      
      #in case primary key is given
      if @p[2] != nil
        db.execute("INSERT OR REPLACE INTO Entries (__orderID__, alldata, pkvalue, cpeeInstance) VALUES (?,?,?,?)",i, @p[0].value, @p[2].value, @h['CPEE_INSTANCE'])
      else
        db.execute("INSERT OR REPLACE INTO Entries (__orderID__, alldata, cpeeInstance) VALUES (?,?,?)",i, @p[0].value, @h['CPEE_INSTANCE'])
      end
      i +=1
    end
    
    @a[0].send("reset")
    nil
  end
end



class Move < Riddl::Implementation
  def self::moveentry(from, to,r)
    fname = File.join('data',r.last,'data.db')
    db = SQLite3::Database.open fname
    
        
    #getMaxID
    result = db.execute "SELECT MAX(__orderID__) FROM Entries"
    if(result[0][0] == nil)
      maxImgId = 0
      else
      maxImgId = result[0][0] +1
    end
    
    i = 0
    if to < from
      while to < from do
      
        #nutze max ID zum tauschen
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [maxImgId, from - 1])
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [from - 1, from])
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [from, maxImgId])
        
        from -=1
      end
    elsif from < to
    
      while from < to do
      
        #nutze max ID zum tauschen
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [maxImgId, from + 1])
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [from + 1, from])
        db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [from, maxImgId])
        
        from +=1
      end
    
    end
    nil
  end
  
  def response
    movement = JSON.parse(@p[0].value.read)

    from = movement["From"]
    to = movement["To"]
    hash = Move::moveentry(from, to,@r)
    nil
  end
end

class Update < Riddl::Implementation
  def response
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    
    updateData = JSON.parse(@p[0].value.read)
    
    orderID = updateData["ID"]
    alldata = JSON.generate(updateData["alldata"])
    db.execute("UPDATE Entries SET alldata = ? WHERE  __orderID__ = ?", [alldata, orderID])
    nil
  end
end

class Storesortedtable < Riddl::Implementation
  def response
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    
    #invert keys from + to - to prevent duplicate primary keys when reordering
    db.execute("UPDATE Entries SET __orderID__ = __orderID__ *-1 -1")
    
    updateData = JSON.parse(@p[0].value.read)
    
    updateData.each_with_index  do |child, idx|
      db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [idx, child.to_i*-1 -1])
    end
    
    
    #security
    #in case there are negative entries remaining add them with positive value to the end
    
    result = db.execute "SELECT MAX(__orderID__) FROM Entries"
    if(result[0][0] == nil)
      maxorderID = 0
      else
      maxorderID = result[0][0] +1
    end
    
    resultpattern = db.execute "SELECT * FROM Entries WHERE __orderID__ < 0"
    resultpattern.each do |row2|
      db.execute("UPDATE Entries SET __orderID__ = ? WHERE  __orderID__ = ?", [maxorderID, row2[0]]);
      maxorderID = maxorderID +1;
    end
    
    @a[0].send("reset")
    
    nil
  end
end




class Delete < Riddl::Implementation
  def response
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    alldata = @p[0].value.read
    db.execute("DELETE FROM Entries WHERE alldata = '" + alldata + "'")
    @a[0].send("reset")   
   
    nil
  end
end

class DeleteID < Riddl::Implementation
  def self::remove(dbfolder, id)
  
    fname = File.join('data',dbfolder,'data.db')
    db = SQLite3::Database.open fname
    db.execute("DELETE FROM Entries WHERE __orderID__ = '" + id.to_s + "'")
  
  end
  def response
    DeleteID::remove(@r.last, @p[0].value.read)
    @a[0].send("reset")
    nil
  end
end

class DeleteIDDirect < Riddl::Implementation
  def response
    DeleteID::remove(@r.last, @p[0].value)
    @a[0].send("reset")
    nil
  end
end


class DeletePK < Riddl::Implementation
  def response
  
    fname = File.join('data',@r.last,'data.db')
    db = SQLite3::Database.open fname
    pkvalue = @p[0].value
    db.execute("DELETE FROM Entries WHERE pkvalue = '" + pkvalue + "'")
    @a[0].send("reset")   
    nil
  end
end


class SearchPK < Riddl::Implementation
  def response
    
    fname = File.join('data',@r[-4],'data.db')
    db = SQLite3::Database.open fname
    pkvalue = @r.last    
    if !db.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Entries';").empty?
      db.results_as_hash = true
      alldata = {};
      alldata["data"] = (db.execute "Select * from Entries WHERE pkvalue = '" + pkvalue + "'")
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump(alldata))
    else 
      Riddl::Parameter::Complex.new('value','application/json',JSON.dump({}))
    end
  end
end


class GetNext < Riddl::Implementation
  def response
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    
    #get value
    db.results_as_hash = true
    alldata = {};
    alldata["data"] = (db.execute "Select * from Entries ORDER BY __orderID__ ASC LIMIT 1")[0]
    
    Riddl::Parameter::Complex.new('value','application/json',JSON.dump(alldata))
  end
end 

class DeleteNext < Riddl::Implementation
  def response
    fname = File.join('data',@r[-2],'data.db')
    db = SQLite3::Database.open fname
    
    #get value
    db.results_as_hash = true
    alldata = {};
    alldata["data"] = (db.execute "Select * from Entries ORDER BY __orderID__ ASC LIMIT 1")[0]
    
    #remove from DB
    DeleteID::remove(@r[-2], alldata["data"]["__orderID__"])
    
    @a[0].send("reset")
    Riddl::Parameter::Complex.new('value','application/json',JSON.dump(alldata))
  end
end 


class DeleteAll < Riddl::Implementation
  def response
  
    File.unlink(File.join('data',@r.last,'data.db')) rescue nil
    nil
  end
end 





class SSE < Riddl::SSEImplementation #{{{
  def onopen
    signals = @a[0]
    signals.add self
    send 'started'
    true
  end

  def onclose
    signals = @a[0]
    signals.remove self
    nil
  end
end #}}}

class Signaling # {{{
  def initialize
    @binding = []
  end

  def add(binding)
    @binding << binding
  end
  def remove(binding)
    @binding.delete(binding)
  end
  def length
    @binding.length
  end

  def send(value)
    @binding.each do |b|
      b.send(value)
    end
  end
end #}}}

server = Riddl::Server.new(File.join(__dir__,'/dashboard.xml'), :host => 'localhost') do |opts|
  accessible_description true
  cross_site_xhr true

  opts[:signals] = {}

  parallel do
    loop do
      opts[:signals].each do |k,v|
        v.send('keepalive')
      end
      sleep 5
    end
  end

  on resource do
    run ConfigSites if get
    
      on resource 'getconfigs' do
        run GetAllConfigs if get
      end
    
    
    
    on resource do |r|
      idx = r[:r][0]
      opts[:signals][idx] ||= Signaling.new
      
      
      run Get if get
      run Put, opts[:signals][idx] if put 'input'
      run PutPrio, opts[:signals][idx] if put 'inputprio'
      run PutPrioN, opts[:signals][idx] if put 'inputNprio'
      run Move if put 'move'
      run Update if put 'update'
      run Storesortedtable, opts[:signals][idx] if put 'storesortedtable'
      run AddEntry, opts[:signals][idx] if post 'directADD'
      run Delete, opts[:signals][idx] if delete 'deleteMsg'
      run DeleteID, opts[:signals][idx] if delete 'deleteByID'
      run DeletePK, opts[:signals][idx] if delete 'deletePK'
      run DeleteAll, opts[:signals][idx] if delete 'deleteAll'
      run DeleteIDDirect, opts[:signals][idx] if delete
      
      
      on resource '\d+' do
        run GetNData, opts if get
        run DeleteNData, opts[:signals][idx] if delete
      end
      
      on resource 'sse' do
        run SSE, opts[:signals][idx] if sse
      end
      
      on resource 'data.db' do
        run GetAllData if get
      end
      
      on resource 'Column' do
        run AddColumn if post 'addColumn'
        run DeleteColumn if post 'removeColumn'
      end
      
      on resource 'htmlform' do
        run PostHtmlVals, opts[:signals][idx] if post
      end
      
      on resource 'search' do
        on resource 'PK' do
          on resource '.*' do
            run SearchPK if get
          end
        end
      end
      
      on resource 'visus' do
        run GetAllVisus if get
      end
      
      on resource 'next' do
        run GetNext, opts[:signals][idx] if get
        run DeleteNext, opts[:signals][idx] if delete
      end
      
    end
  end
end.loop!