<%
use Data::Page ();
use Mojo::JSON ();
use Encode ();
use Data::Dumper ();
# Command
my $command = $dbviewer->command;
# Validation
my $params = $command->params($self);
my $ops = [
'',
'contains',
'like',
'in',
'=',
'<>',
'<',
'<=',
'>',,
'>=',
'is null',
'is not null',
'is space',
'is not space'
];
my $rule = [
database => {default => ''} => [
'safety_name'
],
table => {default => ''} => [
'safety_name'
],
c1 => [
'safety_name || blank'
],
c2 => [
'safety_name || blank'
],
c3 => [
'safety_name || blank'
],
v1 => [
'not_blank'
],
v2 => [
'not_blank'
],
v3 => [
'not_blank'
],
op1 => [
{in_array => $ops}
],
op2 => [
{in_array => $ops}
],
op3 => [
{in_array => $ops}
],
sk1 => [
'safety_name'
],
sk2 => [
'safety_name'
],
so1 => [
'any'
],
so2 => [
'any'
],
u => {default => 'and'} => [
{in_array => ['and', 'or']}
],
j => [
'any'
]
];
my $vresult = $dbviewer->validator->validate($params, $rule);
my $vparams = $vresult->data;
my $database = $vparams->{database};
my $table = $vparams->{table};
my $join_mode = $vparams->{j};
# Paramters
# DBI
my $dbi = $dbviewer->dbi;
my $union = $vparams->{u};
my $charset = stash('charset');
my $where = $dbi->where;
my $merged_param = {};
my @clauses;
for my $num (1 .. 3) {
my $column = $vparams->{"c$num"};
my $op = $vparams->{"op$num"};
my $value = $vparams->{"v$num"};
my $db_op;
my $param;
my $clause;
if (defined $column && $column ne '' && defined $op && $op ne '') {
my $db_value;
if ($op eq 'contains') {
$db_value = "%$value%";
$db_value = Encode::encode($charset, $db_value);
$clause = ":${column}{like}";
$param = {$column => $db_value};
}
elsif ($op eq 'in') {
my $values = $value;
$values =~ s/^ +//;
$values =~ s/ +$//;
my @values = split / +/, $values;
for my $value (@values) {
$value = Encode::encode($charset, $value);
}
$clause = "$column in (" . join(',', (":$column") x @values) . ")";
$param = {$column => \@values};
}
elsif ($op eq 'is null' || $op eq 'is not null') {
$clause = "$column $op";
$param = {};
}
elsif ($op eq 'is space' || $op eq 'is not space') {
$db_op = $op eq 'is space' ? '=' : '<>';
$clause = "$column $db_op ''";
$param = {};
}
else {
$db_op = $op;
$db_value = $value;
$clause = ":${column}{$db_op}";
$db_value = Encode::encode($charset, $db_value) if defined $db_value;
$param = {$column => $db_value};
}
push @clauses, $clause;
for my $column (keys %$param) {
if (exists $merged_param->{$column}) {
if (ref $param->{$column} eq 'ARRAY') {
push @{$merged_param->{$column}}, @{$param->{$column}};
}
else {
push @{$merged_param->{$column}}, $param->{$column};
}
}
else {
if (ref $param->{$column} eq 'ARRAY') {
$merged_param->{$column} = [@{$param->{$column}}];
}
else {
$merged_param->{$column} = [$param->{$column}];
}
}
}
}
}
$where->clause([$union, @clauses]);
$where->param($merged_param);
# Order by
my @order_bys;
for my $num (1 .. 2) {
my $sort_key = $vparams->{"sk$num"};
if (defined $sort_key) {
my $column = "$sort_key";
my $order = param("so$num") || '';
$order = '' unless $order eq 'desc';
push @order_bys, "$column $order";
}
}
my $order_by = @order_bys ? 'order by ' . join(',', @order_bys) : '';
# Limit
my $page = param('page') || 1;
my $count = 100;
my $offset = ($page - 1) * $count;
# Join
my $join_clauses = $join->{$table} || [];
my $joined_tables = [];
for my $clause (@$join_clauses) {
my ($table) = $clause =~ / ([^ ]+) +on/;
if (defined $table && ! grep { $_ eq $table } @$joined_tables) {
push @$joined_tables, $table;
}
}
# Header for join tables
my $select_columns = [];
my $select_sorted_columns = [];
my $table_columns = [];
if ($join_mode) {
for my $t ($table, @$joined_tables) {
my $columns = [];
my $result = $dbi->select(table => "$database.$t", where => '1 <> 1');
my $header2 = $result->header;
for my $h (sort @$header2) {
push @$select_sorted_columns, "$t.$h";
}
for my $h (@$header2) {
push @$select_columns, "$t.$h";
push @$columns, $h;
}
push @$table_columns, {$t => $columns};
}
}
else {
my $result = $dbi->select(table => "$database.$table", where => '1 <> 1');
$select_columns = $result->header;
@$select_sorted_columns = sort @{$result->header};
}
# Select
my $column;
if ($join_mode) {
$column = $table_columns;
}
else { $column = '*' }
my $result = $dbi->select(
table => "$database.$table",
column => $column,
where => $where,
append => "$order_by limit $offset, $count",
join => $join_clauses
);
my $header = $result->header;
my $rows = $result->fetch_all;
my $sql = $dbi->last_sql;
# Pager
my $total = $dbi->select(
'count(*)',
table => "$database.$table",
where => $where,
join => $join_clauses
)->value;
my $pager = Data::Page->new($total, $count, $page);
# Global variable for included templates
stash(pager => $pager);
my $output = param('output') || '';
if ($output eq 'json') {
for my $row (@$rows) {
for my $column (@$row) {
$column = Encode::decode($charset, $column);
}
}
$self->render(json => {header => $header, rows => $rows});
return;
}
%>
% layout 'dbviewer_common', title => "Select statement";
%= javascript begin
// Check all column checkbox or none
$(document).ready(function () {
// Check all display columns
$('.check-all-btn').on('click', function () {
$('input[name="h"]').prop('checked', true);
});
// Check none displya columns
$('.check-none-btn').on('click', function () {
$('input[name="h"]').prop('checked', false);
});
// Initialize columns
var at_least_checked;
$('input[name="h"]').each(function () {
if ($(this).prop('checked')) {
at_least_checked = 1;
}
});
if (at_least_checked) {
$('input[name="h"]').each(function () {
if ($(this).prop('checked')) {
var num = $(this).val();
$('.header-' + num).css('display', 'table-cell');
$('.column-' + num).css('display', 'table-cell');
}
else {
var num = $(this).val();
$('.header-' + num).css('display', 'none');
$('.column-' + num).css('display', 'none');
}
});
}
});
% end
%= include '/dbviewer/navi';
<h3>Select statement <%= $join_mode ? '(join mode)' : '' %></h3>
<div class="well well-small" style="background-color:white">
<form method="get" action="<%= url_for %>" style="margin-bottom:5px">
<div>
<span class="label">Condition</span>
<%= select_field u => ['and', 'or'], style => "width:70px" %>
</div>
% my $sops = [@$ops];
% splice @$sops, 0, 1, ['---' => ''];
<div class="form-inline" style="margin-bottom:1px">
<%= select_field c1 => [['---' => ''], @$select_sorted_columns], style => "width:250px" %>
<%= select_field op1 => $sops, style => "width:130px" %>
<%= input_tag 'v1', style => "width:470px" %>
</div>
<div class="form-inline" style="margin-bottom:1px">
<%= select_field c2 => [['---' => ''], @$select_sorted_columns], style => "width:250px" %>
<%= select_field op2 => $sops, style => "width:130px" %>
<%= input_tag 'v2', style => "width:470px" %>
</div>
<div class="form-inline" style="margin-bottom:10px">
<%= select_field c3 => [['---' => ''], @$select_sorted_columns], style => "width:250px" %>
<%= select_field op3 => $sops, style => "width:130px" %>
<%= input_tag 'v3', style => "width:470px" %>
</div>
<div class="label">Order</div>
<div style="margin-bottom:10px">
<span class="form-inline" style="margin-right:10px">
<%= select_field 'sk1' => [['---' => ''], @$select_sorted_columns], style => "width:250px" %>
<%= select_field 'so1' => [['---' => ''], 'asc', 'desc'], style => "width:130px" %>
</span>
<span class="form-inline" style="margin-bottom:10px">
<%= select_field 'sk2' => [['---' => ''], @$select_sorted_columns], style => "width:250px" %>
<%= select_field 'so2' => [['---' => ''], 'asc', 'desc'], style => "width:130px" %>
</span>
</div>
<div>
<span class="label">
Columns
</span>
<span class="check-all-btn btn btn-mini btn-info">
All
</span>
<span class="check-none-btn btn btn-mini btn-info">
None
</span>
</div>
<div class="form-inline" style="margin-bottom:10px">
% for (my $i = 0; $i < @$select_columns; $i++) {
<label class="checkbox inline">
% my $h = $select_columns->[$i];
<%= check_box h => $i + 1 %> <%= $h %>
</label>
% }
</div>
<div>
% if (@$join_clauses) {
<div>
<span class="label" style="margin-right:5px">
Join mode
</span>
<span>
% if (scalar param('j')) {
<a href="<%= url_with->query([j => '']) %>">Off</a>
% } else {
<a href="<%= url_with->query([j => 1]) %>">On</a>
% }
</span>
</div>
% }
</div>
<div><%= submit_button 'Execute', class => 'btn', style => "margin-top:5px;width:150px" %></div>
%= hidden_field database => scalar param('database');
%= hidden_field table => scalar param('table');
%= hidden_field j => scalar param('j');
</form>
</div>
<div class="well well-small">
<div><b>Query</b></div>
<div><%= $sql %></div>
% local $Data::Dumper::Sortkeys = 1;
% my $param_dump = Data::Dumper::Dumper($merged_param);
% $param_dump =~ s/.+?= //;
<div><b>Parameter:</b> <%= $param_dump %></div>
</div>
<div>
% my $first_count = ($pager->current_page - 1) * $pager->entries_per_page + 1;
% my $last_count = $first_count + $pager->entries_on_this_page - 1;
<%= $first_count %> to <%= $last_count %> (Total <i><%= $pager->total_entries %></i>)
</div>
<table class="table table-bordered">
<tr class="success">
% for (my $i = 0; $i < @$header; $i++) {
% my $h = $header->[$i];
% my $h_class = $h;
% $h_class =~ s/\./-/g;
<th class="header-<%= $h_class %> header-<%= $i + 1 %>"><%= $h %></th>
% }
</tr>
% for (my $i = 0; $i < @$rows; $i++) {
% my $row = $rows->[$i];
<tr class="row-<%= $i + 1 %>">
% for (my $k = 0; $k < @$row; $k++) {
% my $value = Encode::decode($charset, $row->[$k]);
% my $h = $header->[$k];
% my $h_class = $h;
% $h_class =~ s/\./-/g;
<td class="column-<%= $h_class %> column-<%= $k + 1 %>"><%= $value %></td>
% }
</tr>
% }
</table>
% my $first_page = $pager->first_page;
% my $current_page = $pager->current_page;
% my $last_page = $pager->last_page;
% if ($last_page > 1) {
<div>
</div>
<div class="pagination pagination-centered">
<ul>
<li>
<a style="color:#6666FF;font-size:70%" href="<%= url_with->query([page => 1]) %>">
Fisrt
</a>
</li>
% my $prev_page = $current_page - 1;
% $prev_page = 1 if $prev_page < 1;
<li>
<a href="<%= url_with->query([page => $current_page - 1]) %>">
<b>«</b>
</a>
</li>
% my $navi_first_page = $current_page - 10;
% my $navi_last_page = $current_page + 9;
% if ($navi_first_page < 1) {
% $navi_last_page += 1 - $navi_first_page;
% $navi_first_page = 1;
% }
% if ($navi_last_page > $last_page) {
% $navi_last_page = $last_page;
% $navi_first_page = $navi_last_page - 19;
% $navi_first_page = 1 if $navi_first_page < 1;
% }
% for my $page ($navi_first_page .. $navi_last_page) {
<li class="<%= $page == $current_page ? 'active' : 'disable' %>">
<a href="<%= url_with->query([page => $page]) %>">
<%= $page %>
</a>
</li>
% }
% my $next_page = $current_page + 1;
% $next_page = $last_page if $next_page > $last_page;
<li>
<a href="<%= url_with->query([page => $next_page]) %>">
<b>»</b>
</a>
</li>
<li>
<a style="color:#6666FF;font-size:70%" href="<%= url_with->query([page => $last_page]) %>">
Last
</a>
</li>
</ul>
</div>
% }