посмотрите плиз сейчас, например 2 модуля:
может алгоритм где-то поменять надо?
Код: Выделить всё
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;