package App::Test; 

use strict; 
use base 'CGI::Application';
use CGI::Application::Plugin::JSON ':all';
use DBI;
use JSON;

my $db = 'DBServer';
my $user = 'user';
my $pass = 'passw';
my $mysql_dbh = DBI->connect( "DBI:ODBC:$db", $user, $pass )
                        || print "Connect fail: $!";
# Define run modes 

sub setup { 

    my $self = shift; 

    $self->start_mode('show_page');
    
    $self->error_mode('error'); 

    # The query parameter rm will contain the run mode name in run_modes
    $self->mode_param('rm'); 

    $self->run_modes( 

        'show_page'  => 'show_page',
        'add_stuff'  => 'add_stuff',

    ); 

} 

# Process any fatal errors 

sub error { 

    my $self  = shift; 

    my $error = shift; 

    return "There has been an error: $error"; 

} 

sub show_page { 

    my $self     = shift;
    #my $sql  = "select TOP 5 * from my_table where account <>''";
    #my $show    = $self->_get_data($sql);
    my $template = $self->load_tmpl('test.tmpl');
    #$template->param('data', $show);
    return $template->output();
    
} 

sub add_stuff {

 my $self = shift;
 #my $account = shift;
 my $q    = $self->query;

 my $account = $q->param('term');
 
 my $sql = qq{select name, account FROM master_broker where account like ?};
 my $data_handle = $mssql_dbh->prepare($sql);
    $data_handle->execute($account.'%')|| die $data_handle->errstr;;
 
 my @query_output;
 while ( my $row = $data_handle->fetchrow_hashref ){
   push @query_output, $row;
 }
 
# JSON OUTPUT
$self->header_add(-type=>"application/json");
#print JSON::to_json(\@query_output);
#return $self->json_body(\@query_output); 

my $json = to_json( \@query_output, {utf8 => 1} );
$json =~ s/"(\d+?)"/$1/g; # to_json puts quotes around numbers, we take them off
#$self->header_add(-type=>"application/json");
#return $json;

my $template = $self->load_tmpl('test.tmpl');
   $template->param('data', $json);
   return $template->output();
}

1;