=begin
TO DO LIST:
- Make sentenceNumbers become ints in the database - this will make sorting easier
- Sungear
- Ethnologue hierarchy for languages
- Search button for within properties to narrow down what is viewed
- Rethinking the logic
- Slider bar for similarity
- Percentage complete for browsing pages
- Using previous queries as constraints on a new search
=end
require 'set'
require 'json/objects'
class TerraLinguaController < ApplicationController
layout 'main_layout'
def homepage
end
def glossary
end
def cross_languages
pv = params[:id].split("%")
@x_langs = params[:langs]
if(@x_langs != nil)
@l = Language.find_by_sql("SELECT DISTINCT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + pv[0] + "\"" +
" AND L1.value = \"" +pv[1] + "\"" +
" AND L2.property = \"" + pv[2] + "\"" +
" AND L2.value = \"" + pv[3] + "\"
AND L1.language IN " + @x_langs)
else
@l = Language.find_by_sql("SELECT DISTINCT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + pv[0] + "\"" +
" AND L1.value = \"" +pv[1] + "\"" +
" AND L2.property = \"" + pv[2] + "\"" +
" AND L2.value = \"" + pv[3] + "\"")
end
# this is works only for the case where there selections on properties and languages
end
# sends things to be mapped
def map_it
@last_results = session[:results]
last_params = session[:params]
@latlongs = []
@latlong = []
# If last search was a cross
if(last_params["Property_all_any"] == "cross")
@x_langs = nil # used if there are constraints on languages
group = "a"
#Build set of selected languages, if any
if(last_params.member?("Language_options"))
@x_langs = last_params[:Language_options][:language]
x_langs_array = @x_langs
x = @x_langs.collect { |i| '"' + i + '"'}
@x_langs = "(" + x.inject { |i,j| i + ", " + j} + ")"
end
#Get the languages for the cross
for r in @last_results
@latlongs = []
if(@x_langs != nil)
languages = Language.find_by_sql("SELECT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + r.p1 + "\"" +
" AND L1.value = \"" + r.v1 + "\"" +
" AND L2.property = \"" + r.p2 + "\"" +
" AND L2.value = \"" + r.v2 + "\"" +
" AND L1.language IN " + @x_langs)
else
languages = Language.find_by_sql("SELECT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + r.p1 + "\"" +
" AND L1.value = \"" + r.v1 + "\"" +
" AND L2.property = \"" + r.p2 + "\"" +
" AND L2.value = \"" + r.v2 + "\"")
end # end if(@x_langs != nil)
# Find the co-ordinates for these languages and put them into groups
for language in languages
#Get Co-ordinates
temp = Language.find_by_sql("SELECT language, value
FROM languages
WHERE language = \"" + language.language + "\"
AND property = \"latlong\"")
if(temp.size != 0)
l = Lang.new(temp[0].language, temp[0].value.split(",")[0], temp[0].value.split(",")[1])
@latlong << {"language" => l.language,
"lat" => l.lat.to_f,
"lng" => l.long.to_f,
"SSWLProperty" => r.p1 + "|" + r.p2,
"SSWLValue" => r.v1 + "|" + r.v2,
"group" => group.clone}
end
end
group = group.succ!
end
else
# Only language selected
# Get lat lng of selected languages
for r in @last_results
temp = Language.find_by_sql("SELECT language, value
FROM languages
WHERE language = \"" + r.language + "\"
AND property = \"latlong\"")
if(temp.size != 0)
@latlongs << Lang.new(temp[0].language,
temp[0].value.split(",")[0],
temp[0].value.split(",")[1])
end
end
for l in @latlongs
h = {"language" => l.language, "lat" => l.lat.to_f, "lng" => l.long.to_f, "group" => "a"}
@latlong << h
end
end
my_file = File.open("#{RAILS_ROOT}/public/data/map.json", File::WRONLY|File::TRUNC|File::CREAT)
my_file.puts "{ \"markers\":" + @latlong.to_json.to_s + "}"
my_file.close
# render :text => @latlong.to_json
end
def initialize
super
@checked_items = []
#Set of parameters for search
@itemlist = ['Language', 'Property', 'Property_Value', 'Example']
@priority = ['Prioritize examples']
@data_snapshot = "(SELECT language,property, value
FROM languages
ORDER BY language,property)tmp"
end
def index
# Set of languages, property and property_values for collection_select in form
@languages = Language.find_by_sql("SELECT DISTINCT language
FROM languages ORDER BY language" )
@property_values = Language.find_by_sql("SELECT DISTINCT property, value
FROM languages
WHERE property NOT LIKE '%ISO%'
AND property NOT LIKE '%latlong%'
ORDER BY property,value")
@properties = Property.find_by_sql("SELECT DISTINCT property
FROM languages
WHERE property <> \"\"
AND property NOT LIKE '%ISO%'
AND property NOT LIKE '%latlong%'
ORDER BY property")
end
####################################################################################################
############################################# SEARCH ###############################################
####################################################################################################
def search
############################## GET PARAMETERS USED IN SEARCH ####################################
language_array = []
property_array = []
property_value_array = []
#Build an array out of the selected items
@itemlist.each do |item|
if (params[item] != nil)
@checked_items.push(item)
end
end
# Force property into list if Property_value is selected
if(params["Property_Value"] != nil)
if(params["Property"] == nil)
if(params["Language"] != nil)
@checked_items.insert(1, "Property")
else
@checked_items.insert(0, "Property")
end
end
end
#Build set of selected languages
if(params.member?("Language_options"))
@languages = params[:Language_options][:language]
language_array = @languages
x = @languages.collect { |i| '"' + i + '"'}
@languages = "(" + x.inject { |i,j| i + ", " + j} + ")"
else
@languages = nil
end
x = []
# Build set of selected properties
if(params.member?("Property_options"))
@properties = params[:Property_options][:property]
properties_selected = true
property_array = @properties
x = @properties.collect { |i| '"' + i + '"'}
end
if(!x.empty?) # if properties are selected, put them into a string to be used in the query
@properties = "(" + x.inject { |i,j| i + ", " + j} + ")"
else # otherwise make sure that @properties is nil and the query is treated as an "any"s
@properties = nil
@p_all_or_any == "any"
end
#Build set of selected property_values
if(params.member?("property_value_options"))
@property_values = params[:property_value_options][:property]
property_values_selected = true
property_value_array = @property_values
else
property_values_selected = false
@property_values = []
property_value_array = nil
end
#Check if examples are to be used
if(params.member?("Example"))
examples_selected = true
else
examples_selected = false
end
# All properties or any?
@p_all_or_any = params["Property_all_any"]
# All property_values or any?
@pv_all_or_any = params["Property_value_all_any"]
# If we have an "all" but only one thing is selected, then make it "any".
if((@p_all_or_any == "all" && property_array.size < 2) || @p_all_or_any == nil)
@p_all_or_any = "any"
end
if((@pv_all_or_any == "all" && property_value_array.size < 2) || @pv_all_or_any == nil)
@pv_all_or_any = "any"
end
# Gloss contains
if(params["gloss_contains"] != "")
@gloss_contains = true
else
@gloss_contains = false
end
# if we want to display examples, force property and pv to also be "checked" as well because new record
# is not created properly otherwise - however, they will not display, this is only used to create new record
if(@checked_items.include?("Example") || params[:gloss_contains] != "")
@checked_items[0] = "Language"
@checked_items[1] = "Property"
@checked_items[2] = "Property_Value"
@checked_items[3] = "Example"
examples_selected = true
end
############################## START ACTUAL SEARCH ####################################
tmp_results = [] # stores result of queries as search as performed
@results = [] # final result is written here for rendering
# build sql
sql = "SELECT DISTINCT language, property, value FROM languages "
sql_constraints = ""
sql_end = " ORDER BY language, property"
check_lang = false
check_prop = false
check_pv = false
if(params["Language"] != nil)
check_lang = true
end # end if
if(params["Property"] != nil)
check_prop = true
end # end outer if
if(params["Property_Value"] != nil)
check_pv = true
end # end outer if
# sql for constraints
if(@languages != nil)
lang_constraints = "WHERE language IN " + @languages
end #end if
# properties part
if(@properties != nil and @p_all_or_any != "cross")
if(@languages == nil)
prop_constraints = "WHERE property IN " + @properties
else
prop_constraints = "AND property IN " + @properties
end
end # end outer if
@undefined_func = false
### Case: nothing is selected (no constraints whatsoever)
if(@languages == nil && @properties == nil && !property_values_selected)
tmp_results = Language.find_by_sql(sql+sql_end)
### Case: ONLY languages OR ONLY properties are selected, properties search = 'any' and no pv are selected
elsif(((@languages != nil) ^ (@properties != nil)) && !property_values_selected && @p_all_or_any == "any")
if(@languages != nil)
tmp_results = Language.find_by_sql("SELECT DISTINCT language, property, value FROM languages "+lang_constraints+sql_end) # apply the constraints for properties or languages
else
tmp_results = Language.find_by_sql(sql+prop_constraints+sql_end) # apply the constraints for properties or languages
end # end inner if/else block
### Case: no languages selected, no pv selected, properties selected and property search = 'all'
elsif((@languages == nil) && (@properties != nil) && !property_values_selected && @p_all_or_any == "all")
if(check_prop || check_pv)
tmp_results = []
else
tmp_results = Language.find_by_sql("SELECT languages.language, languages.property, languages.value
FROM languages, (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
" WHERE property IN " + @properties +
" GROUP BY language) AS tmp " +
" WHERE count = " + (property_array.size).to_s +
" AND tmp.language = languages.language" +
" AND languages.property IN " + @properties)
end # end inner if/else block on all properties
# Case: languages and properties selected, property search = "all"
elsif((@languages != nil) && (@properties != nil) && !property_values_selected && @p_all_or_any == "all")
if(check_prop || check_pv)
tmp_results = []
else
tmp_results = Language.find_by_sql("SELECT languages.language, languages.property, languages.value
FROM languages, (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
" WHERE property IN " + @properties +
" AND language IN " + @languages +
" GROUP BY language) AS tmp " +
" WHERE count = " + (property_array.size).to_s +
" AND tmp.language = languages.language" +
" AND languages.property IN " + @properties +
" AND languages.language IN " + @languages)
end # end inner if/else block on all properties
### Case: easy cross where only properties (and possibly languages) are selected
elsif((@properties != nil) && !property_values_selected && @p_all_or_any == "cross")
tmp_results, @zero_count_results = easy_cross(property_array, @languages)
### Case: languages AND properties are selected and properties search = 'any'
elsif((@languages != nil) && (@properties != nil) && !property_values_selected && @p_all_or_any == "any")
tmp_results = Language.find_by_sql(sql+lang_constraints+prop_constraints+sql_end)
elsif((@properties != nil) && (property_values_selected) && @p_all_or_any == "all" && (check_prop || check_pv))
tmp_results = nil
### Case: properties, property values and [maybe] langauges are selected, no cross, only show languages
elsif((@properties != nil) && (property_values_selected) && @p_all_or_any == "all" && !check_prop && !check_pv)
if(@languages != nil)
tmp_results_p = Language.find_by_sql("SELECT language " +
" FROM (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
lang_constraints + prop_constraints +
" GROUP BY language) AS tmp " +
" WHERE count = " + (property_array.size).to_s)
else
tmp_results_p = Language.find_by_sql("SELECT language " +
" FROM (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
prop_constraints +
" GROUP BY language) AS tmp " +
" WHERE count = " + (property_array.size).to_s)
end # end if(@languages != nil) for if(@p_all_or_any == "all")
tmp_results_pv = []
if(@pv_all_or_any == "all")
tmp_results_pv = any_pv(@languages, @properties, @property_values)
tmp_results_pv = all_pv(@property_values, tmp_results_pv)
else # do any for property values
# property values query loop
tmp_results_pv = []
@property_values.each do |p|
new_results = Language.find_by_sql(sql + "WHERE property = \"" + p.split(":")[0] +"\"" +
"AND value = \"" + p.split(":")[1] + "\"")
new_results.each do |r|
tmp_results_pv << r
end
end # end loop
# if selecting neither property nor property value then we just want to intersect based on language
if(check_prop || check_pv)
if(@languages != nil) # add further constraint of languages to pv query
tmp_results_pv.delete_if{|r| !language_array.include?(r.language)}
end
if(@properties != nil)
tmp_results_pv.delete_if{|r| !property_array.include?(r.property)}
end
end
end # end else for if(@pv_all_or_any == "all")
# get common languages
tmp_results_pv.each do |pv|
tmp_results_p.each do |p|
if(pv.language == p.language)
tmp_results << pv
end
end
end # end tmp_results_p.each
tmp_results = my_uniq_langs(tmp_results)
elsif((property_values_selected) && @p_all_or_any == "any")
# do any on properties
if(@languages != nil && @properties != nil)
tmp_results_p = Language.find_by_sql(sql+lang_constraints+prop_constraints+sql_end)
elsif(@properties != nil)
tmp_results_p = Language.find_by_sql(sql+prop_constraints+sql_end)
end # end if(@languages != nil) for else
tmp_results_pv = []
if(@pv_all_or_any == "all")
tmp_results_pv = any_pv(@languages, @properties, @property_values)
tmp_results_pv = all_pv(@property_values, tmp_results_pv)
else # do any for property values
# property values query loop
tmp_results_pv = []
@property_values.each do |p|
new_results = Language.find_by_sql(sql + "WHERE property = \"" + p.split(":")[0] +"\"" +
"AND value = \"" + p.split(":")[1] + "\"")
new_results.each do |r|
tmp_results_pv << r
end
end # end loop
# if selecting neither property nor property value then we just want to intersect based on language
JILLIAN: Eliminate above comment
JILLIAN: Eliminate if(check_prop || check_pv) here
if(check_prop || check_pv)
if(@languages != nil) # add further constraint of languages to pv query
tmp_results_pv.delete_if{|r| !language_array.include?(r.language)}
end
JILLIAN: Add comment: If clicking only on language in showline
JILLIAN: we do want languages that have a P1-v1 and a P2 even if P1 != P2.
JILLIAN: That is why we add the constraint that the following deletion occurs
JILLIAN: only if property or property-value are checked on showline
JILLIAN: if(check_prop || check_pv)
if(@properties != nil)
tmp_results_pv.delete_if{|r| !property_array.include?(r.property)}
end
end # end if(check_prop || check_pv)
end # end else for if(@pv_all_or_any == "all")
if(@properties != nil)
# get common languages
tmp_results_pv.each do |pv|
tmp_results_p.each do |p|
if(pv.language == p.language)
tmp_results << pv
end
end
end # end tmp_results_p.each
else # goes with if(@properties != nil)
tmp_results.concat(tmp_results_pv)
tmp_results.sort!{|r1,r2| [r1.language, r1.property] <=> [r2.language, r2.property]}
end # end if(@properties != nil)
tmp_results = my_uniq_results(tmp_results)
if(!@checked_items.include?("Example"))
tmp_results = my_uniq_langs(tmp_results)
end
else # otherwise, we haven't gotten to this search combination yet - show a message reflecting that
tmp_results = []
@undefined_func = true
end # end if/else block
# this block is relevant for rendering
@to_be_selected = @checked_items.inject {|x,y| x+ ", " + y}
@to_be_selected.downcase!
@attributes = @to_be_selected.split(", ")
@attributes.each {|a| a.downcase!}
@to_be_selected.sub!(", example", "")
if(@p_all_or_any != "cross" && tmp_results != nil)
# if language is the only show checked, make sure they are unique
if(tmp_results.include?(nil))
tmp_results.clear
end
if(@attributes.include?("language") && !@attributes.include?("property") && !@attributes.include?("property_value"))
tmp_results = my_uniq_langs(tmp_results)
elsif(!@attributes.include?("language") && @attributes.include?("property") && !@attributes.include?("property_value"))
tmp_results = my_uniq_props(tmp_results)
elsif(!@attributes.include?("language") && @attributes.include?("property_value")) # if pv selected in show, property is automatically as well
tmp_results = my_uniq_pvs(tmp_results)
end
end
if(tmp_results == nil)
tmp_results = []
elsif(tmp_results.include?(nil))
tmp_results = []
end
if(examples_selected && @p_all_or_any != "cross")
grouping_flag = (!check_prop && !check_pv) # if prop and pv aren't checked we don't want grouping
@results = []
usedNumbers = []
orderedResults = [] # this is used only for the language/example example cases so that results are sorted
inserted_in_results = []
ex_priority = false # if example should take priority over other constraints, this should be true [OPTIONAL MODE]
if(params['Prioritize examples'] != nil)
ex_priority = true
end
line_without_example = false # if we've already printed a line wihtout an example, we don't want to do it again for a particular language
# tmp_results are ordered by language so examples are displayed sorted by language
for r in tmp_results
new_language = false
tmp = Record.new(r.language, r.property, r.value)
# this is used to get the unique examples for each language where only language/example or example are selected
# reset used numbers because language, sentenceNumber is the key for examples
if(usedNumbers[0] != r.language)
# if(orderedResults.empty?)
# orderedResults.sort!{|r1, r2| r1.example.split("
")[1].split(": ")[1].to_i <=> r2.example.split("
")[1].split(": ")[1].to_i}
# end
@results.concat(orderedResults)
orderedResults = []
usedNumbers = []
usedNumbers[0] = r.language
new_language = true
if(!ex_priority)
line_without_example = false
end
inserted_in_results = []
end
# for each language, property, value group, do a query to get the examples that match
sentenceNumbers = []
sentenceNumbers = Example.find_by_sql("SELECT DISTINCT language, sentenceNumber
FROM examples
WHERE property = \"" + r.property + "\"
AND value = \"" + r.value + "\"
AND language = \"" + r.language + "\"
ORDER BY sentenceNumber")
if(grouping_flag)
sentenceNumbers.delete_if{|n| usedNumbers.include?(n.sentenceNumber)}
sentenceNumbers.each do |n|
usedNumbers << n.sentenceNumber
end
end
# end # end if(params["Property_Value"] == nil && params["Property"] == nil)
use_record = false
my_display = false
# for each sentence number that is an example of that language, property, value pair
# do a query to get the words, gloss, translation and comment
for n in sentenceNumbers
# this is used to eliminate grouping where only language/example or example are selected
if(grouping_flag)
tmp = Record.new(r.language, r.property, r.value)
end
example = Example.find_by_sql("SELECT property, value
FROM examples
WHERE sentenceNumber = \"" + n.sentenceNumber.to_s + "\"
AND language = \"" + n.language + "\"")
words = ""
gloss = ""
translation = ""
comment = ""
sentenceNumber = n.sentenceNumber
for e in example
if(e.property == "words")
words = e.value
elsif(e.property == "gloss")
gloss = e.value
elsif(e.property == "translation")
translation = e.value
elsif(e.property == "comment")
comment = e.value
end # end if-else block
end # end for loop for example
# if the ______ does not contain the specified string, my_display is false and
# example will not be inserted into @results
if(words != "" or gloss != "" or translation != "" or comment != "")
my_display = true
if(@gloss_contains)
my_display=false
if(params[:contains] == "gloss contains")
my_display = gloss.downcase.include?(params[:gloss_contains].downcase)
elsif(params[:contains] == "sentence contains")
my_display = words.downcase.include?(params[:gloss_contains].downcase)
elsif(params[:contains] == "translation contains")
my_display = translation.downcase.include?(params[:gloss_contains].downcase)
end
end
if(my_display)
use_record = true
if(!inserted_in_results.include?(sentenceNumber))
tmp.newExample(words, gloss, translation, comment, sentenceNumber)
if(grouping_flag)
inserted_in_results << sentenceNumber
orderedResults << tmp
end
end
end
end # end if(words != "" or gloss != "" or translation != "" or comment != "")
end # end for n in sentenceNumbers
# if only language/example or example are selected, we want to add the record now to
# avoid grouping and make sure that only items with examples are added
if(grouping_flag && !ex_priority && use_record)
if(tmp.example == "" && !line_without_example)
orderedResults << tmp
line_without_example = true
end
elsif((use_record && ex_priority && !grouping_flag)||(!ex_priority))
if(use_record || !line_without_example)
orderedResults << tmp
if(grouping_flag)
line_without_example = true
end
end
end # end if(params["Property_Value"] == nil && params["Property"] == nil && !ex_priority && new_language)
end # end for r in tmp_results
# for the very last language
@results.concat(orderedResults)
# these fields are used to create the new record for examples, so they are in @attributes, however, we do not want
# to render them if they are not checked
if((params["Property"] != nil || params["Property_Value"] != nil) && params["Language"] == nil)
@results.sort!{|r1,r2| [r1.property, r1.language] <=> [r2.property, r2.language]}
else
@results.sort!{|r1, r2| r1.language <=> r2.language}
# @results.sort!{|r1, r2|
# [r1.language, r1.example.split("
")[1].split(": ")[1].to_i] <=> [r2.language, r2.example.split("
")[1].split(": ")[1].to_i]}
end
if(params["Language"] == nil)
@attributes.delete("language")
end
if(params["Property_Value"] == nil)
@attributes.delete("property_value")
end
if(params["Property"] == nil && params["Property_Value"] == nil)
@attributes.delete("property")
end
else # this is for case where examples are not selected
#Languages controller has a field value and not a field property_value
if(@attributes.include?("property_value"))
@attributes.delete("property_value")
@attributes.push("value")
end # end if
@results = tmp_results
end # end if-else block
if(@results == nil)
@results = []
end
@results_link = write_results(@results, @zero_count_results, @attributes, @p_all_or_any, @pv_all_or_any,
@languages, @properties, @property_values, language_array, property_array, property_value_array)
session[:to_be_selected] = @to_be_selected
session[:params] = params
end # end of search function
# removes duplicate elements
def my_uniq_langs(tmp_results)
tmp_results.sort!{|r1, r2| r1.language <=> r2.language}
new_results = []
previous = tmp_results.first
new_results << previous
tmp_results.each do |r|
if(r.language != previous.language)
new_results << r
end
previous = r
end
return new_results
end # end my_uniq
# removes duplicate elements
def my_uniq_props(tmp_results)
tmp_results.sort!{|r1, r2| r1.property <=> r2.property}
new_results = []
previous = tmp_results.first
new_results << previous
tmp_results.each do |r|
if(r.property != previous.property)
new_results << r
end
previous = r
end
return new_results
end # end my_uniq
# removes duplicate elements
def my_uniq_pvs(tmp_results)
tmp_results.sort!{|r1, r2| [r1.property, r1.value] <=> [r2.property, r2.value]}
new_results = []
previous = tmp_results.first
new_results << previous
tmp_results.each do |r|
if(r.property != previous.property || r.value != previous.value)
new_results << r
end
previous = r
end
return new_results
end # end my_uniq
def my_uniq_results(tmp_results)
tmp_results.sort!{|r1,r2| [r1.language, r1.property, r1.value] <=> [r2.language, r2.property, r2.value]}
new_results = []
previous = tmp_results.first
new_results << previous
tmp_results.each do |r|
if(r.language != previous.language || r.property != previous.property || r.value != previous.value)
new_results << r
end
previous = r
end
return new_results
end
# any select based on property values
def any_pv(langs, props, prop_values)
@langs = langs
@props = props
@prop_values = prop_values
tmp_results = []
# property values query loop
sql = "SELECT DISTINCT language, property, value FROM languages WHERE"
if(@langs != nil)
sql += " language IN " + @langs
end
if(@props != nil)
if(@langs != nil)
sql += " AND property IN " + @props
else
sql += " property IN " + @props
end
end
if(@langs != nil || @props != nil)
sql += " AND"
end
@prop_values.each do |p|
new_results = Language.find_by_sql(sql + " property = \"" + p.split(":")[0] +"\"" +
" AND value = \"" + p.split(":")[1] + "\"")
new_results.each do |r|
tmp_results << r
end
end # end loop
tmp_results.sort!{|r1,r2| [r1.language, r1.property] <=> [r2.language, r2.property]}
return tmp_results
end
# This does the select based on property values all
# We find the languages containing all these property-values
# and then remove all rows not containing those languages.
def all_pv(prop_values, tmp_results)
@prop_values = prop_values
# build sql to get list of common languages
sql = "SELECT * FROM (SELECT language, COUNT(DISTINCT property) count " +
"FROM languages " +
"WHERE "
first = true
for pv in @prop_values
if(first)
sql = sql + "(property = \"" + pv.split(":")[0] + "\" AND value = \"" + pv.split(":")[1] + "\") "
first = false
else
sql = sql + "OR (property = \"" + pv.split(":")[0] + "\" AND value = \"" + pv.split(":")[1] + "\") "
end
end
sql = sql + "GROUP BY language) AS tmp WHERE count = " + (@prop_values.size).to_s
common_languages = Language.find_by_sql(sql)
tmp_lang = []
for l in common_languages
tmp_lang << l.language
end
tmp_results.delete_if{|r| !tmp_lang.include?(r.language)}
return tmp_results
end # end of all_pv
# This does the select based on properties all
# We find the languages containing all these properties and then remove
# all rows not containing those languages.
def all_p(properties, languages, tmp_results)
# x = properties.collect { |i| '"' + i + '"'}
# @properties = "(" + x.inject { |i,j| i + ", " + j} + ")"
@properties = properties
@languages = languages
if(@languages != nil)
common_languages = Language.find_by_sql("SELECT * " +
" FROM (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
" WHERE property IN " + @properties +
" AND language IN " + @languages +
" GROUP BY language) AS tmp " +
" WHERE count = " + (properties.size).to_s)
else
common_languages = Language.find_by_sql("SELECT * " +
" FROM (SELECT language, COUNT(DISTINCT property) count " +
" FROM languages " +
" WHERE property IN " + @properties +
" GROUP BY language) AS tmp " +
" WHERE count = " + (properties.size).to_s)
end
# " WHERE count = " + (@properties.count(",")+1).to_s)
# we use count here whereas we use size for property-values
# because @properties is a string, whereas @prop_values is an array -
# can make them the same by using properties, which is an array -
# count is based on the number of delimiting marks (commas), which is why there is +1
tmp_lang = []
for l in common_languages
tmp_lang << l.language
end
tmp_results.delete_if{|r| !tmp_lang.include?(r.language)}
return tmp_results
end # end of all_p
# easy cross works when there are no constraints on languages or property_values
def easy_cross(properties, languages)
@zero_count_results = []
@properties = properties
#Build set of selected properties
if(@properties.size == 2)
x = Language.find_by_sql("SELECT DISTINCT property, value FROM languages WHERE property = \"" + @properties[0] + "\"")
y = Language.find_by_sql("SELECT DISTINCT property, value FROM languages WHERE property = \"" + @properties[1] + "\"")
cross = []
x.each do |i|
y.each do |j|
cross << i.property+":"+i.value+":"+j.property+":"+j.value
end
end # end of nested loop
# selecting the result set
first = true
for pv_pair in cross
pv = pv_pair.split(":")
if(languages != nil)
result = Language.find_by_sql("SELECT L1.property AS p1, L1.value AS v1,
L2.property AS p2, L2.value AS v2,
COUNT(DISTINCT L1.language) AS count
FROM languages AS L1, languages AS L2
WHERE L1.language = L2.language
AND L1.property = \""+pv[0] + "\"
AND L1.value = \"" +pv[1] + "\"
AND L2.property = \""+pv[2] + "\"
AND L2.value = \"" + pv[3] + "\"
AND L1.language IN " + languages)
else
result = Language.find_by_sql("SELECT L1.property AS p1, L1.value AS v1,
L2.property AS p2, L2.value AS v2,
COUNT(DISTINCT L1.language) AS count
FROM languages AS L1, languages AS L2
WHERE L1.language = L2.language
AND L1.property = \""+pv[0] + "\"
AND L1.value = \"" +pv[1] + "\"
AND L2.property = \""+pv[2] + "\"
AND L2.value = \"" + pv[3] + "\"")
end # end if(languages != nil)
if(result[0].p1 != nil)
@results << result[0]
else
@zero_count_results << pv[0] + ":" + pv[1] + ":" + pv[2] + ":" + pv[3]
end
end # end of for loop
end # end of if(@properties_size==2)
# return @results, which contains combinations where count > 0 and
# @zero_count_results, which contains combinations where count = 0
return @results, @zero_count_results
end # end of easy cross
# write results to a pipe-delimited file
def write_results(results, zero_count_results, attributes, p_all_or_any, pv_all_or_any, languages,
properties, property_values, language_array, property_array, property_value_array)
new_file = File.open("#{RAILS_ROOT}/public/results" + params[:authenticity_token] +
".csv", File::WRONLY|File::TRUNC|File::CREAT)
new_file.puts "QUERY INFORMATION"
# print out fields checked in show line
new_file.print "Show Line|"
if(params['Language'] != nil)
new_file.print "Language"
if(params['Property'] != nil)
new_file.print "|Property"
if(params['Property_Value'] != nil)
new_file.print "|Property_Value"
if(params['Example'] != nil)
new_file.print "|Example"
end
elsif(params['Example'] != nil)
new_file.print "|Example"
end
elsif(params['Property_Value'] != nil)
new_file.print "|Property_Value"
if(params['Example'] != nil)
new_file.print "|Example"
end
end
elsif(params['Property'] != nil)
new_file.print "Property"
if(params['Property_Value'] != nil)
new_file.print "|Property_Value"
if(params['Example'] != nil)
new_file.print "|Example"
end
elsif(params['Example'] != nil)
new_file.print "|Example"
end
elsif(params['Property_Value'] != nil)
new_file.print "Property_Value"
if(params['Example'] != nil)
new_file.print "|Example"
end
elsif(params['Example'] != nil)
new_file.print "Example"
end # end show line block
new_file.puts ""
new_file.puts ""
# print out selected langauges
new_file.puts "Languages: Any"
if(language_array.size == 0)
new_file.puts "None"
else
language_array.each do |l|
new_file.puts l
end
end # end of printing languages
new_file.puts ""
# print out selected properties
new_file.puts "Properties: " + p_all_or_any
if(property_array.size == 0)
new_file.puts "None"
else
property_array.each do |p|
new_file.puts p
end
end # end of printing properties
new_file.puts ""
# print out selected property values
new_file.puts "Property_Values: " + pv_all_or_any
if(property_value_array == nil || property_value_array.size == 0)
new_file.puts "None"
else
property_value_array.each do |pv|
new_file.puts pv
end
end # end of printing property values
new_file.puts ""
new_file.print "Examples Information|"
if(params['Prioritize examples'] != nil)
new_file.print "Prioritize examples = true|"
else
new_file.print "Prioritize examples = false|"
end
new_file.print "Text Constraint: " + params[:contains] + "|"
if(params[:gloss_contains] == "")
new_file.print "NONE"
else
new_file.print params[:gloss_contains]
end
new_file.puts ""
new_file.puts ""
new_file.puts "RESULTS"
#cross results
if(p_all_or_any == "cross")
if(properties == nil)
new_file.puts "No properties selected"
elsif(properties.size != 2)
new_file.puts "Please select exactly 2 properties for Cross."
else
new_file.puts "Property 1|Value 1|Property 2|Value 2|Count"
results.each do |r|
new_file.puts r.p1 + "|" + r.v1 + "|" + r.p2 + "|" + r.v2 + "|" + r.count
end # end for r in results
if(zero_count_results != nil && zero_count_results.size != 0)
zero_count_results.each do |r|
new_file.puts r.split(":")[0] + "|" + r.split(":")[1] + "|" + r.split(":")[2] + "|" + r.split(":")[3] + "|0"
end # end for r in zero_count_results
end # end if(zero_count_results)
new_file.puts ""
results.each do |r|
new_file.puts r.p1 + ":" + r.v1 + "-" + r.p2 + ":" + r.v2
x_langs = []
if(languages != nil)
x_langs = Language.find_by_sql("SELECT DISTINCT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + r.p1 + "\"" +
" AND L1.value = \"" + r.v1 + "\"" +
" AND L2.property = \"" + r.p2+ "\"" +
" AND L2.value = \"" + r.v2 + "\"
AND L1.language IN " + languages)
else
x_langs = Language.find_by_sql("SELECT DISTINCT L1.language FROM languages L1, languages L2
WHERE L1.language = L2.language
AND (L1.property != L2.property OR L1.value != L2.value)
AND L1.property = \"" + r.p1 + "\"" +
" AND L1.value = \"" + r.v1 + "\"" +
" AND L2.property = \"" + r.p2+ "\"" +
" AND L2.value = \"" + r.v2 + "\"")
end # query for x_langs
x_langs.each do |x|
new_file.puts x.language
end
new_file.puts ""
end # results.each do r
end # end if(properties == nil)
elsif(p_all_or_any != "cross" && !results.empty?) # results for not cross
# write attributes
attributes.each do |a|
new_file.print a
if(a != attributes.last) # don't want a pipe after the last element
new_file.print "|"
end
end # end attributes.each
new_file.puts ""
# write results
results.each do |r|
if (params["Language"] == nil && @attributes.include?("example"))
if (params["Property"] != nil || params["Property_Value"] != nil)
new_file.print r.property + "|"
end
if(params["Property_Value"] != nil)
new_file.print r.property_value + "|"
end
if(r.example != "")
new_file.print "Language: " + r.language + "|"
new_file.print r.example.split("
")[1] + "|" + r.example.split("
")[2] +
"|" + r.example.split("
")[3] + "|" + r.example.split("
")[4] + "|" + r.example.split("
")[5]
end
else # examples not selected without language being selected
if(attributes.include?("language"))
new_file.print r.language
if("language" != attributes.last)
new_file.print "|"
end
end
if(attributes.include?("property"))
new_file.print r.property
if("property" != attributes.last)
new_file.print "|"
end
end
if(attributes.include?("value"))
new_file.print r.value
if(("value" != attributes.last))
new_file.print "|"
end
end
if(attributes.include?("property_value"))
new_file.print r.property_value
if("property_value" != attributes.last)
new_file.print "|"
end
end
if(attributes.include?("example"))
if(r.example != "")
new_file.print r.example.split("
")[1] + "|" + r.example.split("
")[2] +
"|" + r.example.split("
")[3] + "|" + r.example.split("
")[4] + "|" + r.example.split("
")[5]
else
new_file.print "NO EXAMPLE"
end
end
end # end if (params["Language"] == nil && @attributes.include?("example"))
new_file.puts "" # new line
end # end results.each
elsif(results.empty?)
new_file.puts "Did not find any results that satisfy your search criteria."
end # end if(p_all_or_any == cross)
new_file.close
return "/results" + params[:authenticity_token] + ".csv"
end # end function write_results
end # end of terra_lingua_controller