#require "rdb_concat"

class ExplorerController < ApplicationController 
  layout "gfdnavi"
 @cachedb="gfdnavi_querycache"
 
 
 def index
   session[:previd]=nil
   @previd=nil
   @qargs=nil
   @results=ExplorerResult.new(0)
   session[:results]=@results
 end
 
 def add_keyname
     @results=session[:results]
     @previd=session[:previd]
     @results.keynames.push(params[:keyname])
     @results.keynames.uniq!
     session[:results]=@results
     #render :action=>"index.rhtml"
     valuelist=@results.get_keyvalue_list(params[:keyname])
     if valuelist.size>0 then
       render :update do |page|
        valuelist.each{ |v|          
          page.insert_html(:bottom,"kw_#{params[:keyname]}","<li><a href=\"/explorer/search_kw?keyname=#{params[:keyname]}&keyvalue=#{v.value}\">#{v.value}(#{v.cnt})</a></li>")
        }
       page.show("kw_#{params[:keyname]}_close")       
       end
     end     
 end
 
 def del_keyname
     @results=session[:results]
     @previd=session[:previd]
     @results.keynames.delete(params[:keyname])
     session[:results]=@results
     session[:previd]=@previd
     render :update do |page|
       page.replace_html("kw_#{params[:keyname]}","")
       page.hide("kw_#{params[:keyname]}_close")
     end
 end
 
 def search_path
   c = Variable.connection.adapter_name=="MySQL" ? "path like concat("'%1'","'%'")" : "'%1'||'%'"
   options={
     :cond=>c,
     :argsstr=>"path",
     :table=>"nodes",
     :select=>"id as vid",
     :description=>"[P]%1",
     :path=>params[:path]
   }
   search("path",options)
 end

 def search_freeword
   @freeword=params[:freeword]
 
   options={
     :cond=>"name like '%%1%' or value like '%%1%'",
     :argsstr=>"freeword",
     :table=>"keyword_attributes",
     :select=>"node_id as vid",
     :description=>"[FW]%1"
   }
   search("freeword",options)
 end

 
 def search_kw
   @keyname=params[:keyname]
   @keyvalue=params[:keyvalue]
 
   options={
     :cond=>"name = '%1' and value = '%2'",
     :argsstr=>"keyname,keyvalue",
     :table=>"keyword_attributes",
     :select=>"node_id as vid",
     :description=>"[K]%1=%2"
   }
   search("keyword",options)
 end
 
 def search_kname
   @keyname=params[:keyname]
   options={
     :cond=>"name = '%1'",
     :argsstr=>"keyname",
     :table=>"keyword_attributes",
     :select=>"node_id as vid",
     :description=>"[K]%1"
   }
   search("keyname",options) 
 end
 
  def search_space
   # もともとのプログラムを見て書き直す
   @start_lon=params[:start_lon]
   @start_lat=params[:start_lat]
   @end_lon=params[:end_lon]
   @end_lat=params[:end_lat]
    
   qcond=<<-EOM
        longitude_lb <= %1 and longitude_rt >= %2 
    and latitude_lb <= %3 and latitude_rt >= %4 
    and not(node_id IS NULL)
   EOM
   options={
     :cond=>qcond,
     :argsstr=>"end_lon-f,start_lon-f,end_lat-f,start_lat-f",
     :table=>"spatial_and_time_attributes",
     :select=>"node_id as vid",
     :description=>"[S](%2,%4)-(%1,%3)"
   }
   search("space",options)
 end

  def search_time
   # もともとのプログラムを見て書き直す
   @starttime=params[:starttime]
   @endtime=params[:endtime]
 
   options={
     :cond=>"starttime <= %1 or endtime >= %2",
     :argsstr=>"endtime,starttime",
     :table=>"spatial_and_time_attributes",
     :select=>"node_id as vid",
     :description=>"[T]%2-%1"
   }
   search("time",options)
 end
 
 def back
     @results=session[:results]
     previd=params[:previd].to_i
     @previd=previd
     @results.id=@previd
     session[:previd]=previd
     session[:results]=@results
     render :action=>"index.rhtml"
 end

 def search(querytype,options)   
   newcond=[]
   i=1
   overwrite=0

   desc=options[:description] #問合せ条件（表記用）
   conditions=options[:cond]  #問合せ条件（実行用）
   cur_args=options[:argsstr].split(/,/)
   cur_args.each{|k|
     if k=~ /-/ then
       value=k.split(/-/)
       if value[1]=="f" then
         newcond.push(params[value[0]].to_f)
       elsif value[1]=="i" then
         newcond.push(params[value[0]].to_i)
       else       
         newcond.push(params[value[0]])
       end
       conditions.gsub!(/%#{i}/,"#{params[value[0]]}")
       desc.gsub!(/%#{i}/,"#{params[value[0]]}")
     else
       newcond.push(params[k])
       conditions.gsub!(/%#{i}/,"#{params[k]}")
       desc.gsub!(/%#{i}/,"#{params[k]}")
     end
     i=i+1
   }
     
   if session[:previd]!=nil then #前回の問合せ分を取り出す
     previd=session[:previd].to_i
     qprev=QueryHistory.find(previd)
     #条件がまったく同じものだったらこれ以降の処理は飛ばす
     if QueryHistory.find(:all,:conditions=>["queryset_id=? and description=?",qprev.queryset_id,desc]).size>0 then    
        @results=session[:results]
        @previd=session[:previd]
        session[:previd]=@previd
        session[:results]=@results
        render :action=>"index.rhtml"
        return
     end
     
     if querytype!="keyword" then
       #前のがあれば上書きする
       qprev=QueryHistory.find(previd)
       qlog=QueryHistory.find(:first,:conditions=>["querytype=? and queryset_id=?",querytype,qprev.queryset_id])
       if qlog then
         qlog.time=Time.now
         overwrite=1
       end
     end
     if overwrite!=1 then
       #新しいのを作る
       qlog=QueryHistory.new
       qlog.querytype=querytype
       qlog.queryset_id=QueryHistory.find(previd).queryset_id
       qlog.time=Time.now     
     end            
     ActiveRecord::Base.connection.execute("drop table if exists gfdnavi_querycache.queryset_#{qlog.queryset_id}")
   else #session[:previd]がない
     #新しいのを作る（queryset_idも新しいの）
     previd=nil
     qlog=QueryHistory.new
     qlog.querytype=querytype
     qlog.queryset_id=QueryHistory.new_setid
   end
   qlog.save            
   qlog.description=desc
   qlog.conditions=conditions
   
   if overwrite==1 then
     ActiveRecord::Base.connection.execute("drop table if exists gfdnavi_querycache.cache_#{qlog.id}")
   end
   
   curqstr=<<-EOM
        create table gfdnavi_querycache.cache_#{qlog.id} as 
        select p.child as vid
        from 
        (select #{options[:select]}
          from #{options[:table]}
          where #{qlog.conditions}) r, pathrelations p
        where r.vid = p.parent;
   EOM
   ActiveRecord::Base.connection.execute(curqstr)
   qlog.count=Node.find_by_sql("select count(*) as cnt from gfdnavi_querycache.cache_#{qlog.id}")[0].cnt
   qlog.save   
   
   #すべての条件を合わせる
   fromstr=[]
   wherestr=[]
   cnt=0
   qhstr=<<-EOM
     select *
     from query_histories
     where queryset_id=?
     order by count;
   EOM
   qhist=QueryHistory.find_by_sql([qhstr,qlog.queryset_id])
   if qhist then
     qhist.each{ |qh|
        f="gfdnavi_querycache.cache_#{qlog.id} n#{cnt}"
        fromstr.push(f)
        if cnt>0 then
          c=cnt-1
          w="n#{c}.vid=n#{cnt}.vid"
          wherestr.push(w)          
        end
        cnt=cnt+1
     }
   end
   if cnt>1 then
     allqstr=<<-EOM
       create table gfdnavi_querycache.queryset_#{qlog.queryset_id} as 
       select n0.vid
       from #{fromstr.join(",")}
       where #{wherestr.join(" and ")}
     EOM
   else
     allqstr=<<-EOM
       create table gfdnavi_querycache.queryset_#{qlog.queryset_id} as 
       select n0.vid
       from #{fromstr[0]}       
     EOM
   end
   ActiveRecord::Base.connection.execute(allqstr)         
   @results=ExplorerResult.new(qlog.id)
   @previd=qlog.id
   session[:previd]=qlog.id
   session[:results]=@results
   render :action=>"index.rhtml"
 end
end
