Good day monks ..
I've made a small application and I am going to give a presentation about dancer .. I am seeking your wisdom code-wise:
The full app is here:
http://www.4shared.com/file/wmPRXdxZ/App-Thunder2.html the main file is:

package App::Thunder;
use Dancer ':syntax';
use DBI;
use autodie;
use Template;
use Email::Valid;
use Dancer::Plugin::FlashMessage;

our $VERSION = '0.8';

sub connect_db {
    my $hostname = 'localhost';
    my $database = 'thunder';
    my $user     = 'root';
    my $password = '';
    my $dsn      = "DBI:mysql:database=$database;host=$hostname;";
    my $dbh      = DBI->connect( $dsn, $user, $password );
    $dbh->{'mysql_enable_utf8'} = 1;
    $dbh->do('SET NAMES utf8');
    return $dbh;
}

before_template sub {
    my $tokens = shift;
    $tokens->{'details_url'} = request->base . 'details';
    $tokens->{'css_url'}     = request->base . 'css/';
    $tokens->{'login_url'}   = uri_for('/login');
    $tokens->{'logout_url'}  = uri_for('/logout');
    $tokens->{'account_url'} = uri_for('/account/');
    $tokens->{'signup_url'}  = uri_for('/signup');
    $tokens->{'review_url'}  = uri_for('/review');
    $tokens->{'add_url'}     = uri_for('/add');
    $tokens->{'update_url'}     = uri_for('/update');
};

get '/' => sub {
    template 'index.tt', {};
};

get '/cat/:group' => sub {
    my $dbh  = connect_db();
    my $stat = 'on';
    my $sql  = 'SELECT * FROM ad WHERE ad_category = ? AND ad_stat = ?
+';
    my $sth  = $dbh->prepare($sql);
    $sth->execute( params->{group}, $stat );

    template 'show_entries.tt',
      { 'entries' => $sth->fetchall_hashref('ad_id') };
};

get '/details/:id' => sub {
    my $dbh = connect_db();
    my $sql =
'SELECT * FROM ad, user WHERE ad.ad_id = ? AND ad.ad_stat = "on" AND a
+d.ad_owner_id = user.user_id ';
    my $sth = $dbh->prepare($sql);
    $sth->execute( params->{id} );

    template 'show_details.tt', { 'entry' => $sth->fetchall_hashref('a
+d_id') };
};

post '/search' => sub {
    my $db = connect_db();
    my $sql =
      'SELECT * FROM ad WHERE ad_title  LIKE ?';
    my $sth = $db->prepare($sql);
    $sth->execute( params->{search});

    template 'show_entries.tt',
      { 'entries' => $sth->fetchall_hashref('ad_id') };
};

any '/add' => sub {
    if ( not session->{user_id} ) {
        flash error => 'You are not logged in...';
        return redirect '/';
    }
    if ( request->method() eq "POST" ) {
        my ( $photo, @error ) = helper_ad(
            params->{'ad_title'}, params->{'ad_description'},
            params->{'ad_price'}, params->{'ad_photo'}
        );
        if (@error) {
            flash error => join( ' & ', @error );
        }
        else {
            my $db = connect_db();
            my $sql =
'insert into ad (ad_type, ad_category, ad_title, ad_description, ad_co
+ndition,
    ad_price, ad_owner_id, ad_photo) values (?, ?, ?, ?, ?, ?, ?, ?)';
            my $sth = $db->prepare($sql);
            $sth->execute(
                params->{'ad_type'},      params->{'ad_category'},
                params->{'ad_title'},     params->{'ad_description'},
                params->{'ad_condition'}, params->{'ad_price'},
                session->{user_id},       $photo
            );
            flash msg =>
              'Your entry is added successfully and is pending approva
+l';
            return redirect '/';
        }
    }

    template 'add.tt', {};
};

any '/review' => sub {

    if ( request->method() eq "POST" ) {
        my $db     = connect_db();
        my $params = params('body');
        my ( @approve, @delete );
        while ( my ( $key, $value ) = each %$params ) {
            push @approve, $key if ( $value eq 'approve' );
            push @delete,  $key if ( $value eq 'delete' );
        }

        if (@approve) {
            my $placeholders = join( ',', ('?') x @approve );
            my $statement = <<"EOF";
   update ad set ad_stat = 'on'
   WHERE ad_id IN ( $placeholders )
EOF
            my $sth = $db->prepare($statement);
            $sth->execute(@approve);
        }
        if (@delete) {
            my $placeholders = join( ',', ('?') x @delete );
            my $statement = <<"EOF";
DELETE FROM ad
WHERE ad_id IN ( $placeholders )
EOF
            my $sth = $db->prepare($statement);
            $sth->execute(@delete);
            flash msg => 'Update completed...';
            return redirect '/';
        }
    }

    if ( session->{logged_in} ) {
        if ( session->{user_mode} eq "super" ) {
            my $stat = 'off';
            my $dbh  = connect_db();
            my $sql  = 'SELECT * FROM ad WHERE ad_stat = ?';
            my $sth  = $dbh->prepare($sql);
            $sth->execute($stat);

            template 'review.tt',
              { 'entry' => $sth->fetchall_hashref('ad_id') };
        }

        else {
            flash error => 'You should be an admin to approve posts !'
+;
            return redirect '/';
        }
    }
    else {
        return redirect '/';
    }

};

any '/update/:id' => sub {
    if ( not session->{user_id} ) {
        flash error => 'You are not logged in...';
        return redirect '/';
    }
    if ( request->method() eq "POST" ) {
        my ( $photo, @error ) = helper_ad(
            params->{'ad_title'}, params->{'ad_description'},
            params->{'ad_price'}, params->{'ad_photo'}
        );
        if (@error) {
            return join( ' & ', @error );
        }
        my $db = connect_db();
        my $sql =
'update ad set ad_type = ?, ad_category = ?, ad_title = ?, ad_descript
+ion = ?, ad_condition = ?,
    ad_price = ?, ad_owner_id = ?, ad_photo = ? where ad_id = ?';
        my $sth = $db->prepare($sql);
        $sth->execute(
            params->{'ad_type'},      params->{'ad_category'},
            params->{'ad_title'},     params->{'ad_description'},
            params->{'ad_condition'}, params->{'ad_price'},
            session->{user_id},       $photo,
            params->{id}
        );
        flash msg => 'Updated successfully...';
        return redirect '/';
    }

    my $dbh = connect_db();
    my $sql = 'SELECT * FROM ad WHERE ad_id = ?';
    my $sth = $dbh->prepare($sql);
    $sth->execute( params->{id} );
    if ( $sth->rows ) {
        my $ref         = $sth->fetchrow_hashref();
        my $ad_owner_id = $ref->{'ad_owner_id'};
        if ( not session->{user_id} == $ad_owner_id ) {
             flash error => 'You dont have access for this page!';
        return redirect '/';
        }
        my $sth = $dbh->prepare($sql);
        $sth->execute( params->{id} );

        template 'edit.tt', { 'entry' => $sth->fetchall_hashref('ad_id
+') };

    }
    else {
             flash error => 'No such entry..';
        return redirect '/';
    }
};

any '/account/:id' => sub {
    if ( not session->{user_id} ) {
        flash error => 'You are not logged in...';
        return redirect '/';
    }
    elsif ( not session->{user_id} == params->{id} ) {
            flash error => 'You dont have access for this page !';
        return redirect '/';
    }
    else {
        if ( request->method() eq "POST" ) {
            my @error = helper_user(
                params->{'user_email'},    params->{'user_pass'},
                params->{'user_location'}, params->{'user_phone'}
            );
            if (@error) {
                flash error => join( ' & ', @error );
            }
            else {
                my $db  = connect_db();
                my $pass = crypt(params->{'user_pass'},'q1');
                my $sql = 'SELECT * FROM user WHERE user_email = ?';
                my $sth = $db->prepare($sql);
                $sth->execute( params->{'user_email'} );
                if ( !$sth->rows || params->{'user_email'} eq session-
+>{user} )
                {
                    my $sql =
'update user set user_email = ?, user_pass = ?, user_location = ?, use
+r_phone = ? where user_id = ?';
                    my $sth = $db->prepare($sql);
                    $sth->execute(
                        params->{'user_email'},    $pass,
                        params->{'user_location'}, params->{'user_phon
+e'},
                        session->{user_id}
                    );
                    flash msg => 'Updated successfuly..';
                    return redirect '/';
                }
                else {
                         flash error => 'we already have this email si
+gned up..';

                }

            }
        }

        my $dbh = connect_db();
        my $sql = 'SELECT * FROM user WHERE user_id = ?';
        my $sth = $dbh->prepare($sql);
        $sth->execute( session->{user_id} );
        my $user = $sth->fetchall_hashref('user_id');
        my $sql  = 'SELECT * FROM ad WHERE ad_owner_id = ?';
        my $sth  = $dbh->prepare($sql);
        $sth->execute( session->{user_id} );

        template 'account.tt',
          { 'account' => $user,
          'entries' => $sth->fetchall_hashref('ad_id') 
          };
    }
};

any '/login' => sub {
    if ( session->{user} ) {
        flash msg => 'You are already logged in as ' . session->{user}
+;
        return redirect '/';
    }
    if ( request->method() eq "POST" ) {
        my $dbh = connect_db();
        my $pass = crypt(params->{'password'},'q1');
        my $sql = 'SELECT * FROM user WHERE user_email = ? AND user_pa
+ss = ?';
        my $sth = $dbh->prepare($sql);
        $sth->execute( params->{email}, $pass );
        if ( $sth->rows ) {
            my $ref       = $sth->fetchrow_hashref();
            my $user      = $ref->{'user_email'};
            my $user_id   = $ref->{'user_id'};
            my $user_mode = $ref->{'user_mode'};
            session 'logged_in' => true;
            session 'user'      => $user;
            session 'user_id'   => $user_id;
            session 'user_mode' => $user_mode;
            flash msg           => 'You are now logged in..';
            return redirect '/';
        }
        else {
            flash error => 'No such user or password mismatched.';
        }
    }
    template 'sign_in.tt', {};

};

any '/signup' => sub {
    if ( request->method() eq "POST" ) {
        my @error = helper_user(
            params->{'user_email'},    params->{'user_pass'},
            params->{'user_location'}, params->{'user_phone'}
        );
        if (@error) {
            flash error => join( ' & ', @error );
        }
        else {
            my $db  = connect_db();
            my $sql = 'SELECT * FROM user WHERE user_email = ?';
            my $sth = $db->prepare($sql);
            $sth->execute( params->{'user_email'} );
            if ( not $sth->rows ) {
                my $pass = crypt(params->{'user_pass'},'q1');
                my $sql = 'insert into user (user_email, user_pass, us
+er_location, user_phone) values (?, ?, ?, ?)';
                my $sth = $db->prepare($sql);
                $sth->execute(
                    params->{'user_email'},    $pass,
                    params->{'user_location'}, params->{'user_phone'}
                );
                flash msg => 'Successfuly signed up...';
                return redirect '/';
            }
            else {
                flash error => 'We already have this email signed up! 
+Choose another email..';
            }
        }
    }

    template 'sign_up.tt', {};
};

get '/logout' => sub {
    session->destroy;
    flash msg => 'You are now logged out...';
    return redirect '/';
};

get '/about' => sub {
    template 'about.tt', {};
};

##### Form Helpers #####

sub helper_user {
    my ( $email, $password, $phone, $location ) = @_;
    my @errors;
    push @errors, "No value entered for Email."    if ( $email    eq '
+' );
    push @errors, "No value entered for password." if ( $password eq '
+' );
    push @errors, "No value entered for location." if ( $phone    eq '
+' );
    push @errors, "No value entered for phone."    if ( $location eq '
+' );
    if ( not $email eq '' ) {
        my $email_eval =
          Email::Valid->address( params->{'user_email'} ) ? '1' : '0';
        push @errors, "Doesn't seem like a valid Email.." if ( !$email
+_eval );
    }
    return @errors;
}

sub helper_ad {
    my ( $title, $description, $price, $photo ) = @_;
    my @errors;
    $photo = '../images/na.jpg' if ( $photo eq '' );
    push @errors, "No value entered for title."       if ( $title     
+  eq '' );
    push @errors, "No value entered for description." if ( $descriptio
+n eq '' );
    push @errors, "No value entered for price."       if ( $price     
+  eq '' );
    return ( $photo, @errors );
}



true;
[download]