#!/usr/local/bin/perl

# $Id: index.pl 8268 2008-01-30 12:10:17Z oinuma $

use strict;

BEGIN {
    package FJ::C::Com::Support::SOS::Index;

    use Tie::IxHash;
    use FJ::Controller;
    use FJ::Formatter qw(format_date);
    use FJ::M::CMSCategoryFJ20;
    use FJ::M::CMSEntryFJ20;
    use FJ::M::CMSAccessLogSummary;

    use base qw(FJ::Controller);

    my @SOS_CMS_CATEGORY_IDS = qw(35 36 37 38 39 40 41);

    tie my %CATEGORIES, 'Tie::IxHash';
    %CATEGORIES = (
        cat01 => {
            cms_category_id => 35,
            sub_title_css => 'for_recstaff_soscat_01',
        },
        cat02 => {
            cms_category_id => 36,
            sub_title_css => 'for_recstaff_soscat_02',
        },
        cat03 => {
            cms_category_id => 37,
            sub_title_css => 'for_recstaff_soscat_03',
        },
        cat04 => {
            cms_category_id => 38,
            sub_title_css => 'for_recstaff_soscat_04',
        },
        cat05 => {
            cms_category_id => 39,
            sub_title_css => 'for_recstaff_soscat_05',
        },
        cat06 => {
            cms_category_id => 40,
            sub_title_css => 'for_recstaff_soscat_06',
        },
        cat07 => {
            cms_category_id => 41,
            sub_title_css => 'for_recstaff_soscat_07',
        },
    );

    #
    # TEMPLATES
    #
    __PACKAGE__->templates(
        # TOP: /com/support/sos/
        index => { file => 'tmpl/com/support/sos/index.tmpl' },
        # 各カテゴリのTOP: /com/support/sos/cat01/
        index_category => { file => 'tmpl/com/support/sos/index_category.tmpl' },
    );

    #
    # PREPARE
    #
    sub prepare {
        my ($self) = @_;
        return ($self->params->{category}) ? 'index_category' : 'index';
    }

    #
    # METHODS
    #
    sub index {
        my ($self) = @_;

        my $sdbh = FJ::DBManager->load_slave_dbh();
        # 「最新人事SOS」
        my $latest_sos_iterator = FJ::M::CMSEntryFJ20->load_iterator_by_sql(
            _sql => <<"SQL",
SELECT e.*, c.category_name, c.directory
FROM cms_entry_fj20 AS e
INNER JOIN cms_category_fj20 AS c ON e.cms_category_id = c.cms_category_id
WHERE
    e.status = 'publish'
    AND e.cms_category_id IN (@{[ join ',', @SOS_CMS_CATEGORY_IDS ]})
ORDER BY e.cms_entry_id DESC
LIMIT 10
SQL
           _dbh => $sdbh,
        );
        my @latest_sos = ();
        while (my $row = $latest_sos_iterator->next) {
            $row->{category_name} = $self->trim_sos_prefix($row->{category_name});
            push @latest_sos, $row;
        }

        my @other_categories = $self->load_other_categories();

        my $summary = FJ::M::CMSAccessLogSummary->load_by_sql(
            _sql => "SELECT * FROM cms_access_log_summary ORDER BY date_from DESC",
        );
        if (!$summary) {
            # データが1件もなかった時の保険
            $summary = FJ::M::CMSAccessLogSummary->new(
                from_date => '2008-01-21',
                to_date => '2008-01-27'
            );
        }

        my $access_ranking_iterator = FJ::M::CMSAccessLogSummary->load_iterator_by_sql(
            _sql => <<"SQL",
SELECT
    c.directory, c.category_name, e.file_name, e.title
FROM cms_access_log_summary AS log
INNER JOIN cms_entry_fj20 AS e ON log.cms_entry_id = e.cms_entry_id
INNER JOIN cms_category_fj20 AS c ON e.cms_category_id = c.cms_category_id
WHERE
    e.status = 'publish'
    AND log.date_from = ? AND log.date_to = ?
    AND e.cms_category_id IN (@{[ join ',', @SOS_CMS_CATEGORY_IDS ]})
ORDER BY log.access_count DESC
LIMIT 5
SQL
            _bind_values => [ $summary->date_from, $summary->date_to ],
           _dbh => $sdbh,
        );
        my @access_ranking = ();
        while (my $row = $access_ranking_iterator->next) {
            $row->{category_name} = $self->trim_sos_prefix($row->category_name);
            push @access_ranking, $row;
        }

        $self->template->param(
            latest_sos              => \@latest_sos,
            other_categories         => \@other_categories,
            access_ranking_date_from => format_date($summary->date_from),
            access_ranking_date_to   => format_date($summary->date_to),
            access_ranking           => \@access_ranking,
        );

        $self->template->param(
            side_menu => $self->show_company_side_menu(type => 'support'),
            sc_tag => $self->show_sc_tag(
                channel => qq{"com/support"},
            ),
            _no_escape => 1,
        );
    }

    sub index_category {
        my ($self) = @_;
        my $sdbh = FJ::DBManager->load_slave_dbh();

        my $cat = $self->params->{category};

        my $category = FJ::M::CMSCategoryFJ20->load(
            _where => "directory = ?",
            _bind_values => [ "/com/support/sos/$cat/" ],
            _dbh => $sdbh,
        );
        if (!$category) {
            $self->output_http_header(status => 404);
            $self->exit;
        }

        my $entries_iterator = FJ::M::CMSEntryFJ20->load_iterator_by_sql(
            _sql => <<"SQL",
SELECT e.*
FROM cms_entry_fj20 AS e
WHERE
    e.cms_category_id = ?
    AND e.status = 'publish'
ORDER BY e.cms_entry_id DESC
SQL
           _bind_values => [ $category->cms_category_id ],
        );
        my @entries = ();
        while (my $e = $entries_iterator->next) {
            $e->{directory} = $category->directory;
            push @entries, $e;
        }

        my @other_categories = $self->load_other_categories();

        $self->template->param(
            category_name => $self->trim_sos_prefix($category->category_name),
            sub_title_css => $CATEGORIES{$cat}->{sub_title_css},
            entries       => \@entries,
            other_categories => \@other_categories,
        );

        $self->template->param(
            side_menu => $self->show_company_side_menu(type => 'support'),
            sc_tag => $self->show_sc_tag(
                channel => qq{"com/support"},
            ),
            _no_escape => 1,
        );
    }

    # 「カテゴリ別SOSを検索」のデータを取得
    sub load_other_categories {
        my ($self) = @_;
        my $sdbh = FJ::DBManager->load_slave_dbh();

        my $other_categories_iterator = FJ::M::CMSEntryFJ20->load_iterator_by_sql(
            _sql => <<"SQL",
SELECT
    c.cms_category_id, c.category_name, c.directory, e.count
FROM cms_category_fj20 AS c
LEFT JOIN (
    SELECT cms_category_id, COUNT(*) AS count FROM cms_entry_fj20
    WHERE
        status = 'publish'
        AND cms_category_id IN (@{[ join ',', @SOS_CMS_CATEGORY_IDS ]})
    GROUP BY cms_category_id
) AS e ON c.cms_category_id = e.cms_category_id
WHERE
    c.cms_category_id IN (@{[ join ',', @SOS_CMS_CATEGORY_IDS ]})
ORDER BY c.cms_category_id ASC
SQL
           _dbh => $sdbh,
        );
        my @other_categories = ();
        while (my $row = $other_categories_iterator->next) {
            $row->{category_name} = $self->trim_sos_prefix($row->category_name);
            $row->{count} ||= 0;
            push @other_categories, $row;
        }

        return @other_categories;
    }

    sub trim_sos_prefix {
        my ($self, $name) = @_;
        # Jcode->trだとなぜかうまく動かない＆問題なさそうなのでs///を使用
        $name =~ s/人事S\.O\.S //;
        return $name;
    }

    1;
} # End of package definition scope.

FJ::C::Com::Support::SOS::Index->new()->process();