Finished characters report, filtering for keys API call

3 files changed, 180 insertions, 34 deletions

diff --git a/web/lib/api/db.rb b/web/lib/api/db.rb
index 56ca45f..04bcc45 100644
--- a/web/lib/api/db.rb
+++ b/web/lib/api/db.rb
@@ -1,13 +1,27 @@
 # api/db.rb
 class Taginfo < Sinatra::Base
 
+    @@filters = {
+        :characters_space       => "characters='space'",
+        :characters_problematic => "characters='problem'"
+    }
+
     get '/api/2/db/keys' do
+
+        if params[:filters]
+            filters = params[:filters].split(',').map{ |f| @@filters[f.to_sym] }.compact
+        else
+            filters = []
+        end
+
         total = @db.count('db.keys').
             condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            conditions(filters).
             get_first_value().to_i
         
         res = @db.select('SELECT * FROM db.keys').
             condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            conditions(filters).
             order_by(params[:sortname], params[:sortorder]) { |o|
                 o.key
                 o.count_all
diff --git a/web/lib/sql.rb b/web/lib/sql.rb
index af03825..0394a65 100644
--- a/web/lib/sql.rb
+++ b/web/lib/sql.rb
@@ -78,6 +78,13 @@ module SQL
             self
         end
 
+        def conditions(cond)
+            cond.each do |cond|
+                condition(cond)
+            end
+            self
+        end
+
         def order_by(values, direction='ASC', &block)
 
             if values.is_a?(Array)
diff --git a/web/views/reports/characters_in_keys.erb b/web/views/reports/characters_in_keys.erb
index 52e5595..7c450dd 100644
--- a/web/views/reports/characters_in_keys.erb
+++ b/web/views/reports/characters_in_keys.erb
@@ -4,38 +4,163 @@
 case latin letters (a-z) and the underscore (_) are used, sometimes the colon
 (:) is added as a separator character.</p>
 
-<table class="list">
-    <tr><th></th><th>Count</th><th>Fraction</th><th>Description</th><th></th></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_plain') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_plain') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Only latin lowercase letters (<span class="char">a</span> to <span class="char">z</span>) and underscore (<span class="char">_</span>), first and last characters are letters.</td>
-    <td class="border"></td></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_colon') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_colon') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Like above but with one or more colons (<span class="char">:</span>) inside.</td>
-    <td class="border"></td></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_letters') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_letters') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Like above but with uppercase latin letters or letters from other alphabets.</td>
-    <td class="border"></td></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_space') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_space') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Contain whitespace (space, tab, new line, carriage return, or whitespace characters from other alphabets).</td>
-    <td class="border"></td></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_problem') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_problem') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Contain possibly problematic characters <span class="char">=+/&amp;&lt;&gt;;'"?%#@\,</span>.</td>
-    <td class="border"></td></tr>
-    <tr><td class="border"></td>
-        <td class="number"><%= @db.stats('characters_in_keys_rest') %></td>
-        <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_rest') * 100.0 / @db.stats('num_keys') %>%</td>
-        <td>Everything else.</td>
-    <td class="border"></td></tr>
-    <tr><th colspan="5"/></tr>
-</table>
+<div id="tabs">
+    <ul>
+        <li><a href="#tabs-statistics">Statistics</a></li>
+        <li><a href="#tabs-whitespace">Whitespace</a></li>
+        <li><a href="#tabs-problematic">Problematic</a></li>
+    </ul>
+    <div id="tabs-statistics">
+        <h2>Statistics</h2>
+        <table class="list">
+            <tr><th></th><th>Count</th><th>Fraction</th><th>Description</th><th></th></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_plain') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_plain') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Only latin lowercase letters (<span class="char">a</span> to <span class="char">z</span>) and underscore (<span class="char">_</span>), first and last characters are letters.</td>
+            <td class="border"></td></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_colon') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_colon') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Like above but with one or more colons (<span class="char">:</span>) inside.</td>
+            <td class="border"></td></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_letters') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_letters') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Like above but with uppercase latin letters or letters from other alphabets.</td>
+            <td class="border"></td></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_space') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_space') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Contain whitespace (space, tab, new line, carriage return, or whitespace characters from other alphabets).</td>
+            <td class="border"></td></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_problem') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_problem') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Contain possibly problematic characters: <span class="char">=+/&amp;&lt;&gt;;'"?%#@\,</span>.</td>
+            <td class="border"></td></tr>
+            <tr><td class="border"></td>
+                <td class="number"><%= @db.stats('characters_in_keys_rest') %></td>
+                <td class="number"><%= sprintf '%.2f', @db.stats('characters_in_keys_rest') * 100.0 / @db.stats('num_keys') %>%</td>
+                <td>Everything else.</td>
+            <td class="border"></td></tr>
+            <tr><th colspan="5"/></tr>
+        </table>
+    </div>
+    <div id="tabs-whitespace">
+        <h2>Keys With Whitespace</h2>
+        <p>Keys that contain whitespace characters such as space, tab, new line, carriage return, or whitespace characters from other alphabets.</p>
+        <table id="keys-whitespace">
+        </table>
+    </div>
+    <div id="tabs-problematic">
+        <h2>Keys With Possibly Problematic Characters</h2>
+        <p>Keys that contain possibly problematic characters: <span class="char">=+/&amp;&lt;&gt;;'"?%#@\,</span>.</p>
+        <table id="keys-problematic">
+        </table>
+    </div>
+</div>
 
+<script type="text/javascript">
+    jQuery(document).ready(function() {
+        var tabs = jQuery('#tabs').tabs();
+        jQuery('#keys-whitespace').flexigrid({
+            url: '/api/2/db/keys?filters=characters_space',
+            method: 'GET',
+            dataType: 'json',
+            colModel: [
+                { display: 'Key',               name: 'key',      width: 180, sortable: true, align: 'left' },
+                { display: '<span title="Number of objects with this key"><img src="/img/types/all.16.png" alt=""/> Total</span>',           name: 'count_all',        width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of nodes with this key"><img src="/img/types/node.16.png" alt=""/> Nodes</span>',            name: 'count_nodes',      width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of ways with this key"><img src="/img/types/way.16.png" alt=""/> Ways</span>',               name: 'count_ways',       width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of relations with this key"><img src="/img/types/relation.16.png" alt=""/> Relation</span>', name: 'count_relations',  width: 250, sortable: true, align: 'center' },
+                { display: 'Users', name: 'users_all', width: 44, sortable: true, align: 'right' },
+                { display: '<img src="/img/sources/wiki.16.png" alt="Wiki" title="Wiki"/>', name: 'in_wiki', width: 20, sortable: true, align: 'center' },
+                { display: '<img src="/img/sources/josm.16.png" alt="JOSM" title="JOSM"/>', name: 'in_josm', width: 20, sortable: true, align: 'center' },
+             //   { display: '<img src="/img/sources/potlatch.16.png" alt="Potlatch 2" title="Potlatch 2"/>', name: 'in_potlatch', width: 20, sortable: true, align: 'center' },
+             //   { display: '<img src="/img/sources/merkaartor.16.png" alt="Merkaartor" title="Merkaartor"/>', name: 'in_merkaartor', width: 20, sortable: true, align: 'center' },
+                { display: '<span title="Number of different values for this key">Values</span>', name: 'values_all', width: 70, sortable: true, align: 'right' },
+                { display: 'Prevalent Values', name: 'prevalent_values', width: 500, sortable: true, align: 'left' }
+            ],
+            searchitems: [
+                { display: 'Key', name: 'key' }
+            ],
+            sortname: 'count_all',
+            sortorder: 'desc',
+            showToggleBtn: false,
+            usepager: true,
+            useRp: true,
+            rp: 15,
+            rpOptions: [10,15,20,25,50,100],
+            height: 420,
+            preProcess: function(data) {
+                data.rows = jQuery.map(data.data, function(row, i) {
+                    return { 'cell': [
+                        link_to_key(row.key),
+                        print_value_with_percent(row.count_all,       row.count_all_fraction),
+                        print_value_with_percent(row.count_nodes,     row.count_nodes_fraction),
+                        print_value_with_percent(row.count_ways,      row.count_ways_fraction),
+                        print_value_with_percent(row.count_relations, row.count_relations_fraction),
+                        print_with_ts(row.users_all),
+                        row.in_wiki       ? '&#x2714;' : '-',
+                        row.in_josm       ? '&#x2714;' : '-',
+                 //       row.in_potlatch   ? '&#x2714;' : '-',
+                 //       row.in_merkaartor ? '&#x2714;' : '-',
+                        print_with_ts(row.values_all),
+                        print_tag_list(row.key, row.prevalent_values)
+                    ] };
+                });
+                return data;
+            }
+        });
+        jQuery('#keys-problematic').flexigrid({
+            url: '/api/2/db/keys?filters=characters_problematic',
+            method: 'GET',
+            dataType: 'json',
+            colModel: [
+                { display: 'Key',               name: 'key',      width: 180, sortable: true, align: 'left' },
+                { display: '<span title="Number of objects with this key"><img src="/img/types/all.16.png" alt=""/> Total</span>',           name: 'count_all',        width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of nodes with this key"><img src="/img/types/node.16.png" alt=""/> Nodes</span>',            name: 'count_nodes',      width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of ways with this key"><img src="/img/types/way.16.png" alt=""/> Ways</span>',               name: 'count_ways',       width: 250, sortable: true, align: 'center' },
+                { display: '<span title="Number of relations with this key"><img src="/img/types/relation.16.png" alt=""/> Relation</span>', name: 'count_relations',  width: 250, sortable: true, align: 'center' },
+                { display: 'Users', name: 'users_all', width: 44, sortable: true, align: 'right' },
+                { display: '<img src="/img/sources/wiki.16.png" alt="Wiki" title="Wiki"/>', name: 'in_wiki', width: 20, sortable: true, align: 'center' },
+                { display: '<img src="/img/sources/josm.16.png" alt="JOSM" title="JOSM"/>', name: 'in_josm', width: 20, sortable: true, align: 'center' },
+             //   { display: '<img src="/img/sources/potlatch.16.png" alt="Potlatch 2" title="Potlatch 2"/>', name: 'in_potlatch', width: 20, sortable: true, align: 'center' },
+             //   { display: '<img src="/img/sources/merkaartor.16.png" alt="Merkaartor" title="Merkaartor"/>', name: 'in_merkaartor', width: 20, sortable: true, align: 'center' },
+                { display: '<span title="Number of different values for this key">Values</span>', name: 'values_all', width: 70, sortable: true, align: 'right' },
+                { display: 'Prevalent Values', name: 'prevalent_values', width: 500, sortable: true, align: 'left' }
+            ],
+            searchitems: [
+                { display: 'Key', name: 'key' }
+            ],
+            sortname: 'count_all',
+            sortorder: 'desc',
+            showToggleBtn: false,
+            usepager: true,
+            useRp: true,
+            rp: 15,
+            rpOptions: [10,15,20,25,50,100],
+            height: 420,
+            preProcess: function(data) {
+                data.rows = jQuery.map(data.data, function(row, i) {
+                    return { 'cell': [
+                        link_to_key(row.key),
+                        print_value_with_percent(row.count_all,       row.count_all_fraction),
+                        print_value_with_percent(row.count_nodes,     row.count_nodes_fraction),
+                        print_value_with_percent(row.count_ways,      row.count_ways_fraction),
+                        print_value_with_percent(row.count_relations, row.count_relations_fraction),
+                        print_with_ts(row.users_all),
+                        row.in_wiki       ? '&#x2714;' : '-',
+                        row.in_josm       ? '&#x2714;' : '-',
+                 //       row.in_potlatch   ? '&#x2714;' : '-',
+                 //       row.in_merkaartor ? '&#x2714;' : '-',
+                        print_with_ts(row.values_all),
+                        print_tag_list(row.key, row.prevalent_values)
+                    ] };
+                });
+                return data;
+            }
+        });
+    });
+</script>