Форум программистов [Powered by Invision Power Board]

Код

package MyApp::Controller::view_content;

use strict;
use warnings;
use parent 'Catalyst::Controller';

=head1 NAME

MyApp::Controller::view_content - Catalyst Controller

=head1 DESCRIPTION

Catalyst Controller.

=head1 METHODS

=cut

=head2 index 

=cut

sub view_content : Global {
    my ( $self, $c, @args ) = @_;

    $c->stash->{template} = 'view_content.tt';

    if ( !$args[0] || $args[0] !~ /^\d+$/ ) {
        $c->response->redirect( $c->uri_for('/') );
        $c->detach();
    }

    $args[1] = undef if ( $args[1] && $args[1] !~ /^\d+$/ );

    ## terms of query for current content
    my $sql;

    if ( !$c->check_user_roles('moder_se') && $c->user_exists() == 1 ) {

        $sql = 'AND (( t1.hiden_co = 0 
                     AND t1.active_co = 1)  
                     OR t1.id_un = ' . $c->user->{user}->{id} . ')';

    }
    elsif ( $c->check_user_roles('moder_se') ) {
        $sql = '';

    }

    elsif ( $c->user_exists() == 0 ) {
        $sql = 'AND t1.active_co = 1
                     AND t1.hiden_co = 0
                     AND t1.hiden_g_co = 0';
    }

    else {

    }

    my $sql_content = 'SELECT t1.id_co,  
                                 t1.id_un,            
                                    t1.id_se,             
                                        t1.name_co,          
                                            t1.heading_name_co,  
                                            t1.keys_co,          
                                            t1.text_co,         
                                            t1.active_co,        
                                            t1.hiden_co,         
                                            t1.hiden_g_co,       
                                            t1.close_co,         
                                            t1.voting_co,        
                                            t1.vo_all_co,       
                                            t1.vo_balls_co,     
                                            t1.vo_per,           
                                            t1.created,
                                            t1.modified,
                                            t1.forbi_comm_co,
                                            t2.name_se,
                                            t2.forbi_content_se,
                                    t2.hiden_g_co AS hiden_g_se,
                                    t2.active_se,
                                    t2.id_un AS id_un_par,                                            
                                            t3.username                                              
                            FROM content AS t1                                             
                       LEFT JOIN section AS t2
                              ON t1.id_se = t2.id_se
                       LEFT JOIN users AS t3
                              ON t1.id_un = t3.id        
                           WHERE t1.id_co = ' . $args[0] . ' 
                               ' . $sql . '
                           LIMIT 1';

    my $dbh = $c->model('DBI')->dbh;

    my $sth = $dbh->prepare($sql_content);
    $sth->execute();
    my $loop_data2 = $sth->fetchrow_hashref();
    $sth->finish();

    ## surplus of information and dispatch in a template

    $c->stash->{guest} = 1 if ( !$c->user_exists() );

    if ( !$loop_data2 ) {
        $c->stash->{messages_error} = 1;
        $c->detach();
    }

    if ( $loop_data2->{hiden_g_se} == 1 && !$c->user_exists() ) {
        $c->stash->{error_hiden_g} = 1;
        $c->detach();
    }

    if (   $loop_data2->{active_se} == 0
        && $loop_data2->{id_un_par} != $c->user->{user}->{id}
        && !$c->check_user_roles('moder_se') )
    {
        $c->stash->{error_active_se} = 1;
        $c->detach();
    }

    $c->stash->{add_co} = 1
      if ( $c->check_user_roles('add_co')
        && $loop_data2->{forbi_content_se} == 0 );

    $loop_data2->{username} =
      !$loop_data2->{username} ? 'Guest' : $loop_data2->{username};

    while ( my ( $key, $value ) = each( %{$loop_data2} ) ) {

        if (
            (
                   $c->user_exists()
                && $loop_data2->{id_un} == $c->user->{user}->{id}
            )
            || $c->check_user_roles('moder_se')
          )
        {
            $c->stash->{edit_co} = 1;
        }

        if ( $key eq 'active_co' ) {
            $value = $value == 0 ? '1' : undef;
        }

        if ( $key eq 'hiden_g_co' ) {
            $value = $value == 1 ? '1' : undef;
        }

        if ( $key eq 'voting_co' && $c->user_exists() ) {

            $value = $value == 1 ? '1' : undef;
        }

        if ( $key eq 'voting_co' && !$c->user_exists() ) {
            $value = undef;
        }

        if ( $key eq 'close_co' ) {
            $value = $value == 1 ? '1' : undef;
        }

        if ( $key eq 'hiden_co' ) {
            $value = $value == 1 ? '1' : undef;
        }

        if (
            (
                   $c->user_exists()
                && $loop_data2->{id_un} == $c->user->{user}->{id}
                && $loop_data2->{created} > time - 86400
                && $loop_data2->{close_co} != 1
            )
            || $c->check_user_roles('moder_co')
          )
        {
            $c->stash->{to_edit_co} = 1;
            $c->stash->{delete_co}  = 1;
        }

        $c->stash->{$key} = $value;

    }

    if (
        !$c->user_exists()
        || (   $loop_data2->{forbi_comm_co} == 1
            || $loop_data2->{close_co} == 1 )
        && !$c->check_user_roles('moder_co')
      )
    {

        $c->stash->{comm_co} = 1;
    }

    my $sql_max = 'SELECT count(*)+1 AS position
                              FROM content AS t1
                        LEFT JOIN content AS t2
                                 ON t1.id_co = ' . $loop_data2->{id_co} . ' 
                                AND t1.vo_all_co < t2.vo_all_co
                             WHERE t2.id_co IS NOT NULL';

    my $sth = $dbh->prepare($sql_max);
    $sth->execute();
    $c->stash->{position} = $sth->fetchrow_array();
    $sth->finish();

    ##  condition for the queries of paginal conclusion of comments
    my $sql2;

    if ( $c->user_exists() == 0 ) {
        $sql2 = 'id_co = ' . $args[0] . '
                     AND hiden_g_cm = 0';
    }
    else {
        $sql2 = 'id_co = ' . $args[0];
    }

    my $max_count = 'SELECT count(*)
                              FROM comment                                             
                              WHERE ' . $sql2;

    my $sth = $dbh->prepare($max_count);
    $sth->execute();
    my $count_content = $sth->fetchrow_arrayref();
    $sth->finish();

    my $url = '/view_content/' . $args[0] . '/';

    my $url_panel;

    $c->request->params->{moder_panel} = $c->request->params->{moder_panel}
      || '';

    if ( $c->request->params->{moder_panel} eq '1' ) {
        $url_panel = '/?moder_panel=1';
    }

    my ( $count_limit, $page, $max_page ) =
      $c->model('DBI')
      ->build_pages( $url, $count_content->[0], $args[1], $url_panel );

    $c->stash->{page} = $page if $page;

    my $sql_comment = 'SELECT    t1.id_cm,
                                            t1.id_co,  
                                            t1.id_se,         
                                            t1.id_un,           
                                            t1.name_guest,     
                                            t1.text_cm,       
                                            t1.hiden_g_cm,    
                                            t1.created,          
                                            t2.username
                            FROM comment AS t1                                             
                       LEFT JOIN users AS t2
                              ON t1.id_un = t2.id    
                           WHERE ' . $sql2 . '
                           LIMIT ?,?';

    my $sth = $dbh->prepare($sql_comment);
    $sth->execute( $count_limit, $max_page );
    my $loop_data;
    push @{$loop_data}, $_ while $_ = $sth->fetchrow_hashref();
    $sth->finish();

    if ($loop_data) {

        foreach $_ ( @{$loop_data} ) {

            ## determination of author as being a guest of ( it is cut out from architecture of the program)
            #if ($_->{id_un} == 0){
            #            if ($_->{text_cm}) {
            #        $_->{username} = $_->{text_cm};
            #
            #       } else {
            #       $_->{username} = 'Guest';
            #        }
            #}

            $_->{username} = 'Guest' if ( $_->{username} eq '' );

            if (
                (
                       $c->user_exists()
                    && $_->{id_un} == $c->user->{user}->{id}
                    && $_->{created} > time - 86400
                    && $loop_data2->{close_co} != 1
                )
                || $c->check_user_roles('moder_co')
              )
            {

                #  print '1';
                $_->{delete_cm} = 1;
                $_->{edit_cm}   = 1;
            }

        }

        $c->stash->{comment} = $loop_data;
    }
    else {
        $c->stash->{no_comment} = 1;
    }

    #### tree
    $c->forward( 'tree_sections', [$loop_data2] );

    ## verification and conclusion previous and followings content

    my $sql_max = 'SELECT MAX(t1.id_co) AS prev                
                              FROM content AS t1
                            WHERE t1.id_se = ' . $loop_data2->{id_se} . ' 
                               AND t1.id_co > ' . $args[0] . '
                               ' . $sql . ' 
                             LIMIT 1';

    my $sth = $dbh->prepare($sql_max);
    $sth->execute();
    my $loop_data3 = $sth->fetchrow_hashref();
    $sth->finish();

    my $sql_min = 'SELECT MIN(t1.id_co) AS next
                             FROM content AS t1
                           WHERE t1.id_se = ' . $loop_data2->{id_se} . ' 
                                AND t1.id_co < ' . $args[0] . '
                                ' . $sql . '  
                           LIMIT 1';

    my $sth = $dbh->prepare($sql_min);
    $sth->execute();
    my $loop_data4 = $sth->fetchrow_hashref();
    $sth->finish();

    $c->stash->{next} = $loop_data4->{next} if ( $loop_data4->{next} );
    $c->stash->{prev} = $loop_data3->{prev} if ( $loop_data3->{prev} );

}

sub tree_sections : Privat {

    my ( $self, $c, $loop_data2 ) = @_;

    my @loop_data_tree;
    my $loop_data_tree;

    if ( $loop_data2->{id_se} != 1 ) {

        my $index = 0;
        my @head  = ();
        my $dbh   = $c->model('DBI')->dbh;
        while (1) {

            my $sth = $dbh->prepare( "
        SELECT parent_se_id,
                 id_se,
                 name_se             
        FROM section
        WHERE id_se = ? 
        LIMIT 1
      " );

            #my $sqle;
            #if ( $head[$#head] ) {
            #$sqle = $head[$#head];
            #} else {
            #$sqle = $loop_data2->{id_se};
            #}

            $sth->execute( $head[$#head] || $loop_data2->{id_se} );

            my $loop = $sth->fetchrow_hashref();
            $sth->finish();

            push( @{$loop_data_tree}, $loop );

            push @head, $loop->{parent_se_id};

            last if ( $head[$#head] eq '1' || !$head[$#head] );

        }

        @{$loop_data_tree} = reverse @{$loop_data_tree};

        $c->stash->{data_tree} = $loop_data_tree;

    }

}

=head1 AUTHOR

Charlie &

=head1 LICENSE

This library is free software, you can redistribute it and/or modify
it under the same terms as Perl itself.

=cut

1;

Код

package MyApp::Controller::view_section;

use warnings;
use parent 'Catalyst::Controller';
use strict;

=head1 NAME

MyApp::Controller::view_section - Catalyst Controller

=head1 DESCRIPTION

Catalyst Controller.

=head1 METHODS

=cut

=head2 index 

=cut

sub view_section : Global {
    my ( $self, $c, @args ) = @_;

    $c->stash->{template} = 'view_section.tt';

    if ( $args[0] == 1 || $args[0] !~ /^\d+$/ ) {
        $c->response->redirect( $c->uri_for('/view_global_section') );
        return;
    }

    $args[1] = undef if ( $args[1] && $args[1] !~ /^\d+$/ );

    my $sql2;    ## page section
    my $sql3;    ## count_section
    my $sql4;    ## count_content
                 #  my @sql;

    if ( $c->user_exists() == 0 ) {

        ## page section
        $sql2 = 't1.hiden_g_co = 0
                     AND t1.active_se = 1
                     AND t1.privat_se = 0
                     AND t1.parent_se_id = ? ';

        ## count_section
        $sql3 = 'AND t2.hiden_g_co = 0
                     AND t2.active_se = 1
                     AND t2.privat_se = 0';

        ## count_content
        $sql4 = ' AND t3.active_co = 1
                    AND t3.hiden_co = 0
                    AND t3.hiden_g_co = 0';

    }

    if ( $c->user_exists() == 1
        && !$c->check_user_roles('moder_se') )
    {

        ## page section
        $sql2 = '(t1.active_se = 1
                     OR t1.id_un = ' . $c->user->{user}->{id} . ')
                     AND t1.privat_se = 0
                     AND t1.parent_se_id = ?';

        ## count_section
        $sql3 = 'AND (t2.active_se = 1                     
                     OR t2.id_un = ' . $c->user->{user}->{id} . ')
                     AND t2.privat_se = 0';

        ## count_content
        $sql4 = 'AND (( t3.hiden_co = 1 
                     AND t3.active_co = 1)  
                     OR t3.id_un = ' . $c->user->{user}->{id} . ')';

    }

    if (   $c->user_exists() == 1
        && $c->check_user_roles('moder_se') )
    {

        ## page section
        $sql2 = 't1.parent_se_id = ?
                     AND t1.privat_se = 0';

        ## count_section
        $sql3 = 'AND t2.privat_se = 0';

        ## count_content
          $sql4 = '';
    }

    ## check section and parent
    my $sql_section = 'SELECT t1.id_se,
                                      t1.id_un, 
                                   t1.parent_se_id,
                                   t1.hiden_g_co,
                                   t1.close_se,
                                   t1.forbi_section_se,
                                   t1.forbi_content_se,
                                   t1.active_se,
                                   t2.hiden_g_co AS hiden_g_co_par,
                                   t2.active_se AS active_se_par,
                                   t2.id_un AS id_un_par
                                   
                                    
                              FROM section AS t1
                       LEFT JOIN section AS t2
                             ON t2.id_se = t1.parent_se_id 

                             WHERE t1.id_se = ? 
                             LIMIT 1';

    my $parent = $c->model('DBI')->extra_prepare( $sql_section, $args[0] );

    if ( $parent->{parent_se_id} ) {

        if ( $parent->{parent_se_id} != 1 ) {
            $c->stash->{parent_se_id} = $parent->{parent_se_id};
            $c->stash->{id_se}        = $parent->{id_se};
        }

    }
    else {
        $c->stash->{error_section} = 1;
        return;

    }

    if ( $parent->{hiden_g_co} == 1 && $c->user_exists() == 0 ) {
        $c->stash->{error_hiden_g} = 1;
        return;
    }

    if ( $parent->{hiden_g_co_par} == 1 && $c->user_exists() == 0 ) {
        $c->stash->{error_hiden_g_par} = 1;
        return;
    }

    if (   $parent->{active_se} == 0
        && $parent->{id_un} != $c->user->{user}->{id}
        && !$c->check_user_roles('moder_se') )
    {

        $c->stash->{error_active_se} = 1;
        return;

    }

    if (   $parent->{active_se_par} == 0
        && $parent->{id_un_par} != $c->user->{user}->{id}
        && !$c->check_user_roles('moder_se') )
    {

        $c->stash->{error_active_se_par} = 1;
        return;

    }

    if (
        $c->check_user_roles('moder_se')
        || (   $c->user_exists() == 1
            && $parent->{id_un_par} == $c->user->{user}->{id} )
      )
    {
        $c->stash->{close_se}  = $parent->{close_se} == 1  ? '1'   : undef;
        $c->stash->{active_se} = $parent->{active_se} == 1 ? undef : '1';
        $c->stash->{active_se_par} =
          $parent->{active_se_par} == 1 ? undef : '1';
        $c->stash->{hiden_g_co} = $parent->{hiden_g_co} == 1 ? '1' : undef;
        $c->stash->{hiden_g_co_par} =
          $parent->{hiden_g_co_par} == 1 ? '1' : undef;

    }

    $c->stash->{add_co} = 1
      if ( $c->check_user_roles('add_co') && $parent->{forbi_content_se} == 0 );
    $c->stash->{add_se} = 1
      if ( $c->check_user_roles('add_se') && $parent->{forbi_section_se} == 0 );

    ## select count section from subsections
    my $sql_count = 'SELECT count(*) AS count
                 FROM section AS t1 
                WHERE 
                     ' . $sql2;

    $c->stash->{id_se_sort} = $args[0];

    my $count_section = $c->model('DBI')->extra_prepare( $sql_count, $args[0] );

    ## preparation of sorting of information
    my $url = '/view_section/' . $args[0] . '/';

    my ( $sort_url, $sort, $asc, $sort_sql );

    if ( $c->request->params->{sort} ) {

        ( $sort, $asc ) = split( /-/, $c->request->params->{sort}, 2 );

        if ( $sort eq 'name' ) {
            $sort_sql              = 't1.name_se';
            $sort_url              = '/?sort=' . $sort;
            $c->stash->{name_sort} = 1;
        }
        elsif ( $sort eq 'username' ) {
            $sort_sql                  = 't3.username';
            $sort_url                  = '/?sort=' . $sort;
            $c->stash->{username_sort} = 1;
        }
        else {
            $sort_sql = 't1.created';
            $c->stash->{time_sort} = 1;
        }

        if ($asc) {

            $sort_url .= '-asc';
            $c->stash->{asc} = 1;
            $sort_sql .= ' asc';
        }
        else {
            $sort_sql .= ' desc';
        }

    }
    else {

        $sort_sql = 'created desc';
        $c->stash->{time_sort} = 1;
    }

    $c->request->params->{moder_panel} = $c->request->params->{moder_panel}
      || '';

    if ( $c->request->params->{moder_panel} eq '1' ) {
        $sort_url = $sort_url ? '&moder_panel=1' : '?moder_panel=1';
    }

    $c->stash->{id_se_sort} = $args[0];

    ##construction of pages

    my ( $count_limit, $page, $max_page ) =
      $c->model('DBI')
      ->build_pages( $url, $count_section->{count}, $args[1], $sort_url );

    $c->stash->{page} = $page if $page;

    ## query of viewing of subsections

    my $sqlsubsections = 'SELECT t1.id_se,
                      t1.id_un, 
                      t1.name_se, 
                      t1.parent_se_id,
                      t1.close_se,
                      t1.active_se,
                      t1.hiden_g_co,
                      t1.forbi_section_se,
                      t1.forbi_content_se,
                      t1.created,

                      (select count(*) 
                         from section as t2 
                        where t1.id_se = t2.parent_se_id
                          ' . $sql3 . '
                        ) AS count_section,

                      (select count(*) 
                         from content as t3 
                        where t3.id_se = t1.id_se
                            ' . $sql4 . '
                        ) AS count_content,
                        
                      t4.id,
                      t4.username                                      
             FROM section AS t1
                LEFT JOIN users AS t4
                ON id_un = t4.id 
           WHERE ' . $sql2 . '
           ORDER BY ' . $sort_sql . '
           LIMIT ?,?';

    my $loop_data =
      $c->model('DBI')
      ->push_prepare( $sqlsubsections, $args[0], $count_limit, $max_page );

    ## surplus of information, verification is a change, verification of roles, and dispatch in a template
    foreach $_ ( @{$loop_data} ) {
        $_->{count_section} ||= '-';
        $_->{count_content} ||= '-';
        $_->{username} = !$_->{username} ? 'Guest' : $_->{username};

        if ( $c->check_user_roles('moder_se') ) {
            $_->{close_se}  = $_->{close_se} == 1  ? '1' : undef;
            $_->{active_se} = $_->{active_se} == 1 ? '1' : undef;

            $_->{forbi_section_se} = $_->{forbi_section_se} == 1 ? '1' : undef;
            $_->{forbi_content_se} = $_->{forbi_content_se} == 1 ? '1' : undef;
            $_->{hiden_g_co}       = $_->{hiden_g_co} == 1       ? '1' : undef;
        }
        if (
            $c->check_user_roles('moder_se')
            || (   $c->user_exists()
                && $_->{id_un} == $c->user->{user}->{id}
                && $_->{created} > time - 86400 )
          )
        {

            $_->{edit_se}   = 1;
            $_->{delete_se} = 1;

        }

    }

    if ( $c->check_user_roles('moder_se') ) {

        $_->{edit_close} = 1;

        if ( $c->request->params->{moder_panel} eq '1' ) {

            foreach $_ ( @{$loop_data} ) {
                $_->{id_se}       = $_->{id_se} . '/?moder_panel=1';
                $_->{moder_panel} = 1;

            }
            $c->stash->{id_se} = $args[0];

            $c->stash->{moder_panel} = 1;

        }
        else {
            $c->stash->{id_se} = $args[0] . '/?moder_panel=1';

        }
        $c->stash->{moder_se} = 1;
    }

    if ($loop_data) {
        $c->stash->{section_head} = $loop_data;
    }
    else {
        $c->stash->{no_section_head} = 1;
    }

    ## verification and conclusion previous and followings section
    my $sql_max = 'SELECT MIN(t2.id_se) AS next                
                              FROM section AS t2
                             WHERE t2.id_se > ?
                               AND t2.parent_se_id = ?
                               '.$sql3.'
                             LIMIT 1';

    my $loop_max =
      $c->model('DBI')
      ->extra_prepare( $sql_max, $args[0], $parent->{parent_se_id} );

    my $sql_min = 'SELECT MAX(t2.id_se) AS prev
                               FROM section AS t2
                           WHERE t2.id_se < ?
                                AND t2.parent_se_id = ?
                                '.$sql3.' 
                          LIMIT 1';

    my $loop_min =
      $c->model('DBI')
      ->extra_prepare( $sql_min, $args[0], $parent->{parent_se_id} );

    $c->stash->{prev} = $loop_min->{prev} if ( $loop_min->{prev} );
    $c->stash->{next} = $loop_max->{next} if ( $loop_max->{next} );

    #### tree
    $c->forward( qw /MyApp::Controller::view_content tree_sections/,
        [$parent] );
    ### end tree

}

=head1 AUTHOR

Charlie &

=head1 LICENSE

This library is free software, you can redistribute it and/or modify
it under the same terms as Perl itself.

=cut

1;